Fully automated SQL Server Restore Script
Introduction
Restoring the latest backups of Production databases on a various test servers is one of the frequent activity needed to perform on a regular basis, various application development teams often request a restore the latest backup on new or existing test or quality servers, many times we receive this kind of requests in the eleventh hour when packing up to go back home.
Problem Definition
If we have to restore latest backups we needed to find fully qualified backup file path, then we need to find its logical names then data & log file locations all these activities need to be carried out manually.
Solution
We needed a better process which will do the same thing with minimal intervention.
Here’s the fully automated T-SQL script, this script you can schedule on the server on which you wish to restore databases, this script queries remote servers (where databases backed up) msdb database, backupset table is used to find latest full backup dates and backupmediafamily table to locate fully qualified backup file path.
Query accepts 5 following parameters.
- Linked Server – SQL Server from where you have taken backups and need to be restored.
- DataFileLocation – Optional, Specifies the folder for database data files. If not specified, data files are restored to the default data file location.
- LogFileLocation – Optional, Specifies the folder for all database log files. If not specified, log files are restored to the default log file location.
- ExcludeDbs – Can mention Databases which you want to exclude from restore list, by default master,mode,msdb will be excluded from restore list.
- RestoreDbs – Optional, by default restore all databases other than those mentioned in ExcludeDbs parameter, can mention any particular database needs to be restored.
T-SQL Script
SET NOCOUNT ON
DECLARE
@LinkedServer Varchar (255) = '' ,
--SQL Server from where you have taken backups and need to be restored.
@DataFileLocation Varchar (255) = '' ,
-- Optional, Specifies the folder for database data files. If not specified, data files are restored to the default data file location
@LogFileLocation Varchar (255) = '' ,
-- Optional, Specifies the folder for all database log files. If not specified, log files are restored to the default log file location
@ExcludeDbs Varchar (255) = '(' 'master' ' , ' 'model' ' , ' 'msdb' ')' ,
-- Databases do not wish to restore
@RestoreDbs Varchar (255) = ''
-- Optional, by default restores all databases other than those mentioned in ExcludeDbs list, can mention any particular database needs to be restored like '(''Database1'', ''Database2'')'
DECLARE @dbname Varchar(100) ,
@physicalpath Varchar (500) ,
@BackupDate Date ,
@cmd nvarchar( max ) ,
@logicalname Varchar (255) ,
@PhysicalFileName Varchar ( max ) ,
@type Varchar (5)
--Checks linked server exists, if not then linked server is added, requires ALTER ANY LINKED SERVER permission.
IF NOT EXISTS (SELECT * FROM SYS.servers Where name = @LinkedServer)
EXEC sp_addlinkedserver @LinkedServer
-- If data file location is not specified then data files will be restored to default data file location.
IF @DataFileLocation IS NULL
SELECT @DataFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1
-- If log file location is not specified then log files will be restored to default log file location.
IF @LogFileLocation IS NULL
SELECT @LogFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'mastlog.ldf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 2
CREATE TABLE #filelist (
LogicalName VARCHAR (255),
PhysicalName VARCHAR (500),
[Type] VARCHAR (1),
FileGroupName VARCHAR (64),
Size DECIMAL(20, 0),
MaxSize DECIMAL (25,0),
FileID bigint ,
CreateLSN DECIMAL (25,0),
DropLSN DECIMAL (25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN DECIMAL (25,0),
ReadWriteLSN DECIMAL (25,0),
BackupSizeInBytes DECIMAL (25,0),
SourceBlockSize INT ,
filegroupid INT ,
loggroupguid UNIQUEIDENTIFIER,
differentialbaseLSN DECIMAL (25,0),
differentialbaseGUID UNIQUEIDENTIFIER,
isreadonly BIT ,
ispresent BIT ,
TDEThumbprint Varchar (255))
--Queries backupset and backupmediafamily tables on remote msdb database to get latest full backup.
SET @cmd = 'DECLARE restore_db Cursor For SELECT a.database_name , BackupDate , physical_device_name FROM ['+ @LinkedServer+'].msdb.dbo.backupset A ' +
' INNER JOIN (SELECT database_name , BackupDate = MAX(backup_finish_date) ' +
' FROM [' +@LinkedServer+ '].msdb.dbo.backupset ' +
' WHERE type = ' 'D' ' '
IF @RestoreDbs IS NULL
SET @cmd = @cmd + ' AND database_name NOT IN '+ @ExcludeDbs +' And backup_finish_date >= DATEADD(MONTH , -1 , GETDATE()) '
ELSE
SET @cmd = @cmd + ' AND database_name IN '+ @RestoreDbs
SET @cmd = @cmd + ' GROUP BY database_name ) as b ' +
' ON A.database_name = b.database_name and a.backup_finish_date = BackupDate ' +
' INNER JOIN [' + @LinkedServer + '].msdb.dbo.backupmediafamily c ON c.media_set_id = a.media_set_id ORDER BY database_name '
exec sp_executesql @cmd
OPEN restore_db
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath
WHILE @@FETCH_STATUS = 0
BEGIN
--Check database to be restored is already there in this server, if yes then just restore with replace.
IF EXISTS ( SELECT * FROM sys.databases WHERE name = @dbname)
BEGIN
--Get rid of any existing connections, so that our restore process go smoothly.
DECLARE @kill varchar(8000) = '';
SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';'
FROm master.dbo.sysprocesses
WHERE dbid=db_id(''+ @dbname + '');
IF len(@kill) <> 0
exec sp_executesql @kill;
SET @cmd = 'RESTORE DATABASE [' + @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , REPLACE '
Exec sp_executesql @cmd;
END
ELSE
BEGIN
-- If database is not already there then go through the filelist and move to appropriate locations.
SET @cmd = 'RESTORE FILELISTONLY FROM DISK= '''+ @physicalpath +''''
INSERT INTO #filelist
EXEC (@cmd)
SET @cmd = 'RESTORE DATABASE ['+ @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , '
DECLARE file_list cursor for
SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
OPEN file_list
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
WHILE @@fetch_status = 0
BEGIN
-- If it is data file move to data file location.
IF @type = 'D'
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation +'\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
ELSE
-- Log files move to log file location.
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation + '\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
END
CLOSE file_list
DEALLOCATE file_list
truncate table #filelist
Exec sp_executesql @cmd
END
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath
END
CLOSE restore_db
DEALLOCATE restore_db
drop table #filelist
Permissions You need to have below mentioned permissions in place to execute this script.
Requires ALTER ANY LINKED SERVER permission refer http://technet.microsoft.com/en-us/library/ms190479.aspx
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the **sysadmin **and dbcreator fixed server roles and the owner (dbo) of the database refer http://technet.microsoft.com/en-us/library/ms186858.aspx.
Beginning in SQL Server 2008, obtaining information about a backup set or backup device requires CREATE DATABASE permission refer http://msdn.microsoft.com/en-us/library/ms173778.aspx
Conclusion
We have demonstrated here how we can dynamically restore production database backups on different servers, we can enhance this script to accommodate differential backups & log backups.
References
http://technet.microsoft.com/en-us/library/ms186858(v=sql.105).aspx
sp_addlinkedserver (Transact-SQL)
RESTORE FILELISTONLY (Transact-SQL)
Download
Download this T-SQL script from gallery.
This article participates in the TechNet Guru Contributions for March.