How to Restore Multiple Databases in SQL Server

How To Restore Multiple Databases In SQL Server

Today I got the task where I have to restore 100 + databases from production to test server. We get time of four days to complete the task but being a DBA and good hands on T-SQL I decide not to do this task manually and wrote the script to do same.

Description: This Procedure is created to restore multiple databases from their backups.

Example:If i have to restore 100 databases with their respective backups thenI can keep all backups in same folder and run procedure.

Here all backup names in same format and must start with database name

example : master_today.bak , model_today.bak

The stored Procedure I created check for below options

1. Is xp_cmdshell is enable if not enable it and once work done disable same.
2. If database is not in simple recovery, take log backup of database.
3. Kill all sessions on database which we have to restore.
4. If database is new create data and log file and default location
5. If database already exists overwrite same and script will find values for move option.
6. Create / drop all temp tables used
7. Extract all backup files and their info from location

In this stored procedure we have to pass 3 Parameters

1. @SourceFile = where all backup files are placed

2. @dbprefixcount = characters in backup file name after db name (Banco + _09042014.bak)where backup file name is dbanme + date.bak example Banco_09042014.bak , Here in prefix we have 13 characters (Banco + _09042014.bak)

3. @logbackuppath = location to take log backup when db is not in simple recovery model

SYNTAX :

EXEC dbo.Multi_Restore @SourceFile = 'G:\Adhoc_Backup\Saurabh\'

,@dbprefixcount = 13

,@logbackuppath = 'G:\Adhoc_Backup\'

Here backup filename was : dbname_04092014.Bak

There fore : dbname + _04092014.Bak (13 character as prefix)

Attached Procedure code :

Script :

CREATEPROCEDURE Multi_Restore( @SourceFile nvarchar(4000)

,@dbprefixcount int

,@logbackuppath varchar (1000)

)

As

/*

Author : Saurabh Sinha

DATE : 04/09/2014

Modified : 04/09/2014

Description :

This Procedure is created to restore multiple databases from their backups.

for example if i have to restore 100 databases with their respective backups then

I can keep all backups in same folder and run procedure.

Here all backup names in same format and must start with database name

example : master_today.bak , model_today.bak

Parameters :

--> @SourceFile = where all backup files are placed

--> @dbprefixcount = characters in backup file name after db name (Banco + _09042014.bak)

where backup file name is dbanme + date.bak example Banco_09042014.bak

here in prefix we have 13 characters (Banco + _09042014.bak)

--> @logbackuppath = location to take log backup when db is not in simple recovery model

SYNTAX :

EXEC dba_admin.dbo.Multi_Restore @SourceFile = 'G:\Adhoc_Backup\Saurabh\'

,@dbprefixcount = 13

,@logbackuppath = 'G:\Adhoc_Backup\'

*/

Begin

SETNOCOUNTON

/* Declaring Variables */

DECLARE @path nvarchar(4000)

Declare @name asnvarchar (4000);

Declare @Backupname asnvarchar (4000);

DECLARE @Cmd ASVARCHAR(5000);

DECLARE @i asint

Declare @a asint

Declare @backuplocation varchar (5000)

Declare @Lfile1 varchar (200), @Lfile2 varchar (200)

Declare @Pfile1 varchar (2000), @Pfile2 varchar (2000)

Declare @logbackup varchar (1000)

Declare @sconfig int

--Declare @logbackuppath varchar (1000)

--Declare @dbprefixcount int

--DECLARE @SourceFile AS VARCHAR(5000);

/* verifying and removing temp objects*/

ifexists(select 1 fromsys.sysobjectswheretype='U')

select @i =COUNT(*)fromsys.sysobjectswheretype='U'

while @i 0

begin

SET @name =(Selecttop 1 name fromsys.sysobjectswheretype='U'and name like'#%')

SET @CMD ='Drop table '+ @name

Print @cmd

Exec (@cmd)

SET @i = @i-1

END

/* Create temp tables*/

Declare @version varchar (20)

SELECT @version =convert(varchar (100),SERVERPROPERTY('ProductVersion'))

select @version

if @version like'9%'or @version like'8%'

Begin

/* Create temp tables*/

Createtable #fileListTable

(

LogicalName nvarchar(128),

PhysicalName nvarchar(260),

[Type] char(1),

FileGroupName nvarchar(128),

Size numeric(20,0),

MaxSize numeric(20,0),

FileID bigint,

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,

IsReadOnl bit,

IsPresent bit--,

--TDEThumbprint varbinary(32)

)

End

Else

Begin

Createtable #fileListTable

(

LogicalName nvarchar(128),

PhysicalName nvarchar(260),

[Type] char(1),

FileGroupName nvarchar(128),

Size numeric(20,0),

MaxSize numeric(20,0),

FileID bigint,

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,

IsReadOnl bit,

IsPresent bit,

TDEThumbprint varbinary(32)

)

End CREATETable #MyTable(Results varchar(500))

CREATETable #MYFILES(create_date datetime,is_directory int,[Name] varchar (500))

CREATETable #MYFILES_main(id int identity, name varchar (500))

/*Initialization*/

--Set @logbackuppath = 'T:\adhocbackup\'

--SET @SourceFile = 'T:\adhocbackup\Saurabh\'

--SET @dbprefixcount = 19----> this value is the count of prefix after db name in backupfile

SET @backuplocation = @SourceFile

SET @Path = @SourceFile --> Pass the path in which you want to search

SET @cmd = 'dir '+ @path +' /A /OS /-C'

Set @i=1

/*Collecting data into temp tables */

INSERTINTO #MyTAble

EXECXP_CMDSHELL@cmd

INSERTINTO #MYFILES --(create_date datetime ,is_directory int ,[Name] varchar (500))

SELECTLEFT(Results, 20) [create_date],CASELTRIM(RTRIM(SUBSTRING(Results, 21, 18)))

WHEN''THEN 1 ELSE 0 END as is_directory,

SUBSTRING(Results, 40,Len(Results))AS [name]FROM #MyTable

WHEREISNULL(PATINDEX('%__/__/____%', Results), 0) != 0

DELETEFROM #MYFILES

where name in('..','.')or is_directory =1

insertinto #MYFILES_main select name from #MYFILES

Select @a = count(*) from #MYFILES_main

/* Checking all backup files and restoring database one by one */

While @a >= @i

Begin

/*Declaration */

Declare @RestoreStatement varchar (8000)

Declare @recovery_model_desc varchar (100)

DECLARE @spid int

/* Initialization */

Select @Backupname = name from #MYFILES_main where id = @i

Set @backuplocation = @SourceFile

Set @backuplocation = @backuplocation + @Backupname

SET @name = LEFT(@Backupname, LEN(@Backupname)- @dbprefixcount)

SET @RestoreStatement =N'RESTORE FILELISTONLY FROM DISK=N''' + @backuplocation + ''''

select @recovery_model_desc = recovery_model_desc from sys.databaseswhere name = @name

select @recovery_model_desc ,@name

insertinto #fileListTable

EXEC(@RestoreStatement)

Set @Pfile1 = Null

Set @Pfile2 = Null

Select @Lfile1 = LogicalName from #fileListTable where fileid =1

Select @Lfile2 = LogicalName from #fileListTable where fileid =2

Select @Pfile1 = filenamefromsys.sysaltfileswheredbid =db_id(@name )and fileid =1

select @Pfile2 = filenamefromsys.sysaltfileswheredbid =db_id(@name )and fileid = 2;

/* Taking log backup for db not in simple recovery*/

if @recovery_model_desc 'simple'

Begin

ifexists(select 1 fromsys.databaseswhere name = @name)

Begin

select @logbackup = @logbackuppath + @name + '.log'

backuplog @name todisk = @logbackup;

End

END

/* Killing all user session from db before restoring */

SELECT @spid = min(spid) frommaster.dbo.sysprocesseswheredbid =db_id(@name)

WHILE @spid IS NOTNULL

BEGIN

EXECUTE ('KILL '+ @spid)

SELECT @spid = min(spid) frommaster.dbo.sysprocesseswheredbid =db_id(@name)AND spid @spid

END

Select @name ,@backuplocation, @Lfile1, @Pfile1

/*Restoring database */

If @Pfile1 is notnull

Begin

Restoredatabase @name fromdisk = @backuplocation with stats=1,

move @Lfile1 to @Pfile1,

move @Lfile2 to @Pfile2,Replace

End

Else

Begin

Restoredatabase @name fromdisk = @backuplocation with stats=1

END

SET @i= @i+1

select'Restore for '+@name +' Completed '

END

/*Dropping temp tables */

DropTable #MYFILES

Droptable #MYFILES_main

DROPTable #MyTAble

Droptable #fileListTable

END