SQL Restore Script

I have had to script restores using different backup programs and different options.  Here is my latest script that searches a network path for the latest backup using the maintenance plan naming format.  It can also be used to restore a single backup file.  This version is designed to only restore a single database.


SET NOCOUNT ON

DECLARE @MyDB NVARCHAR(MAX)
DECLARE @MyMDF NVARCHAR(MAX)
DECLARE @MyMDFFileName NVARCHAR(MAX)
DECLARE @MyLDF NVARCHAR(MAX)
DECLARE @MyLDFFileName NVARCHAR(MAX)
DECLARE @BackupDir AS NVARCHAR(255)
DECLARE @MyBackupFile NVARCHAR(MAX) = NULL
DECLARE @MySQL NVARCHAR(MAX)
DECLARE @LiteSpeed BIT
DECLARE @UpgradeCompatibility BIT
DECLARE @ReadOnly BIT
DECLARE @Debug BIT

SET @MyDB = N'<databasename>'
SET @LiteSpeed = 0 -- 1 = Use LiteSpeed Stored Procedure to Restore : 0 = Use Standard SQL Restore
SET @DEBUG = 1 -- 1 = Run in Debug Mode : 0 = Run without Debug
SET @UpgradeCompatibility 1 -- 1 = Upgrade to Server's Compatibility Level : 0 = Keep at restored version
SET @ReadOnly = 1 -- 1 = Set Database Read Only after Restore : 0 = Keep Database at restored setting

-- THE DIRECTORY WHERE THE BACKUPS ARE PLACED
SET @BackupDir = N'\serversharepath'
-- TO PROVIDE A SINGLE FILE TO RESTORE UNCOMMENT LINE BELOW AND MODIFY
-- SET @MyBackupFile = N'\serversharepathbackupfile.bak'

-- POPULATE MDF AND LDF VARIABLES
SELECT
@MyMDF = rtrim(mdf.NAME)
,@MyMDFFileName = rtrim(mdf.filename)
,@MyLDF = rtrim(ldf.NAME)
,@MyLDFFileName = rtrim(ldf.filename)
FROM master.sys.sysdatabases db
INNER JOIN master.sys.sysaltfiles mdf
ON mdf.dbid = db.dbid
AND db.filename = mdf.filename
INNER JOIN master.sys.sysaltfiles ldf
ON ldf.dbid = db.dbid
AND db.filename <> ldf.filename
WHERE db.NAME = @MyDB

IF ( @MyMDF IS NULL OR @MyLDF IS NULL OR @MyMDFFileName IS NULL OR @MyLDFFileName IS NULL )
BEGIN
RAISERROR ('Database Information Not Found', 16, - 1)
RETURN
END

IF @MyBackupFile IS NULL
BEGIN
DECLARE @LG_FILENAME_PRFX AS VARCHAR(200)
DECLARE @Path VARCHAR(1000)
DECLARE @FullPath VARCHAR(2000)
DECLARE @Id INT

-- Drop Temp Table
IF OBJECT_ID('tempdb..#DirectoryTable') IS NOT NULL
DROP TABLE #DirectoryTable;

-- Create Temp Table
CREATE TABLE #DirectoryTable (
ID INT IDENTITY
,FULLPATH VARCHAR(2000)
,SUBDIRECTORY SYSNAME
,DEPTH INTEGER
,isfile BIT
,SORTNAME VARCHAR(200)
,MDFNAME NVARCHAR(256)
,MDFFILENAME NVARCHAR(520)
,LDFNAME NVARCHAR(256)
,LDFFILENAME NVARCHAR(520)
,BackupDate DATETIME
,BackupRank INTEGER
)

--Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTable ADD PRIMARY KEY CLUSTERED (id);

--Populate the table using the initial base path.
INSERT #DirectoryTable (subdirectory, depth, isfile)
EXEC master.sys.xp_dirtree @BACKUPDIR, 1, 1;

UPDATE #DirectoryTable SET fullpath = @BACKUPDIR;

--Loop through the table as long as there are still folders to process.
WHILE EXISTS ( SELECT id FROM #DirectoryTable WHERE isfile = 0 )
BEGIN
--Select the first row that is a folder.
SELECT TOP (1) @Id = id
,@FullPath = fullpath
,@Path = @BACKUPDIR + '' + subdirectory
FROM #DirectoryTable
WHERE isfile = 0;

IF @FullPath = @Path
BEGIN
--Do this section if the we are still in the same folder.
INSERT #DirectoryTable ( subdirectory, depth, isfile )
EXEC master.sys.xp_dirtree @Path, 1, 1;

UPDATE #DirectoryTable SET fullpath = @Path WHERE fullpath IS NULL;

--Delete the processed folder.
DELETE FROM #DirectoryTable WHERE id = @Id;
END
ELSE
BEGIN
--Do this section if we need to jump down into another subfolder.
SET @BACKUPDIR = @FullPath;

--Select the first row that is a folder.
SELECT TOP (1) @Id = id
,@FullPath = fullpath
,@Path = @BACKUPDIR + '' + subdirectory
FROM #DirectoryTable
WHERE isfile = 0;

INSERT #DirectoryTable ( subdirectory, depth, isfile )
EXEC master.sys.xp_dirtree @Path, 1, 1;

UPDATE #DirectoryTable SET fullpath = @Path WHERE fullpath IS NULL;

--Delete the processed folder.
DELETE FROM #DirectoryTable WHERE id = @Id;
END
END

-- Remove Transaction Backups
DELETE FROM #DirectoryTable WHERE RIGHT(SubDirectory, 4) = '.TRN'

-- Get the Database Name from the File Name
UPDATE #DirectoryTable SET SORTNAME = REPLACE(SUBDIRECTORY, RIGHT(SUBDIRECTORY, 20), '') WHERE SUBDIRECTORY LIKE '%.BAK' and SUBDIRECTORY NOT LIKE '%_SD%'

-- Add Date from Backup File
UPDATE #DirectoryTable
SET [BackupDate] = CASE
WHEN isDate(stuff(stuff(left(right(subdirectory, 16), 12), 9, 0, ' '), 12, 0, ':')) = 1
THEN convert(DATETIME, stuff(stuff(left(right(subdirectory, 16), 12), 9, 0, ' '), 12, 0, ':'))
ELSE NULL
END

-- Add Ranking to retrieve only first backup
UPDATE #DirectoryTable
SET BackupRank = (
SELECT BackupRank
FROM (
SELECT ID ,RANK() OVER ( PARTITION BY SortName ORDER BY BackupDate DESC ) AS BackupRank
FROM #DirectoryTable T
) D
WHERE D.ID = #DirectoryTable.ID
)

SELECT @MyBackupFile = FullPath + '' + SubDirectory FROM #DirectoryTable WHERE BackupRank = 1 and SORTNAME like '%' + @MyDB + '%'

IF @@RowCount = 0
BEGIN
RAISERROR ('No Backup Files Found', 16, - 1)
RETURN
END
END

PRINT 'Restoring ' + @MyDB

--GENERATING SQL STATMENTS TO SET DATABASE TO SINGLE_USER
SELECT @MySQL = 'ALTER DATABASE [' + @MyDB + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + @MyDB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'+CHAR(13)+CHAR(10)

--GENERATING SQL STATMENTS TO RESTORE DATABASE
IF ( @LiteSpeed = 1 )
SELECT @MySQL = @MySQL + 'EXEC MASTER.DBO.XP_RESTORE_DATABASE @database = ''' + @MyDB + ''', @filename = ''' + @MyBackupFile + ''', @filenumber = 1, @with = ''RECOVERY'', @with = ''NOUNLOAD'', @with = ''STATS = 10'', @with = ''REPLACE'', @with = ''MOVE N''''' + @MyMDF + ''''' TO N''''' + @MyMDFFileName + ''''''', @with = ''MOVE N''''' + @MyLDF + ''''' TO N''''' + @MyLDFFileName + ''''''''
ELSE
SELECT @MySQL = @MySQL + 'RESTORE DATABASE [' + @MyDB + '] FROM DISK = N''' + @MyBackupFile + ''' WITH RECOVERY, NOUNLOAD, STATS = 10, REPLACE, MOVE N''' + @MyMDF + ''' TO N''' + @MyMDFFileName + ''', MOVE N''' + @MyLDF + ''' TO N''' + @MyLDFFileName + ''''

--GENERATING SQL STATMENTS TO SET DATABASE TO MULTI_USER
SELECT @MySQL = @MySQL + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + @MyDB + '] SET MULTI_USER'
--GENERATING SQL STATMENTS TO UPGRADE COMPATIBILITY
IF @UpgradeCompatibility = 1
BEGIN
DECLARE @Compatibility NVARCHAR(3)
SELECT @Compatibility = CASE WHEN @@version LIKE '%2005%' THEN '90' WHEN @@version LIKE '%2008%' THEN '100' WHEN @@version LIKE '%2012%' THEN '110' WHEN @@version LIKE '%2014%' THEN '120' WHEN @@version LIKE '%2016%' THEN '130' ELSE NULL END
SELECT @MySQL = @MySQL + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + @MyDB + '] SET COMPATIBILITY_LEVEL = ' + @Compatibility
END
--GENERATING SQL STATMENTS TO SET DATABASE TO READ_ONLY
IF @ReadOnly = 1
SELECT @MySQL = @MySQL + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + @MyDB + '] SET READ_ONLY'

PRINT @MySQL

IF @DEBUG = 0
BEGIN
--RUN SQL STATEMENTS GENERATED ABOVE
EXEC (@MySQL)
END

SET NOCOUNT OFF
GO

Posted in T-SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow SQL Uber Geek on WordPress.com
Try Audible and Get Two Free Audiobooks
%d bloggers like this: