Archive

Archive for the ‘Administration’ Category

Fun With READ_ONLY_ROUTING_LIST Syntax

July 13, 2016 Leave a comment

I finally am getting to work with SQL Server 2016 and spent the past two days building out an Availability Group to test the new read-only replica functionality.  This allows connections with an ApplicationIntent of ReadOnly to round-robin between all instances in the READ_ONLY_ROUTING_LIST, load balancing the read-only activity.

For my test, I built out three replicas and set all three to be readable secondaries.  Using Microsoft’s documentation on configuring read-only routing, I coded the following:

ALTER AVAILABILITY GROUP SKREEBYHA
MODIFY REPLICA ON 'AGNODE1\SKREEBYAG'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG')
)
);

To test the new setup, I used the SQLCMD examples from Eric Russo’s blog post on Load Balancing 101.  Running the following command:

SQLCMD -S SKREEBYLIST -K ReadOnly -d testdb -Q "SELECT @@SERVERNAME"

returned AGNODE2\SKREEBYAG in each case. I could not figure out what I was doing wrong. After much frustration, I noticed the routing check query at the bottom of Eric’s post:

SELECT ar_Primary.replica_server_name 'Primary',
ar_secondary.replica_server_name 'Route_to',
ror.routing_priority 'Priority'
FROM sys.availability_read_only_routing_lists ror
INNER JOIN sys.availability_replicas ar_Primary
ON ror.replica_id = ar_Primary.replica_id
INNER JOIN sys.availability_replicas ar_secondary
ON ror.read_only_replica_id = ar_secondary.replica_id
INNER JOIN sys.availability_groups ag
ON ag.group_id = ar_Primary.group_id
WHERE ag.name = 'SKREEBYHA'
ORDER BY 1,3

Running it returned the following results:

RoutingListResults

So, in technical terms, something was screwy.  Looking at Eric’s post again, I noticed his third example for setting a routing list:

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON 'MyNode3'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2'))
)
)

Notice the second set of parentheses around the READ_ONLY_ROUTING_LIST value. Based on this, I changed my code to:

ALTER AVAILABILITY GROUP SKREEBYHA
MODIFY REPLICA ON 'AGNODE1\SKREEBYAG'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=(('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG'))
)
);

and, voila, the routing list check query returned the following:

RoutingListResultsFixed

Rerunning the SQLCMD test, the read-only connections round-robined between AGNODE2 and AGNODE3.

Many thanks to Eric Russo (b|t) for a great blog post.  I hope this saves you some frustration when working with read-only routing.

 

 

Joining sys.dm_exec_requests to sp_whoisactive results

October 28, 2015 1 comment

Previously, I’ve blogged about returning formatted estimated_completion_time values from sys.dm_exec_requests and getting sortable output from sp_whoisactive. In this post I combine the two, joining the two result sets together. In my environment, we use Ola Hallengren’s Maintenance Solution to run our backups. Because the backup job defaults to run backups for all user databases, it can be difficult to tell which database is currently being backed up.

USE [master]
GO
 
--Drop procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_join_whoisactive_execrequests]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_join_whoisactive_execrequests
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author:      Frank Gill
-- Create date: 2015-10-27
-- Description: This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then joins those results to 
-- sys.dm_exec_requests.  This will return estimated completion time for the following commands along with the statement being executed
-- in a single pass:
-- ALTER INDEX REORGANIZE
-- AUTO_SHRINK option with ALTER DATABASE
-- BACKUP DATABASE
-- DBCC CHECKDB
-- DBCC CHECKFILEGROUP
-- DBCC CHECKTABLE
-- DBCC INDEXDEFRAG
-- DBCC SHRINKDATABASE
-- DBCC SHRINKFILE
-- RECOVERY
-- RESTORE DATABASE,
-- ROLLBACK
-- TDE ENCRYPTION
-- ============================================= */
CREATE PROCEDURE sp_join_whoisactive_execrequests
AS
BEGIN
 
	IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL 
	BEGIN
		SELECT 'Dropping'
		DROP TABLE #WhoIsActive
	END
 
	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 and write the result set to the temp table created above */
	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

	/* Join the #whoisactive temp table to sys.dm_exec_requests to get estimated completion time and query information in one pass */ 
	SELECT CASE WHEN ((r.estimated_completion_time/1000)%3600/60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60) 
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%3600/60)
	END  + ':' + 
	CASE WHEN ((r.estimated_completion_time/1000)%60) < 10 THEN '0' +
	CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	ELSE CONVERT(VARCHAR(10),(r.estimated_completion_time/1000)%60)
	END
	AS [Time Remaining],
	r.percent_complete,
	r.session_id,
	w.login_name,
	w.[host_name],
	w.sql_command,
	w.sql_text 
	FROM #WhoIsActive w
	RIGHT OUTER JOIN sys.dm_exec_requests r
	ON r.session_id = w.session_id
	WHERE r.percent_complete > 0;
 
END

This procedure takes the output from sp_whoisactive and joins it to sys.dm_exec_requests on session_id, returning the estimated completion time and current backup statement in a single pass. Here is a sample result set from the procedure (Click to Enlarge):

SpWhoIsActiveExecRequestsResults

The sql_command column contains the full command being run, in this example Ola Hallengren’s DatabaseBackup procedure. The sql_text column contains the actual SQL statement being run, BACKUP DATABASE [AdventureWorks2012] TO DISK…. The DatabaseBackup procedure runs database backups in alphabetical order based on the database name, so this allows tracking a job progress.

Many thanks to Adam Machanic (b|t) for sp_whoisactive, Ola Hallengren (b|t) for his Maintenance Solution, and Aaron Bertrand (b|t) for his advice on formatting the estimated completion time more elegantly. I finally had an opportunity to use it.

I hope you can put this procedure to good use. Let me know if you have any questions.

Generate ALTER Statements for Database Moves

October 13, 2014 1 comment

Last week, I posted Identifying Corrupt Rows USING DBCC CHECKDB and DBCC PAGE. I wrote the scripts in that post to identify corrupt rows in a client’s database. As we continued to investigate, things got strange. Repeated runs of DBCC CHECKDB were generating different results. Each one showed serious corruption, but on different pages with each run. In an effort to remove the storage system from the list of potential causes, I needed to move to data and log files for the database to a a different storage array. This post from TechNet gave me the steps I needed to run.

In this case, only one database had to be moved. To move more than one database, I wrote the following script to generate the statements specified in the TechNet article. In addition to the ALTER DATABASE statements, the script checks to see if the database is part of an availability group. If it is, a statement is generated to remove it. As adding a database to an availability group is far more complicated than removing it, I will leave that to the user.

As usual, I hope you find this useful. And remember, executing code you find on the internet against your production instance always has the potential to become a resume updating event. Use caution and test.

/*---------------------------------------------------------------
Created By - Frank Gill
Created On - 2014-10-13
 
Script to generate ALTER DATABASE statement to move a database to
a different storage location
---------------------------------------------------------------*/

USE master;

SET NOCOUNT ON;

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#dbfiles') IS NOT NULL
BEGIN

	DROP TABLE #dbfiles;

END

/* Declare local variables and create temp table */
DECLARE @dbname SYSNAME;
DECLARE @agname SYSNAME;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @physicalname NVARCHAR(260);
DECLARE @logicalname SYSNAME;
DECLARE @filetype TINYINT;
DECLARE @datapath NVARCHAR(1000);
DECLARE @logpath NVARCHAR(1000);
DECLARE @agdb BIT = 0;
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;

CREATE TABLE #dbfiles
(logicalname SYSNAME
,physicalname NVARCHAR(260)
,filetype TINYINT)

/* Initialize variables */
SET @dbname = 'Your_Database';
SET @datapath = 'Your_Data_Path';
SET @logpath = 'Your_Log_Path';

/* If the database is part of an availability group, generate a statement to remove it prior to taking the database offline */
IF(SELECT replica_ID FROM sys.databases WHERE name = @dbname) IS NOT NULL
BEGIN

	PRINT '/* This database is part of an availability group and must be removed prior to taking the database offline
   You will need to add the database back to the availability group after the database files are moved */'

	SELECT @agname = g.name FROM sys.databases d
	INNER JOIN sys.availability_replicas r
	ON d.replica_id = r.replica_id
	INNER JOIN sys.availability_groups g
	ON g.group_id = r.group_id
	WHERE d.name = @dbname;

	SET @sqlstr = 'ALTER AVAILABILITY GROUP [' + @agname + ']
REMOVE DATABASE [' + @dbname + '];';

	PRINT @sqlstr;
	
	SET @agdb = 1;

END

/* Generate a statement to set the database offline */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET OFFLINE WITH ROLLBACK IMMEDIATE';

PRINT @sqlstr;

/* Get a list of database files for your database and insert them into the temp table for use in the loop */
SET @sqlstr = 'INSERT INTO #dbfiles
SELECT name, physical_name, type FROM ' + @dbname + '.sys.database_files;'

EXEC sp_executesql @sqlstr;

/* Set the loop limit to the number of files returned from the insert statement */
SET @looplimit = @@ROWCOUNT;

WHILE @loopcount &lt;= @looplimit
BEGIN

	/* Get the first set of physical and logical file name and generate an ALTER DATABASE statement using the file type to determine the new physical location */

	SELECT TOP 1 @logicalname = logicalname, @physicalname =  REVERSE(SUBSTRING(REVERSE(physicalname),1,(CHARINDEX('\',REVERSE(physicalname))-1))), @filetype = filetype FROM #dbfiles;

	IF @filetype = 0
	BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @datapath + @physicalname + '''' + ');';
 
 END
 ELSE IF @filetype = 1
 BEGIN

 SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
MODIFY FILE 
(NAME = ' + @logicalname + ' 
,FILENAME = ''' + @logpath + @physicalname + '''' + ');';
 END

	PRINT @sqlstr;

	SET @loopcount += 1;

	DELETE TOP (1) FROM #dbfiles 

END

/* Generate a statement to bring the database back online */
SET @sqlstr = 'ALTER DATABASE ' + @dbname + '
SET ONLINE';

PRINT @sqlstr;


IF @agdb = 1
BEGIN

	PRINT '/* Reminder!  ' + @dbname + 'is part of an availability group 
You will need to add the database back to the availability group if necessary */'

END

Identifying Corrupt Rows Using DBCC CHECKDB and DBCC PAGE

October 6, 2014 1 comment

Four months ago, when I blogged about giving all the presentations, things were starting to ramp up pat my new job. I’ve really enjoyed the last few months, but the combination of my presenting schedule and the responsibilities of the new gig has left little time for blogging.

This week, I ran into a corruption issue that gave me a chance to make a deeper dive into internals than I have in a while. It also gave me the opportunity to develop a script to dump the results of DBCC PAGE to a table.

This week, we discovered corruption in a client’s database we were migrating. Digging deeper into the results of DBCC CHECKDB, I found that six objects in the database were corrupted badly enough to require data loss. One of those objects contained 47 irreparable pages.

In an effort to provide the client as much information as possible about the corrupted data, I set out to identify the rows contained on the corrupt pages. Here are the scripts I used.

First, I needed to create tables to hold the results of DBCC CHECKDB and DBCC PAGE. For the PageResults table, I guessed at the column definitions. For DBCCResults, I made all of the numeric columns BIGINT to prevent conversion errors.

USE [Your_Database]
GO

/* Create table to hold the results of DBCC CHECKDB */

/****** Object:  Table [dbo].[DBCCResults]    Script Date: 9/30/2014 11:00:47 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF OBJECT_ID('Your_Database..DBCCResults') IS NOT NULL
BEGIN

	DROP TABLE DBCCResults;

END

IF OBJECT_ID('Your_Database..PageResults') IS NOT NULL
BEGIN

	DROP TABLE PageResults;

END

CREATE TABLE PageResults
(ParentObject VARCHAR(100)
,[Object] VARCHAR(1000)
,[Field] VARCHAR(100)
,[VALUE] VARCHAR(1000))

CREATE TABLE [dbo].[DBCCResults](
	[Error] [bigint] NULL,
	[Level] [bigint] NULL,
	[State] [bigint] NULL,
	[MessageText] [varchar](7000) NULL,
	[RepairLevel] [varchar](7000) NULL,
	[Status] [bigint] NULL,
	[DbId] [bigint] NULL,
	[DbFragID] [bigint] NULL,
	[ObjId] [bigint] NULL,
	[IndId] [bigint] NULL,
	[PartID] [bigint] NULL,
	[AllocID] [bigint] NULL,
	[RidDbid] [bigint] NULL,
	[RidPruid] [bigint] NULL,
	[File] [bigint] NULL,
	[Page] [bigint] NULL,
	[Slot] [bigint] NULL,
	[RefDbid] [bigint] NULL,
	[RefPruId] [bigint] NULL,
	[RefFile] [bigint] NULL,
	[RefPage] [bigint] NULL,
	[RefSlot] [bigint] NULL,
	[Allocation] [bigint] NULL
) ON [PRIMARY]

GO

After creating the tables, I needed to generate DBCC CHECKDB statements for each database. The script below uses sys.databases as the driver, and you can modify the results with an appropriate WHERE clause. The script is set to print the DBCC CHECKDB commands. Replacing the PRINT statement with EXEC sp_executesql will execute the statement after generation. Upon execution, the results of DBCC CHECKDB will be inserted into the DBCC CHECKDB table.

USE master;
GO

/* Drop temp table if it exists */

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN

	DROP TABLE #databases

END

/* Declare local variables and create temp tables */
DECLARE @sqlstr NVARCHAR(2000);
DECLARE @dbname SYSNAME;
DECLARE @loopcount TINYINT = 1;
DECLARE @looplimit TINYINT;

CREATE TABLE #databases
(dbname SYSNAME);

/* This will generate statements for all user databases 
   Change the WHERE clause to limit or expand the results */
INSERT INTO #databases
SELECT name FROM master.sys.databases
WHERE name > 4;

/* Get the loop limit */
SELECT @looplimit = @@ROWCOUNT;

/* Build the DBCC CHECKDB statement for each database 
   This code will print each statement
   Replace the PRINT statement with EXEC sp_executesql @sql str to execute the code */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT TOP 1 @dbname = dbname FROM #databases;

	SELECT @sqlstr = 'INSERT INTO Your_Database..DBCCResults EXEC(''DBCC CHECKDB(' + @dbname + ') WITH TABLERESULTS, NO_INFOMSGS'')';

	PRINT @sqlstr;

	SELECT @loopcount += 1;

	DELETE FROM #databases WHERE dbname = @dbname;

END


Once, the DBCCResults table was populated, I needed to dump the contents of the irreparable pages. For corrupted pages, DBCC CHECKDB returns two possible RepairLevels — repair_rebuild indicates the page can be rebuilt to fix the corruption; repair_allow_data_loss means that the page is corrupted so badly it cannot be repaired. The only option is to delete the page and restore consistency to the object structure. This link from Paul Randal provides a much more detailed explanation.

USE Your_Database;

/* Drop temp tables if they exist */
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#DBCCPages') IS NOT NULL
BEGIN
	
	DROP TABLE #DBCCPages;

END

IF OBJECT_ID('tempdb..#PageResults') IS NOT NULL
BEGIN
	
	DROP TABLE #PageResults;

END

/* Create temp tables */
CREATE TABLE #PageResults
(ParentObject VARCHAR(100)
,[Object] VARCHAR(1000)
,[Field] VARCHAR(100)
,[VALUE] VARCHAR(1000))

/* Declare local variables */
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;
DECLARE @sqlstr NVARCHAR(4000);
DECLARE @pagenum BIGINT;

/* Select information about the corrupt data
   This example selects rows with a RepairLevel of repair_allow_data_loss for a single object */
SELECT DISTINCT O.name, T.RepairLevel, T.IndId, T.PartID, T.Page 
INTO #DBCCPages 
FROM DBCCResults T
INNER JOIN Your_Database.sys.objects O
ON t.ObjId = O.object_id
WHERE RepairLevel = 'repair_allow_data_loss'
AND O.name = 'Your_Table'
ORDER BY O.name, T.Page;

/* Set the loop limit */
SET @looplimit = @@ROWCOUNT;

/* Build a DBCC PAGE statement for each corrupt page and execute it
   Insert the results into the #PageResults temp table */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT TOP 1 @pagenum = Page FROM #DBCCPages

	SET @sqlstr = 'DBCC PAGE (Your_Database,1,' + CAST(@pagenum AS NVARCHAR) + ',3) WITH TABLERESULTS'

	INSERT INTO PageResults
	EXEC sp_executesql @sqlstr;

	SET @loopcount += 1;

	DELETE FROM #DBCCPages WHERE Page = @pagenum;
	
END


Because I needed to provide the client with a list of rows affected by the corruption, I had to dump all pages identified as repair_allow_data_loss. The script below queries DBCC Results to return a list of PageIDs for the irreparable pages. It runs DBCC PAGE…WITH TABLERESULTS for each PageID and writes the results to the PageResults table. Here is a sample of the output:

DBCCPAGEWithTableResults

You can see that the results contain a set of rows for each slot, or table row, contained on the page. I needed to take each set of those rows and pivot them into a single row. The script below does that. You will need to add a MIN(CASE statement for each row you want to pivot.

USE Your_Database;
/* Select data from PageResults to return the key value for each row contained on a corrupt page */

SELECT ParentObject,
     MIN(CASE Field WHEN 'col1' THEN VALUE END) AS col1,
     MIN(CASE Field WHEN 'col2' THEN VALUE END) AS col2,
     MIN(CASE Field WHEN 'col3' THEN VALUE END) AS col3,
     MIN(CASE Field WHEN 'col4' THEN VALUE END) AS col4,
     MIN(CASE Field WHEN 'col5' THEN VALUE END) AS col5
INTO Your_Database..PageResults
   FROM #PageResults
   GROUP BY ParentObject
   ORDER BY col1, col2, col3, col4, col5; 

I hope this post provides more insight into the tools and methods available for analyzing corruption and determining its extent.

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 &lt;&gt; @restoredbname) OR (@sourcedbname = @restoredbname AND @tvplimit &gt; 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 &lt;= @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 &gt; 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 &lt;&gt; @droprestoredb. replace file extensions
IF @sourcedbname &lt;&gt; @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 &gt; 1
BEGIN

    --SELECT @datafileloopcount, @datafilecount
    WHILE @datafileloopcount &lt;= @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 &gt; @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 &gt; @logbackupdate
	order by a.backup_finish_date ASC

	IF @stopat &lt; @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 &gt; @stopat

    DELETE FROM #backupfiles
    WHERE backupfinishdate &gt; @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 &lt;= @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 &gt; 1
        BEGIN

            WHILE @backuploopcount &lt;= @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) &gt; 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 &lt;&gt; 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 &gt; 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

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

Stored Procedure to Automate Restores – Mark III

February 16, 2014 Leave a comment

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