Hi, in this blog we will restore the Database using the script.
  1. -- Dropping database
  2. USE [master]
  3. GO
  4. EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Portal_PS'
  5. GO
  6. USE [master]
  7. GO
  8. ALTER DATABASE [Portal_PS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  9. GO
  10. USE [master]
  11. GO
  12. DROP DATABASE [Portal_PS]
  13. GO
  14. --Creating database
  15. CREATE DATABASE [Portal_PS] ON PRIMARY
  16. ( NAME = N'Portal_PS', FILENAME = N'T:\MSSQL10_50.UAT\MSSQL\DATA\Portal_PS.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
  17. --Change the highlighted section as per your requirment
  18. LOG ON
  19. ( NAME = N'Portal_PS_log', FILENAME = N'U:\MSSQL10_50.UAT\MSSQL\Data\Portal_PS_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
  20. --Change the highlighted section as per your requirment
  21. GO
  22. ALTER DATABASE [Portal_PS] SET COMPATIBILITY_LEVEL = 100
  23. GO
  24. ALTER DATABASE [Portal_PS] SET ANSI_NULL_DEFAULT OFF
  25. GO
  26. ALTER DATABASE [Portal_PS] SET ANSI_NULLS OFF
  27. GO
  28. ALTER DATABASE [Portal_PS] SET ANSI_PADDING OFF
  29. GO
  30. ALTER DATABASE [Portal_PS] SET ANSI_WARNINGS OFF
  31. GO
  32. ALTER DATABASE [Portal_PS] SET ARITHABORT OFF
  33. GO
  34. ALTER DATABASE [Portal_PS] SET AUTO_CLOSE OFF
  35. GO
  36. ALTER DATABASE [Portal_PS] SET AUTO_CREATE_STATISTICS ON
  37. GO
  38. ALTER DATABASE [Portal_PS] SET AUTO_SHRINK OFF
  39. GO
  40. ALTER DATABASE [Portal_PS] SET AUTO_UPDATE_STATISTICS ON
  41. GO
  42. ALTER DATABASE [Portal_PS] SET CURSOR_CLOSE_ON_COMMIT OFF
  43. GO
  44. ALTER DATABASE [Portal_PS] SET CURSOR_DEFAULT GLOBAL
  45. GO
  46. ALTER DATABASE [Portal_PS] SET CONCAT_NULL_YIELDS_NULL OFF
  47. GO
  48. ALTER DATABASE [Portal_PS] SET NUMERIC_ROUNDABORT OFF
  49. GO
  50. ALTER DATABASE [Portal_PS] SET QUOTED_IDENTIFIER OFF
  51. GO
  52. ALTER DATABASE [Portal_PS] SET RECURSIVE_TRIGGERS OFF
  53. GO
  54. ALTER DATABASE [Portal_PS] SET DISABLE_BROKER
  55. GO
  56. ALTER DATABASE [Portal_PS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  57. GO
  58. ALTER DATABASE [Portal_PS] SET DATE_CORRELATION_OPTIMIZATION OFF
  59. GO
  60. ALTER DATABASE [Portal_PS] SET PARAMETERIZATION SIMPLE
  61. GO
  62. ALTER DATABASE [Portal_PS] SET READ_WRITE
  63. GO
  64. ALTER DATABASE [Portal_PS] SET RECOVERY FULL
  65. GO
  66. ALTER DATABASE [Portal_PS] SET MULTI_USER
  67. GO
  68. ALTER DATABASE [Portal_PS] SET PAGE_VERIFY CHECKSUM
  69. GO
  70. USE [Portal_PS]
  71. GO
  72. IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Portal_PS] MODIFY FILEGROUP [PRIMARY] DEFAULT
  73. GO
  75. --Restoring Database from File
  76. USE [master]
  77. GO
  78. --Change the highlighted section as per your requirment
  79. RESTORE DATABASE [Portal_PS]
  80. FROM DISK = N'Backup Location ( if it is in shared drive then use \\)'
  81. WITH FILE = 1,
  82. MOVE N'IFILEPORTAL_PROD' TO N'T:\MSSQL10_50.UAT\MSSQL\DATA\Portal_PS.mdf',
  83. MOVE N'IFILEPORTAL_PROD_log' TO N'U:\MSSQL10_50.UAT\MSSQL\Data\Portal_PS_log.ldf',
  84. NOUNLOAD, REPLACE, STATS = 10
  85. GO