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.
declare @db nvarchar(255)='db_to_restore'
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'
declare @bak nvarchar(255)='H:\TempBackup\backup.bak'
declare @sql nvarchar(max)=''
use master
IF EXISTS (SELECT name FROM sys.databases WHERE name = @db)
execute('ALTER DATABASE '+@db+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
IF EXISTS (SELECT name FROM sys.databases WHERE name = @db)
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 + '''')
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')