Dynamic Restore SQL Server

Introduction

Here is a script on how to have a restore of a db faster and easily (I hope!)

Using the Code

This script does the following things:

• Drops and recreates the db
• Checks the backup and extracts which settings have
• Restores the backup

To use the script, you only have to change the variable values.

--the db where restore backup
declare @db nvarchar(255)='db_to_restore'

--the path where db files are
declare @db_path nvarchar(max)='E:\Microsoft SQL Server\MSSQL10_50.SQL2K8R201\MSSQL\Data'
declare @db_log nvarchar(max)='F:\Microsoft SQL Server\MSSQL10_50.SQL2K8R201\MSSQL\Data'

--whe backup to restore
declare @bak nvarchar(255)='H:\TempBackup\backup.bak'

declare @sql nvarchar(max)=''

use master
/*
EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;


EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE
*/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = @db)
    --ALTER DATABASE TRUEMART_ZB_IT SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    execute('ALTER DATABASE '+@db+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
--go

IF  EXISTS (SELECT name FROM sys.databases WHERE name = @db)
    --DROP DATABASE TRUEMART_ZB_IT
    execute('DROP DATABASE '+@db+'')

set @sql=
'
CREATE DATABASE '+@db+' ON  PRIMARY 
( NAME = '+@db+', FILENAME = '''+@db_path+'\'+@db+'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = '+@db+'_log, FILENAME = '''+@db_log+'\'+@db+'_log.ldf'', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
'
print @sql
execute(@sql)

declare @tmp table(LogicalName nvarchar(128),PhysicalName nvarchar(260),Type char(1),FileGroupName nvarchar(128),Size numeric(20,0),MaxSize numeric(20,0),FileId tinyint,CreateLSN numeric(25,0),DropLSN numeric(25, 0),UniqueID uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit,TDEThumbprint varbinary(32))
insert @tmp
EXEC ('restore filelistonly from disk = ''' + @bak + '''')
--RESTORE FileListOnly FROM DISK = @bak

--select * from #tmp

set @sql=
'
RESTORE DATABASE '+@db+' 
FROM  DISK = ''' + @bak + '''
WITH  FILE = 1,  
MOVE ''' + (select top 1 logicalname from @tmp where type='d') + ''' TO '''+@db_path+'\'+@db+'.mdf'',
MOVE ''' + (select top 1 logicalname from @tmp where type='l') + ''' TO '''+@db_log+'\'+@db+'_log.ldf'',
NOUNLOAD,  REPLACE,  STATS = 1
'

print @sql
execute(@sql)

execute('ALTER DATABASE '+@db+' SET MULTI_USER')