Restore DB Backup using Script in SQL Server

Hi, in this blog we will restore the Database using the script.

-- Dropping database
USE [master]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Portal_PS'
GO
USE [master]
GO
ALTER DATABASE [Portal_PS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [Portal_PS]
GO
--Creating database
CREATE DATABASE [Portal_PS] ON PRIMARY
( NAME = N'Portal_PS', FILENAME = N'T:\MSSQL10_50.UAT\MSSQL\DATA\Portal_PS.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
--Change the highlighted section as per your requirment
LOG ON
( NAME = N'Portal_PS_log', FILENAME = N'U:\MSSQL10_50.UAT\MSSQL\Data\Portal_PS_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
--Change the highlighted section as per your requirment
GO
ALTER DATABASE [Portal_PS] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [Portal_PS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Portal_PS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Portal_PS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Portal_PS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Portal_PS] SET ARITHABORT OFF
GO
ALTER DATABASE [Portal_PS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Portal_PS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Portal_PS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Portal_PS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Portal_PS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Portal_PS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Portal_PS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Portal_PS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Portal_PS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Portal_PS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Portal_PS] SET DISABLE_BROKER
GO
ALTER DATABASE [Portal_PS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Portal_PS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Portal_PS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Portal_PS] SET READ_WRITE
GO
ALTER DATABASE [Portal_PS] SET RECOVERY FULL
GO
ALTER DATABASE [Portal_PS] SET MULTI_USER
GO
ALTER DATABASE [Portal_PS] SET PAGE_VERIFY CHECKSUM
GO
USE [Portal_PS]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Portal_PS] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

--Restoring Database from File
USE [master]
GO
--Change the highlighted section as per your requirment
RESTORE DATABASE [Portal_PS]
FROM DISK = N'Backup Location ( if it is in shared drive then use \\)'
WITH FILE = 1,
MOVE N'IFILEPORTAL_PROD' TO N'T:\MSSQL10_50.UAT\MSSQL\DATA\Portal_PS.mdf',
MOVE N'IFILEPORTAL_PROD_log' TO N'U:\MSSQL10_50.UAT\MSSQL\Data\Portal_PS_log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO