Archive
Stored Procedure for Querying the Default Trace Using fn_trace_gettable
This morning I needed to query the default trace using fn_trace_gettable. fn_trace_gettable is a table-based function that returns the contents of a SQL trace file you pass to it. Like any table-based function, you SELECT from the function like you would a table and can include a column list and WHERE predicates. Here is an sample query:
SELECT * FROM fn_trace_gettable('C:\filepath\yourtracefile.trc', DEFAULT)
The DEFAULT parameter following the file name will read all existing rollover files, in addition to the file specified. I created stored procedure sp_QueryTraceFiles to return the columns I was interested in while dynamically building the WHERE predicates based on parameters passed in. See the comments for more information.
USE master GO -- ============================================= -- Created By: Frank Gill - skreebydba.com -- Date: 2014-01-30 -- Purpose: To select rows from the default trace -- Disclaimer: The author takes no responsibility if your production system -- descends into a smoking hole after running this proc. -- Like all code on the internet, run in test before running in prod. -- Sample Execution: -- EXEC dbo.sp_QueryTraceFiles -- @starttime = '2014-01-30 12:01:59', -- @servername = 'YourServer', -- @databasename = 'YourDatabase', -- @loginname = 'YourLogin', -- @applicationname = 'YourApplication', -- @textdata = 'YourText', -- @objectname = 'YourObject' -- ============================================= -- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'sp_QueryTraceFiles' ) DROP PROCEDURE dbo.sp_QueryTraceFiles GO CREATE PROCEDURE dbo.sp_QueryTraceFiles @starttime DATETIME = NULL, -- Procedure will return trace rows with a StartTime > @startime @servername SYSNAME = NULL, -- Procedure will return trace rows executed from server = @servername @databasename SYSNAME = NULL, -- Procedure will return trace rows executed against database = @databasename @loginname SYSNAME = NULL, -- Procedure will return trace rows executed by login = @loginname @applicationname VARCHAR(255) = NULL, -- Procedure will return trace rows executed by ApplicationName LIKE @applicationname @textdata VARCHAR(1000) = NULL, -- Procedure will return trace rows with TextData LIKE @textdata @objectname SYSNAME = NULL, -- Procedure will return trace rows executed against object = @objectname @noexec BIT = 0 -- @noexec = 1 will print the dynamic SQL statement instead of executing it AS -- Drop temp table if it exists IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%#traceinfo%') BEGIN DROP TABLE #traceinfo END -- Declare local variables DECLARE @tracefile VARCHAR(1000) DECLARE @sqlstr VARCHAR(2000) DECLARE @wherebit BIT = 0 -- Select fn_trace_getinfo into temp table #traceinfo to get the default trace file location and name -- traceid = 1 returns the default trace SELECT * INTO #traceinfo FROM fn_trace_getinfo(NULL) WHERE traceid = 1 -- Select default trace file name and location into variable for use in query -- property = 2 is the trace file name SELECT @tracefile = CAST(value AS VARCHAR(1000)) FROM #traceinfo WHERE property = 2 -- Build dynamic SQL string without WHERE predicates to return information from the default trace files using fn_trace_gettable -- The DEFAULT parm will read all existing rollover files -- Join to sys.trace_events to return the trace event name SELECT @sqlstr = 'SELECT trc.SPID AS [Process ID], trc.StartTime AS [Start Time], trc.ServerName AS [Server Name], trc.DatabaseName AS [Database Name], trc.LoginName AS [Login Name], trc.ApplicationName AS [Application Name], trc.TextData AS [Text Data], trc.ObjectName AS [Object Name], evt.name AS [Event Name] FROM fn_trace_gettable(' + '''' + @tracefile + '''' + ', DEFAULT) trc INNER JOIN sys.trace_events evt ON trc.EventClass = evt.trace_event_id' -- Append WHERE predicates if parameters are passed in IF @starttime IS NOT NULL BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.StartTime > ' + '''' + CAST(@starttime AS VARCHAR(30)) + '''' END IF @servername IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.ServerName = ' + '''' + @servername + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.ServerName = ' + '''' + @servername + '''' END END IF @loginname IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.LoginName = ' + '''' + @loginname + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.LoginName = ' + '''' + @loginname + '''' END END IF @applicationname IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.ApplicationName LIKE ' + '''' + '%' + @applicationname + '%' + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.ApplicationName LIKE ' + '''' + '%' + @applicationname + '%' + '''' END END IF @textdata IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.TextData LIKE ' + '''' + '%' + @textdata + '%' + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.TextData LIKE ' + '''' + '%' + @textdata + '%' + '''' END END IF @objectname IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.ObjectName = ' + '''' + @objectname + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.ObjectName = ' + '''' + @objectname + '''' END END IF @databasename IS NOT NULL BEGIN IF @wherebit = 0 BEGIN SET @wherebit = 1 SET @sqlstr = @sqlstr + ' WHERE trc.DatabaseName = ' + '''' + @databasename + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' AND trc.DatabaseName = ' + '''' + @databasename + '''' END END -- Append ORDER BY clause to the dynamic SQL string SET @sqlstr = @sqlstr + ' ORDER BY trc.StartTime DESC' IF @noexec = 1 BEGIN PRINT @sqlstr END ELSE BEGIN EXEC(@sqlstr) END GO
Stored Procedure to Automate Restores – Mark II
As promised in my last post, I've updated the automated restore stored procedure to handle error. There are now custom error messages for a database with no full backup files and for databases that have more than one log file. Additionally, I have added code to handle differential backup files.
Once again, I hope you can put this to use.
UPDATE
I've modified the proc to change the physical data and log file names if the @sourcedbname and @restoredbname are different. The previous version worked if the @sourcedbname was contained in the physical file names.
USE master GO -- ============================================= -- Frank Gill - skreebydba.com - 2013-01-12 -- sp_AutomateDatabaseRestores -- This script is provided with no guarantees -- Please run in your test environment before deploying to production -- The author takes no responsibility if your server descends into a smoking hole -- 2013-01-13 - Updated procedure to append _restoredb to the physical data and -- log file if @sourcedbname and @restoredbname are different -- ============================================= -- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'sp_AutomateDatabaseRestores' ) DROP PROCEDURE dbo.sp_AutomateDatabaseRestores GO CREATE PROCEDURE dbo.sp_AutomateDatabaseRestores @sourcedbname SYSNAME, @restoredbname SYSNAME, @droprestoredb TINYINT = 1, @noexec TINYINT = 0 AS SET NOCOUNT ON BEGIN TRY --Drop temp tables if they exist IF OBJECT_ID('tempdb..#backupfiles') IS NOT NULL BEGIN DROP TABLE #backupfiles END IF OBJECT_ID('tempdb..#databasefiles') IS NOT NULL BEGIN DROP TABLE #databasefiles END --Create temp tables CREATE TABLE #backupfiles (physicalfilename VARCHAR(2000) ,backuptype CHAR(4)) CREATE TABLE #databasefiles (DatabaseName SYSNAME ,LogicalName SYSNAME ,PhysicalName VARCHAR(2000) ,filetype TINYINT) --Declare local variables DECLARE @filecount SMALLINT DECLARE @fullfilecount SMALLINT DECLARE @loopcount SMALLINT DECLARE @datafileloopcount SMALLINT DECLARE @backuploopcount SMALLINT DECLARE @backupfile VARCHAR(2000) DECLARE @sqlstr VARCHAR(MAX) DECLARE @backupfiletype CHAR(4) DECLARE @datafilecount SMALLINT DECLARE @logfilecount SMALLINT DECLARE @logbackupcount SMALLINT DECLARE @diffbackupcount SMALLINT DECLARE @datafile VARCHAR(2000) DECLARE @moveparm VARCHAR(2000) DECLARE @fromstatement VARCHAR(2000) DECLARE @logfile SYSNAME DECLARE @physlogfile VARCHAR(2000) DECLARE @physdatafile VARCHAR(2000) --Drop the restored database if it exists and @droprestoredb = 1 IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1 BEGIN SET @sqlstr = 'DROP DATABASE ' + @restoredbname EXEC (@sqlstr) END --Initialize variables SET @moveparm = ' WITH' SET @fromstatement = ' FROM ' SET @loopcount = 1 SET @datafileloopcount = 1 SET @backuploopcount = 1 --Build dynamic SQL to get a list of data and log files for the source database SET @sqlstr = 'INSERT INTO #databasefiles SELECT ' + '''' + @sourcedbname + '''' + ' AS DatabaseName, name, physical_name, type FROM ' + @sourcedbname + '.sys.database_files' --Execute dynamic SQL to return data and log files for the source database EXEC (@sqlstr) --Get counts of data files and log files SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0 SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1 IF @logfilecount > 1 BEGIN RAISERROR ('You have more than one log file, which is a really bad idea. No backup for you. See this link for more information. http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/', -- Message text. 16, -- Severity. 1 -- State. ); END --Get the logical and physical log file names --If restoring to a different database name, append _restore onto the end of the physical file names IF @sourcedbname <> @restoredbname BEGIN SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,'.ldf','_restore.ldf') FROM #databasefiles WHERE filetype = 1 END ELSE BEGIN SELECT @logfile = LogicalName, @physlogfile = PhysicalName FROM #databasefiles WHERE filetype = 1 END --If the database contains one data file, build the MOVE clause for a single file IF @datafilecount = 1 BEGIN IF @sourcedbname <> @restoredbname BEGIN SELECT @datafile = LogicalName, @physdatafile = REPLACE(PhysicalName,'.mdf','_restore.mdf') FROM #databasefiles WHERE filetype = 0 END ELSE BEGIN SELECT @datafile = LogicalName, @physdatafile = PhysicalName FROM #databasefiles WHERE filetype = 0 END SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END ELSE --If the database contains more than one data file, loop through the list of files and build a multi-file MOVE statement IF @datafilecount > 1 BEGIN --SELECT @datafileloopcount, @datafilecount WHILE @datafileloopcount <= @datafilecount BEGIN IF @sourcedbname <> @restoredbname BEGIN SET @physdatafile = (SELECT TOP (1) REPLACE(PhysicalName,'.mdf','_restore.mdf') FROM #databasefiles WHERE filetype = 0) END ELSE BEGIN SET @physdatafile = (SELECT TOP (1) PhysicalName FROM #databasefiles WHERE filetype = 0) END SET @datafile = (SELECT TOP (1) LogicalName FROM #databasefiles WHERE filetype = 0) SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' DELETE TOP (1) FROM #databasefiles WHERE filetype = 0 SET @datafileloopcount += 1 END --Append the MOVE command for the log file to the MOVE clause and set it to NORECOVERY SET @moveparm = @moveparm + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END IF @droprestoredb = 0 BEGIN SET @moveparm = @moveparm + ', REPLACE' END --Get the most recent full backup file for the source database and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT b.physical_device_name, 'FULL' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND backup_finish_date = (SELECT MAX(c.backup_finish_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type = 'D') order by a.backup_finish_date ASC --Get a list of differential backup files older than the most recent full backup INSERT INTO #backupfiles SELECT TOP 1 b.physical_device_name, 'DIFF' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND a.type = 'I' AND backup_finish_date > (SELECT MAX(c.backup_finish_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type = 'D') order by a.backup_finish_date DESC --Get all log backupfiles run after the latest full or differential backup and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT b.physical_device_name, 'LOG' AS [backuptype] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND a.type = 'L' AND backup_finish_date > (SELECT MAX(c.backup_start_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND c.type IN ('D', 'I')) order by a.backup_finish_date ASC --Get counts of all backup files, full backup files, differential backup files, and log backup files for use in loop processing below SELECT @filecount = COUNT(*) FROM #backupfiles SELECT @fullfilecount = COUNT(*) FROM #backupfiles WHERE backuptype = 'FULL' SELECT @diffbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'DIFF' SELECT @logbackupcount = COUNT(*) FROM #backupfiles WHERE backuptype = 'LOG' IF @fullfilecount = 0 BEGIN RAISERROR ('There are no full backup files for your database.', -- Message text. 16, -- Severity. 1 -- State. ); END --While backup files exists, loop through them to generate the restore statement WHILE @loopcount <= @filecount BEGIN --Get the top backup file from the temp table SET @backupfiletype = (SELECT TOP 1 backuptype FROM #backupfiles) --If it is a full backup file, build the restore statement IF @backupfiletype = 'FULL' BEGIN --If there are no log backups to apply, replace NORECOVERY with RECOVERY in the MOVE clause IF (@logbackupcount = 0 AND @diffbackupcount = 0) BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END --If there is a single full backup file, build the RESTORE statement with that file IF @fullfilecount = 1 BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If there is more than one full backup file, build the RESTORE command for all full backup files ELSE IF @fullfilecount > 1 BEGIN WHILE @backuploopcount <= @fullfilecount BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles WHERE backuptype = 'FULL') SET @fromstatement = @fromstatement + 'DISK = ' + '''' + @backupfile + '''' + ',' DELETE TOP (1) FROM #backupfiles WHERE backuptype = 'FULL' SET @backuploopcount += 1 END --Truncate the trailing comma from the FROM clause SET @fromstatement = SUBSTRING(@fromstatement,1,(LEN(@fromstatement) - 1)) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + @fromstatement + @moveparm SET @loopcount += 1 --PRINT @sqlstr END END --One RESTORE datement will be built for the most recent differential backup ELSE IF @backupfiletype = 'DIFF' BEGIN IF @diffbackupcount = 1 AND @logbackupcount = 0 BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If it is a log backup file, build a RESTORE log statement --One RESTORE datement will be built for each log backup file ELSE IF @backupfiletype = 'LOG' BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE LOG ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' --If there is another log backup file, set the RESTORE to WITH NORECOVERY IF (SELECT COUNT(*) FROM #backupfiles) > 1 BEGIN SET @sqlstr = @sqlstr + ' WITH NORECOVERY' END --If there is not another log backup file, set the RESTORE to WITH RECOVERY ELSE BEGIN SET @sqlstr = @sqlstr + ' WITH RECOVERY' END DELETE TOP (1) FROM #backupfiles END --If @noexec = 1, PRINT @sqlstr IF @noexec = 1 BEGIN PRINT @sqlstr END --If @noexec <> 1, EXEC @sqlstr ELSE BEGIN EXEC (@sqlstr) END --Increment the loop count and continue processing backup files SET @loopcount += 1 END END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END CATCH GO
Stored Procedure to Automate Database Restores
It's been awhile since I've posted here, and I've already fallen behind on my New Year's resolution to post once a week. I will do what I can to keep up over the next 51 weeks.
For the past few months, I've been working to standardize the backup processes at my current job. Recently, I've needed to develop a process to restore a read-only copy of a production database using the most recent full backup file and any subsequent log backup files. The procedure below does that. It will work for database backups containing multiple backup files and databases containing multiple data files. However, if you have more than one log file in your database, you are out of luck. See Paul Randal's post Multiple Log Files and Why They're Bad for an excellent explanation of why you shouldn't do that. I will add error handling to deal with this scenario this weekend.
I hope you can put this to use. Let me know if you have questions.
USE YourDatabaseName GO -- ============================================= -- Frank Gill - skreebydba.com -- sp_AutomateDatabaseRestores -- This script is provided with no guarantees -- Please run in you test environment before deploying to production -- The author takes no responsibility if your server descends into a smoking hole -- ============================================= -- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'sp_AutomateDatabaseRestores' ) DROP PROCEDURE dbo.sp_AutomateDatabaseRestores GO CREATE PROCEDURE dbo.sp_AutomateDatabaseRestores @sourcedbname SYSNAME, @restoredbname SYSNAME, @droprestoredb TINYINT = 1, @noexec TINYINT = 0 AS SET NOCOUNT ON --Drop temp tables if they exist IF OBJECT_ID('tempdb..#backupfiles') IS NOT NULL BEGIN DROP TABLE #backupfiles END IF OBJECT_ID('tempdb..#databasefiles') IS NOT NULL BEGIN DROP TABLE #databasefiles END --Create temp tables CREATE TABLE #backupfiles (physicalfilename VARCHAR(2000) ,recoverymodel CHAR(4)) CREATE TABLE #databasefiles (DatabaseName SYSNAME ,LogicalName SYSNAME ,PhysicalName VARCHAR(2000) ,filetype TINYINT) --Declare local variables DECLARE @filecount SMALLINT DECLARE @fullfilecount SMALLINT DECLARE @loopcount SMALLINT DECLARE @datafileloopcount SMALLINT DECLARE @backuploopcount SMALLINT DECLARE @backupfile VARCHAR(2000) DECLARE @sqlstr VARCHAR(MAX) DECLARE @backupfiletype CHAR(4) DECLARE @datafilecount SMALLINT DECLARE @logfilecount SMALLINT DECLARE @logbackupcount SMALLINT DECLARE @datafile VARCHAR(2000) DECLARE @moveparm VARCHAR(2000) DECLARE @fromstatement VARCHAR(2000) DECLARE @logfile SYSNAME DECLARE @physlogfile VARCHAR(2000) DECLARE @physdatafile VARCHAR(2000) --Drop the restored database if it exists and @droprestoredb = 1 IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1 BEGIN SET @sqlstr = 'DROP DATABASE ' + @restoredbname EXEC (@sqlstr) END --Initialize variables SET @moveparm = ' WITH' SET @fromstatement = ' FROM ' SET @loopcount = 1 SET @datafileloopcount = 1 SET @backuploopcount = 1 --Build dynamic SQL to get a list of data and log files for the source database SET @sqlstr = 'INSERT INTO #databasefiles SELECT ' + '''' + @sourcedbname + '''' + ' AS DatabaseName, name, physical_name, type FROM ' + @sourcedbname + '.sys.database_files' --Execute dynamic SQL to return data and log files for the source database EXEC (@sqlstr) --Get counts of data files and log files SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0 SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1 --Get the logical and physical log file names SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 1 --If the database contains one data file, build the MOVE clause for a single file IF @datafilecount = 1 BEGIN SELECT @datafile = LogicalName, @physdatafile = REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 0 SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END ELSE --If the database contains more than one data file, loop through the list of files and build a multi-file MOVE statement IF @datafilecount > 1 BEGIN --SELECT @datafileloopcount, @datafilecount WHILE @datafileloopcount <= @datafilecount BEGIN --SELECT TOP (1) LogicalName, REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 0 SET @physdatafile = (SELECT TOP (1) REPLACE(PhysicalName,@sourcedbname,@restoredbname) FROM #databasefiles WHERE filetype = 0) SET @datafile = (SELECT TOP (1) LogicalName FROM #databasefiles WHERE filetype = 0) SET @moveparm = @moveparm + ' MOVE ' + '''' + @datafile + '''' + ' TO ' + '''' + @physdatafile + '''' + ',' DELETE TOP (1) FROM #databasefiles WHERE filetype = 0 SET @datafileloopcount += 1 END --Append the MOVE command for the log file to the MOVE clause and set it to NORECOVERY SET @moveparm = @moveparm + ' MOVE ' + '''' + @logfile + '''' + ' TO ' + '''' + @physlogfile + '''' + ', NORECOVERY' END IF @droprestoredb = 0 BEGIN SET @moveparm = @moveparm + ', REPLACE' END --Get the most recent full backup file for the source database and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT b.physical_device_name, 'FULL' AS [RecoveryModel] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND backup_finish_date = (SELECT MAX(c.backup_finish_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND REVERSE(SUBSTRING(REVERSE(d.physical_device_name),1,3)) = 'BAK') order by a.backup_finish_date ASC --Get all log backupfiles run after the latest full backup and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT b.physical_device_name, 'LOG' AS [RecoveryModel] from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id where a.database_name = @sourcedbname AND REVERSE(SUBSTRING(REVERSE(b.physical_device_name),1,3)) = 'TRN' AND backup_finish_date > (SELECT MAX(c.backup_start_date) FROM msdb.dbo.backupset c INNER JOIN msdb.dbo.backupmediafamily d ON c.media_set_id = d.media_set_id WHERE c.database_name = @sourcedbname AND REVERSE(SUBSTRING(REVERSE(d.physical_device_name),1,3)) = 'BAK') order by a.backup_finish_date ASC --Get counts of all backup files, full backup files, and log backup files for use in loop processing below SELECT @filecount = COUNT(*) FROM #backupfiles SELECT @fullfilecount = COUNT(*) FROM #backupfiles WHERE recoverymodel = 'FULL' SELECT @logbackupcount = COUNT(*) FROM #backupfiles WHERE recoverymodel = 'LOG' --While backup files exists, loop through them to generate the restore statement WHILE @loopcount <= @filecount BEGIN --Get the top backup file from the temp table SET @backupfiletype = (SELECT TOP 1 recoverymodel FROM #backupfiles) --If it is a full backup file, build the restore statement IF @backupfiletype = 'FULL' BEGIN --If there are no log backups to apply, replace NORECOVERY with RECOVERY in the MOVE clause IF @logbackupcount = 0 BEGIN SET @moveparm = REPLACE(@moveparm, 'NORECOVERY', 'RECOVERY') END --If there is a single full backup file, build the RESTORE statement with that file IF @fullfilecount = 1 BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' + @moveparm --PRINT @sqlstr DELETE TOP (1) FROM #backupfiles END --If there is more than one full backup file, build the RESTORE command for all full backup files ELSE IF @fullfilecount > 1 BEGIN WHILE @backuploopcount <= @fullfilecount BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles WHERE recoverymodel = 'FULL') SET @fromstatement = @fromstatement + 'DISK = ' + '''' + @backupfile + '''' + ',' DELETE TOP (1) FROM #backupfiles WHERE recoverymodel = 'FULL' SET @backuploopcount += 1 END --Truncate the trailing comma from the FROM clause SET @fromstatement = SUBSTRING(@fromstatement,1,(LEN(@fromstatement) - 1)) SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + @fromstatement + @moveparm SET @loopcount += 1 --PRINT @sqlstr END END --If it is a log backup file, build a RESTORE log statement --One RESTORE datement will be built for each log backup file ELSE IF @backupfiletype = 'LOG' BEGIN SET @backupfile = (SELECT TOP 1 physicalfilename FROM #backupfiles) SET @sqlstr = 'RESTORE LOG ' + @restoredbname + ' FROM DISK = ' + '''' + @backupfile + '''' --If there is another log backup file, set the RESTORE to WITH NORECOVERY IF (SELECT COUNT(*) FROM #backupfiles) > 1 BEGIN SET @sqlstr = @sqlstr + ' WITH NORECOVERY' END --If there is not another log backup file, set the RESTORE to WITH RECOVERY ELSE BEGIN SET @sqlstr = @sqlstr + ' WITH RECOVERY' END DELETE TOP (1) FROM #backupfiles END --If @noexec = 1, PRINT @sqlstr IF @noexec = 1 BEGIN PRINT @sqlstr END --If @noexec <> 1, EXEC @sqlstr ELSE BEGIN EXEC (@sqlstr) END --Increment the loop count and continue processing backup files SET @loopcount += 1 END GO