Archive
A New Chapter
This week marked the start of a new chapter in my life as a SQL Server DBA. I am now a Senior Technology Specialist at Apparatus. The company is based out of Indianapolis, but I'll be spending the majority of my time working from home. The job is going to be a mixed bag, combining operations and project work for multiple clients. The project work is going to involve Analysis Services and Reporting Services. These are fairly new areas for me, as is working from home, but I really think I am up to the challenge and I'm extremely excited to jump in.
The major lesson I learned since my previous job change is to focus on the positive in all situations. When I left my previous job 18 months ago, I had been there for 13 years and there were a number of things I found frustrating. I was so frustrated that I failed to recognize the things I valued about the job. I accepted the first offer I received, because it was different than where I was. It took me a few months to realize I had run from my old job than running to a new opportunity. I don't regret my experience over the past year and a half, but it's taught me to always focus on the positive.
Stored Procedure to Automate Restores – Mark III
sp_AutomateDatabaseRestores, now with STOPAT! I've added parameter @stopat to allow generation and execution of a RESTORE statement WITH STOPAT. Check the Presentation Slides and Scripts link for a script to demonstrate this functionality.
USE [master] GO /****** Object: StoredProcedure [dbo].[sp_automateDBRestores] Script Date: 2/16/2014 8:48:07 PM ******/ IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_automateDBRestores') BEGIN DROP PROCEDURE [dbo].[sp_automateDBRestores] END GO /****** Object: StoredProcedure [dbo].[sp_automateDBRestores] Script Date: 2/16/2014 8:48:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_automateDBRestores] @sourcedbname SYSNAME, @restoredbname SYSNAME, @droprestoredb TINYINT = 1, @noexec TINYINT = 0, @stopat DATETIME = NULL 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 (backupstartdate DATETIME ,backupfinishdate DATETIME ,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) DECLARE @fullbackupdate DATETIME DECLARE @logbackupdate DATETIME DECLARE @logbackuplimit DATETIME DECLARE @errormessage VARCHAR(1000) --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 SELECT @logfile = LogicalName, @physlogfile = REPLACE(PhysicalName,'.ldf','_testrestore.ldf') FROM #databasefiles WHERE filetype = 1 SELECT @physlogfile AS PhysLogFile --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,'.mdf','_testrestore.mdf') FROM #databasefiles WHERE filetype = 0 SELECT @physdatafile AS PhysDataFile 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,'.mdf','_testrestore.mdf') 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 SELECT @fullbackupdate = 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' SELECT @logbackupdate = 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') --Get the most recent full backup file for the source database and insert it into the #backupfiles temp table INSERT INTO #backupfiles SELECT a.backup_start_date, a.backup_finish_date, 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 = @fullbackupdate --(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 a.backup_start_date, a.backup_finish_date, 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 > @fullbackupdate --(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 a.backup_start_date, a.backup_finish_date, 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 > @logbackupdate --(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 IF @stopat < @fullbackupdate BEGIN SET @errormessage = 'The @stopat values provided ' + CAST(@stopat AS VARCHAR(26)) + 'is older than the most recent full backup date of ' + CAST(@fullbackupdate AS VARCHAR(26)) + '. Please check the value and rerun.' RAISERROR (@errormessage, -- Message text. 16, -- Severity. 1 -- State. ); END IF @stopat IS NOT NULL BEGIN SELECT TOP 1 @logbackuplimit = backupfinishdate FROM #backupfiles WHERE backupfinishdate > @stopat DELETE FROM #backupfiles WHERE backupfinishdate > @logbackuplimit SELECT * FROM #backupfiles END --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 IF @stopat IS NOT NULL BEGIN --CAST(@stopat AS VARCHAR(26)) SET @sqlstr = @sqlstr + ' WITH NORECOVERY, STOPAT = ' + '''' + CONVERT(VARCHAR(30),@stopat,121) + '''' END ELSE BEGIN SET @sqlstr = @sqlstr + ' WITH RECOVERY' END 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 IF @stopat IS NOT NULL BEGIN SET @sqlstr = 'RESTORE DATABASE ' + @restoredbname + ' WITH RECOVERY' IF @noexec = 1 BEGIN PRINT @sqlstr END ELSE BEGIN EXEC(@sqlstr) END END END TRY BEGIN CATCH SELECT @sourcedbname AS DatabaseName, 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