Archive
Archive for March, 2014
Upcoming Presentations
March 24, 2014
Leave a comment
I’m a presenting fool!
I am continuing to work toward my goal of averaging a presentation a month this year. So far I’ve presented Designing a Restore Strategy at I-380 PASS, QCPASS, and IndyPASS. I’m presenting it at the Chicago SQL Server User Group and SQL Saturday 291 – Chicago in April. The presentation centers around my stored procedure sp_AutomateDBRestores, which I hope to have documented fully this week. You can download the code here. If you are interested in attending the user group, you can RSVP here. If you haven’t registered for SQL Saturday Chicago, I recommend it highly. It is a wonderful opportunity to get a day’s worth of free training and network with your peers.
If you are looking for additional training, take a look at the pre-conference sessions being offered by Adam Machanic (b|t) and David Klee (b|t). Adam is the author of sp_whoisactive, which I can’t recommend highly enough, and is presenting Better Performance Through Parallelism. David is an expert in virtualizing SQL Server, so it is fitting that he is presenting Virtualization for SQL Server DBAs.
Categories: Presenting, Restores, SQL Saturday, SQL Server, Uncategorized
SQL Saturday, SQL Server
sp_exec_whoisactive
March 17, 2014
Leave a comment
I’ve posted a procedure that I wrote some time ago to make Adam Machanic’s sp_whoisactive easier to execute. It dumps the results of sp_whoisactive to a temporary table and selects the contents of that table. I’ve set EXEC sp_exec_whoisactive as one of my keyboard shortcuts in Management Studio. This makes it really easy to execute the proc repeatedly. If you aren’t using sp_whoisactive, I would advise stopping what you are doing and downloading it now. It is the most useful piece of code I’ve ever come across.
USE [master]
GO
--Drop procedure if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_exec_whoisactive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_exec_whoisactive
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Frank Gill
-- Create date: 2014-03-17
-- Description: This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then selects the contents
-- 'EXEC sp_whoisactive' can be set up as a keyboard shortcut to allow easy execution of the proc
-- =============================================
CREATE PROCEDURE sp_exec_whoisactive
AS
BEGIN
--Drop temp table if it exists
IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL
BEGIN
SELECT 'Dropping'
DROP TABLE #WhoIsActive
END
--Create temp table to hold the results of sp_whoisactive
CREATE TABLE #WhoIsActive
([dd hh:mm:ss.mss] VARCHAR(20)
,[dd hh:mm:ss.mss (avg)] VARCHAR(20)
,[session_id] SMALLINT
,[sql_text] XML
,[sql_command] XML
,[login_name] SYSNAME
,[wait_info] NVARCHAR(4000)
,[tran_log_writes] NVARCHAR(4000)
,[CPU] VARCHAR(30)
,[tempdb_allocations] VARCHAR(30)
,[tempdb_current] VARCHAR(30)
,[blocking_session_id] SMALLINT
,[blocked_session_count] VARCHAR(30)
,[reads] VARCHAR(30)
,[writes] VARCHAR(30)
,[physical_reads] VARCHAR(30)
,[query_plan] XML
,[used_memory] VARCHAR(30)
,[status] VARCHAR(30)
,[tran_start_time] DATETIME
,[open_tran_count] VARCHAR(30)
,[percent_complete] VARCHAR(30)
,[host_name] SYSNAME
,[database_name] SYSNAME
,[program_name] SYSNAME
,[start_time] DATETIME
,[login_time] DATETIME
,[request_id] INT
,[collection_time] DATETIME)
--Execute sp_whoisactive with #WhoIsActive set as the @destination_table parameter
EXEC master.dbo.sp_WhoIsActive
@get_plans = 2,
@get_outer_command = 1,
@get_transaction_info = 1,
@get_avg_time = 1,
@find_block_leaders = 1,
@destination_table = #WhoIsActive
--Select the contents of #WhoIsActive, ordered by the blocked_session_count
--The ORDER BY clause can be removed or modified as necessary
SELECT * FROM #WhoIsActive
ORDER BY blocked_session_count DESC
END
Adam is teaching an all-day pre-conference seminar entitled Better Performance Through Parallelism on Friday, April 25th. It is the day before SQL Saturday #291 Chicago. This is a great opportunity to get a day’s worth of training from the author of sp_whoisactive for a very reasonable price. You can get more information and register here.
Categories: Uncategorized
Working From Home – Two Weeks In
March 7, 2014
Leave a comment
I’ve been working for Apparatus for three weeks now, the last two of them from home. The work is interesting and I’ve finally achieved my goal of getting an Availability Group set up on my laptop. Plus, I know more about SPNs than I thought possible. And working from home has been a great experience.
The standing desk I built using plans from this site has worked out really well. I was afraid it wouldn’t be tall enough, but it has worked out perfectly.
My standing desk
I have also been eating healthier. More fruit, fewer fries. Hopefully, the combination of those things will let me get closer to my fighting weight, as seen here.
The other thing that has made it fun is I’d forgotten how much I love listening to music through speakers. That, and the fact I have the time and ability to listen to music on vinyl.
Some many albums, so little time…
I even listened to the first album I bought with my own money the other day. It sounds as good as it did in 1981.
Have you seen Junior’s grades?
My thanks to Kyle Neier (b|t) and everyone else at Apparatus for bringing me on board. And thanks to Jes Borland (b|t) and everyone else who has offered advice about working from home.
Categories: SQL Server, Working From Home
SQL Server
Stored Procedure to Automate Restores – Mark V
March 4, 2014
2 comments
I have added parameters @inphysdatapath and @inphyslogpath to the procedure. This allows alternate physical file restore paths to be passed in. You must pass in both parameters or the proc will error out.
Here is a sample execution:
USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_automateDBRestoresWithFileList] @sourcedbname = YourSourceDatabaseName, @restoredbname = YourRestoreDatabaseName,@droprestoredb = 0, @noexec = 1, @stopat = NULL, @inphysdatapath = 'C:\Restore', @inphyslogpath = 'C:\Restore' SELECT 'Return Value' = @return_value GO
If you have questions or comments about the procedure. leave them in the comments on this page.
</span>
<pre>
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/
IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_automateDBRestoresWithFileList')
BEGIN
DROP PROCEDURE [dbo].[sp_automateDBRestoresWithFileList]
END
GO
USE [master]
GO
--If the backupfiletype data type does not exist, create it
IF NOT EXISTS
(SELECT 1 FROM sys.types
WHERE name = 'backupfiletype')
BEGIN
CREATE TYPE backupfiletype AS TABLE
(backupfilename VARCHAR(255)
,backupfiletype VARCHAR(4));
END
/****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].sp_automateDBRestoresWithFileList
@sourcedbname SYSNAME,
@restoredbname SYSNAME = NULL,
@droprestoredb TINYINT = 1,
@noexec TINYINT = 0,
@stopat DATETIME = NULL,
@TVP backupfiletype READONLY
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
IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronly%')
BEGIN
DROP TABLE #headeronly
END
IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronlyfile%')
BEGIN
DROP TABLE #headeronlyfile
END
IF EXISTS(SELECT * FROM tempdb.sys.objects WHERE name LIKE 'FileListOnly%')
BEGIN
DROP TABLE #FileListOnly
END
--Create temp tables
CREATE TABLE #tvpfiles
(backupfilename VARCHAR(2000)
,backupfiletype VARCHAR(4))
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)
CREATE TABLE #headeronly
(BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType SMALLINT
,ExpirationDate DATETIME
,Compressed BIT
,Position SMALLINT
,DeviceType TINYINT
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate DATETIME
,BackupSize NUMERIC(20,0)
,FirstLSN NUMERIC(25,0)
,LastLSN NUMERIC(25,0)
,CheckpointLSN NUMERIC(25,0)
,DatabaseBackupLSN NUMERIC(25,0)
,BackupStartDate DATETIME
,BackupFinishDate DATETIME
,SortOrder SMALLINT
,CodePage SMALLINT
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel TINYINT
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID UNIQUEIDENTIFIER
,RecoveryForkID UNIQUEIDENTIFIER
,Collation NVARCHAR(128)
,FamilyGUID UNIQUEIDENTIFIER
,HasBulkLoggedData BIT
,IsSnapshot BIT
,IsReadOnly BIT
,IsSingleUser BIT
,HasBackupChecksums BIT
,IsDamaged BIT
,BeginsLogChain BIT
,HasIncompleteMetaData BIT
,IsForceOffline BIT
,IsCopyOnly BIT
,FirstRecoveryForkID UNIQUEIDENTIFIER
,ForkPointLSN NUMERIC(25,0)
,RecoveryModel NVARCHAR(60)
,DifferentialBaseLSN NUMERIC(25,0)
,DifferentialBaseGUID UNIQUEIDENTIFIER
,BackupTypeDescription NVARCHAR(60)
,BackupSetGUID UNIQUEIDENTIFIER
,CompressedBackupSize BIGINT
,Containment BIT)
CREATE TABLE #FileListOnly (
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,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY(32)
)
--Declare local variables
DECLARE @filecount SMALLINT
DECLARE @fullfilecount SMALLINT
DECLARE @loopcount SMALLINT
DECLARE @datafileloopcount SMALLINT
DECLARE @backuploopcount SMALLINT
DECLARE @backupfile VARCHAR(2000)
DECLARE @tvpbackupfile VARCHAR(2000)
DECLARE @sqlstr VARCHAR(MAX)
DECLARE @backupfiletype CHAR(4)
DECLARE @tvpbackupfiletype 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)
DECLARE @tvplimit SMALLINT
DECLARE @tvploopcount SMALLINT
--Initialize variables
SET @moveparm = ' WITH'
SET @fromstatement = ' FROM '
SET @loopcount = 1
SET @datafileloopcount = 1
SET @backuploopcount = 1
SET @tvploopcount = 1
--Get backup file name and file type from the table-valued parameter, if they exist
INSERT INTO #tvpfiles
SELECT backupfilename, backupfiletype FROM @TVP
--Get the count of rows from the table-valued parameter
SET @tvplimit = @@ROWCOUNT
SELECT @tvplimit AS TVPLimit
--Drop the restored database if it exists and @droprestoredb = 1
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1
BEGIN
--Drop the existing database if @sourcedbname and @restoredbname are different or if they are the same, but @TVP has been passed in
IF (@sourcedbname <> @restoredbname) OR (@sourcedbname = @restoredbname AND @tvplimit > 0)
BEGIN
SET @sqlstr = 'DROP DATABASE ' + @restoredbname
EXEC (@sqlstr)
END
ELSE
--This condition will drop the existing database, making sys.database_files unavailable
--An error is raised and the procedure stops.
BEGIN
RAISERROR ('You are attempting to restore over an existing database and have set @droprestoredb = 1. This will cause the existing database to be dropped, making sys.database_files unavailable. Please change the @restoredbname or set @droprestoredb = 0.', -- Message text.
16, -- Severity.
1 -- State.
);
END
END
--If a table-valued parameter is not passed in, get file information from sys.database_files
IF @tvplimit = 0
BEGIN
--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
END
--If table-valued parameter is passed in get database file information from RESTORE FILELISTONLY
--Insert the backupfilename and backupfiletype into #backupfiles
ELSE
BEGIN
SELECT @tvpbackupfile = backupfilename FROM #tvpfiles
WHERE backupfiletype = 'FULL'
SET @sqlstr = 'RESTORE FILELISTONLY FROM DISK = ' + '''' + @tvpbackupfile + ''''
PRINT @sqlstr
INSERT INTO #FileListOnly
EXEC(@sqlstr)
INSERT INTO #databasefiles
SELECT
@sourcedbname,
LogicalName,
PhysicalName,
CASE [Type]
WHEN 'D' THEN 0
WHEN 'L' THEN 1
END
FROM #FileListOnly
--Loop through files in #tvpfiles and run RESTORE HEADERONLY to get the BackupStartDate and BackupFinishDate for each file
WHILE @tvploopcount <= @tvplimit
BEGIN
SELECT TOP 1 @tvpbackupfile = backupfilename, @tvpbackupfiletype = backupfiletype
FROM #tvpfiles
IF @tvpbackupfiletype NOT IN ('FULL', 'DIFF', 'LOG')
BEGIN
RAISERROR ('You have passed an invalid backup file type. Valid backup file types are FULL, DIFF, and LOG. Please review your data.', -- Message text.
16, -- Severity.
1 -- State.
);
END
SELECT @sqlstr = 'RESTORE HEADERONLY FROM DISK = ' + '''' + @tvpbackupfile + ''''
INSERT INTO #headeronly
EXEC(@sqlstr)
INSERT INTO #backupfiles
SELECT BackupStartDate, BackupFinishDate, @tvpbackupfile, @tvpbackupfiletype
FROM #headeronly
SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0
SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1
SET @tvploopcount += 1
DELETE FROM #tvpfiles
WHERE backupfilename = @tvpbackupfile
DELETE FROM #headeronly
END
END
--Raise an error if the database contains more than one log file
IF @logfilecount > 1
BEGIN
RAISERROR ('You have more than one log file, which is a really bad idea. No restore 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
--If @droprestoredb = 1 and @restoredbname <> @droprestoredb. replace file extensions
IF @sourcedbname <> @restoredbname
BEGIN
UPDATE #databasefiles
SET PhysicalName = REPLACE(PhysicalName,'.mdf','_testrestore.mdf')
WHERE PhysicalName LIKE '%mdf%'
UPDATE #databasefiles
SET PhysicalName = REPLACE(PhysicalName,'.ldf','_testrestore.ldf')
WHERE PhysicalName LIKE '%ldf%'
END
--Get the logical and physical log file names
SELECT @logfile = LogicalName, @physlogfile = PhysicalName
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 = PhysicalName
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) PhysicalName 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
--If the table-valued parameter is not passed in, build the #backupfiles table from msdb tables
IF @tvplimit = 0
BEGIN
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
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
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
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
END
--If a @stopat date has been passed, delete LOG files newer than @stopat
IF @stopat IS NOT NULL
BEGIN
SELECT TOP 1 @logbackuplimit = backupfinishdate FROM #backupfiles
WHERE backupfinishdate > @stopat
DELETE FROM #backupfiles
WHERE backupfinishdate > @logbackuplimit
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 no FULL backups exists. error out
IF @fullfilecount = 0
BEGIN
RAISERROR ('There are no full backup files for your database.', -- Message text.
16, -- Severity.
1 -- State.
);
END
--While backup files exist, 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 statement 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 statement 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, execute @sqlstr
ELSE
BEGIN
EXEC (@sqlstr)
END
--Increment the loop count and continue processing backup files
SET @loopcount += 1
END
--If @stopat has been passed and a LOG backup exists, restore database with recovery
IF @stopat IS NOT NULL AND @logbackupcount > 0
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
Categories: Administration, SQL Server, T-SQL
SQL Server, T-SQL
Stored Procedure to Automate Restores – Mark IV
March 3, 2014
Leave a comment
Here is the latest version of sp_AutomateDBRestores. Previous versions used system tables to build the MOVE clause for the RESTORE statement and to identify valid backup files. That works fine if your instance is online and healthy. This version allows a list of backup files to be input as a table-valued parameter, in the event that your primary server is unavailable and you need to restore to another location. RESTORE FILELISTONLY is used to identify logical and physical data files and RESTORE HEADERONLY validates the backup files. Code to define the table-valued parameter is contained in the procedure. Its definition is:
CREATE TYPE backupfiletype AS TABLE (backupfilename VARCHAR(255) ,backupfiletype VARCHAR(4));
The procedure expects backupfiletype to equal FULL, DIFF, or LOG. Any other value will generate an error.
This is an example of calling the procedure with the table-valued parameter and a STOPAT date:
DECLARE @backuptvp AS backupfiletype
INSERT INTO @backuptvp
VALUES
('C:\Backup\AdventureWorks2012\FULL\AdventureWorks2012_FULL_20140303_110000.bak', 'FULL'),
('C:\Backup\AdventureWorks2012\DIFF\AdventureWorks2012_DIFF_20140303_110432.bak', 'DIFF'),
('C:\Backup\AdventureWorks2012\LOG\AdventureWorks2012_LOG_20140303_110438.trn', 'LOG'),
('C:\Backup\AdventureWorks2012\LOG\AdventureWorks2012_LOG_20140303_110444.trn', 'LOG'),
('C:\Backup\AdventureWorks2012\LOG\AdventureWorks2012_LOG_20140303_110500.trn', 'LOG')
EXEC sp_automateDBRestoresWithFileList
@sourcedbname = 'AdventureWorks2012',
@restoredbname = 'AdventureWorks2012test',
@droprestoredb = 0,
@noexec = 1,
@stopat = '2014-03-03 11:04:43.878',
@TVP = @backuptvp
Currently, the MOVE clause will use the physical data file of the original backup. If you need to restore to a different location, execute the procedure with @noexec = 1 and manually edit the restore commands. I will be posting an update shortly that will allow physical file names to be passed in as parameters.
As usual, I hope you can put this code to use. If you have questions or comments, use the comment link on this page.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/
IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_automateDBRestoresWithFileList')
BEGIN
DROP PROCEDURE [dbo].[sp_automateDBRestoresWithFileList]
END
GO
USE [master]
GO
--If the backupfiletype data type does not exist, create it
IF NOT EXISTS
(SELECT 1 FROM sys.types
WHERE name = 'backupfiletype')
BEGIN
CREATE TYPE backupfiletype AS TABLE
(backupfilename VARCHAR(255)
,backupfiletype VARCHAR(4));
END
/****** Object: StoredProcedure [dbo].[sp_automateDBRestoresWithFileList] Script Date: 2/16/2014 8:48:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].sp_automateDBRestoresWithFileList
@sourcedbname SYSNAME,
@restoredbname SYSNAME = NULL,
@droprestoredb TINYINT = 1,
@noexec TINYINT = 0,
@stopat DATETIME = NULL,
@TVP backupfiletype READONLY
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
IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronly%')
BEGIN
DROP TABLE #headeronly
END
IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%headeronlyfile%')
BEGIN
DROP TABLE #headeronlyfile
END
IF EXISTS(SELECT * FROM tempdb.sys.objects WHERE name LIKE 'FileListOnly%')
BEGIN
DROP TABLE #FileListOnly
END
--Create temp tables
CREATE TABLE #tvpfiles
(backupfilename VARCHAR(2000)
,backupfiletype VARCHAR(4))
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)
CREATE TABLE #headeronly
(BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType SMALLINT
,ExpirationDate DATETIME
,Compressed BIT
,Position SMALLINT
,DeviceType TINYINT
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate DATETIME
,BackupSize NUMERIC(20,0)
,FirstLSN NUMERIC(25,0)
,LastLSN NUMERIC(25,0)
,CheckpointLSN NUMERIC(25,0)
,DatabaseBackupLSN NUMERIC(25,0)
,BackupStartDate DATETIME
,BackupFinishDate DATETIME
,SortOrder SMALLINT
,CodePage SMALLINT
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel TINYINT
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID UNIQUEIDENTIFIER
,RecoveryForkID UNIQUEIDENTIFIER
,Collation NVARCHAR(128)
,FamilyGUID UNIQUEIDENTIFIER
,HasBulkLoggedData BIT
,IsSnapshot BIT
,IsReadOnly BIT
,IsSingleUser BIT
,HasBackupChecksums BIT
,IsDamaged BIT
,BeginsLogChain BIT
,HasIncompleteMetaData BIT
,IsForceOffline BIT
,IsCopyOnly BIT
,FirstRecoveryForkID UNIQUEIDENTIFIER
,ForkPointLSN NUMERIC(25,0)
,RecoveryModel NVARCHAR(60)
,DifferentialBaseLSN NUMERIC(25,0)
,DifferentialBaseGUID UNIQUEIDENTIFIER
,BackupTypeDescription NVARCHAR(60)
,BackupSetGUID UNIQUEIDENTIFIER
,CompressedBackupSize BIGINT
,Containment BIT)
CREATE TABLE #FileListOnly (
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,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY(32)
)
--Declare local variables
DECLARE @filecount SMALLINT
DECLARE @fullfilecount SMALLINT
DECLARE @loopcount SMALLINT
DECLARE @datafileloopcount SMALLINT
DECLARE @backuploopcount SMALLINT
DECLARE @backupfile VARCHAR(2000)
DECLARE @tvpbackupfile VARCHAR(2000)
DECLARE @sqlstr VARCHAR(MAX)
DECLARE @backupfiletype CHAR(4)
DECLARE @tvpbackupfiletype 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)
DECLARE @tvplimit SMALLINT
DECLARE @tvploopcount SMALLINT
--Initialize variables
SET @moveparm = ' WITH'
SET @fromstatement = ' FROM '
SET @loopcount = 1
SET @datafileloopcount = 1
SET @backuploopcount = 1
SET @tvploopcount = 1
--Get backup file name and file type from the table-valued parameter, if they exist
INSERT INTO #tvpfiles
SELECT backupfilename, backupfiletype FROM @TVP
--Get the count of rows from the table-valued parameter
SET @tvplimit = @@ROWCOUNT
SELECT @tvplimit AS TVPLimit
--Drop the restored database if it exists and @droprestoredb = 1
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @restoredbname) AND @droprestoredb = 1
BEGIN
--Drop the existing database if @sourcedbname and @restoredbname are different or if they are the same, but @TVP has been passed in
IF (@sourcedbname <> @restoredbname) OR (@sourcedbname = @restoredbname AND @tvplimit > 0)
BEGIN
SET @sqlstr = 'DROP DATABASE ' + @restoredbname
EXEC (@sqlstr)
END
ELSE
--This condition will drop the existing database, making sys.database_files unavailable
--An error is raised and the procedure stops.
BEGIN
RAISERROR ('You are attempting to restore over an existing database and have set @droprestoredb = 1. This will cause the existing database to be dropped, making sys.database_files unavailable. Please change the @restoredbname or set @droprestoredb = 0.', -- Message text.
16, -- Severity.
1 -- State.
);
END
END
--If a table-valued parameter is not passed in, get file information from sys.database_files
IF @tvplimit = 0
BEGIN
--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
END
--If table-valued parameter is passed in get database file information from RESTORE FILELISTONLY
--Insert the backupfilename and backupfiletype into #backupfiles
ELSE
BEGIN
SELECT @tvpbackupfile = backupfilename FROM #tvpfiles
WHERE backupfiletype = 'FULL'
SET @sqlstr = 'RESTORE FILELISTONLY FROM DISK = ' + '''' + @tvpbackupfile + ''''
PRINT @sqlstr
INSERT INTO #FileListOnly
EXEC(@sqlstr)
INSERT INTO #databasefiles
SELECT
@sourcedbname,
LogicalName,
PhysicalName,
CASE [Type]
WHEN 'D' THEN 0
WHEN 'L' THEN 1
END
FROM #FileListOnly
--Loop through files in #tvpfiles and run RESTORE HEADERONLY to get the BackupStartDate and BackupFinishDate for each file
WHILE @tvploopcount <= @tvplimit
BEGIN
SELECT TOP 1 @tvpbackupfile = backupfilename, @tvpbackupfiletype = backupfiletype
FROM #tvpfiles
IF @tvpbackupfiletype NOT IN ('FULL', 'DIFF', 'LOG')
BEGIN
RAISERROR ('You have passed an invalid backup file type. Valid backup file types are FULL, DIFF, and LOG. Please review your data.', -- Message text.
16, -- Severity.
1 -- State.
);
END
SELECT @sqlstr = 'RESTORE HEADERONLY FROM DISK = ' + '''' + @tvpbackupfile + ''''
INSERT INTO #headeronly
EXEC(@sqlstr)
INSERT INTO #backupfiles
SELECT BackupStartDate, BackupFinishDate, @tvpbackupfile, @tvpbackupfiletype
FROM #headeronly
SELECT @datafilecount = COUNT(*) FROM #databasefiles WHERE filetype = 0
SELECT @logfilecount = COUNT(*) FROM #databasefiles WHERE filetype = 1
SET @tvploopcount += 1
DELETE FROM #tvpfiles
WHERE backupfilename = @tvpbackupfile
DELETE FROM #headeronly
END
END
--Raise an error if the database contains more than one log file
IF @logfilecount > 1
BEGIN
RAISERROR ('You have more than one log file, which is a really bad idea. No restore 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
--If @droprestoredb = 1 and @restoredbname <> @droprestoredb. replace file extensions
IF @sourcedbname <> @restoredbname
BEGIN
UPDATE #databasefiles
SET PhysicalName = REPLACE(PhysicalName,'.mdf','_testrestore.mdf')
WHERE PhysicalName LIKE '%mdf%'
UPDATE #databasefiles
SET PhysicalName = REPLACE(PhysicalName,'.ldf','_testrestore.ldf')
WHERE PhysicalName LIKE '%ldf%'
END
--Get the logical and physical log file names
SELECT @logfile = LogicalName, @physlogfile = PhysicalName
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 = PhysicalName
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) PhysicalName 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
--If the table-valued parameter is not passed in, build the #backupfiles table from msdb tables
IF @tvplimit = 0
BEGIN
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
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
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
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
END
--If a @stopat date has been passed, delete LOG files newer than @stopat
IF @stopat IS NOT NULL
BEGIN
SELECT TOP 1 @logbackuplimit = backupfinishdate FROM #backupfiles
WHERE backupfinishdate > @stopat
DELETE FROM #backupfiles
WHERE backupfinishdate > @logbackuplimit
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 no FULL backups exists. error out
IF @fullfilecount = 0
BEGIN
RAISERROR ('There are no full backup files for your database.', -- Message text.
16, -- Severity.
1 -- State.
);
END
--While backup files exist, 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 statement 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 statement 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, execute @sqlstr
ELSE
BEGIN
EXEC (@sqlstr)
END
--Increment the loop count and continue processing backup files
SET @loopcount += 1
END
--If @stopat has been passed and a LOG backup exists, restore database with recovery
IF @stopat IS NOT NULL AND @logbackupcount > 0
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
Categories: Administration, SQL Server, T-SQL
SQL Server, T-SQL
Where I’m Presenting
SQL Saturday Indianapolis - August 13th
