Disaster Recovery
For a while we had a disaster recovery site and once a quarter we would bring our backups and restore the databases and we would see how far we could get bringing the business backup in one day. The site just had Servers, I got them after the OS was loaded, so I had to load SQL Server, and start restoring. We did not try to get everything up and running the first try, we set a goal of the databases and the main website and try to processes some business. Then in later visits we added to what we knew we could do, adding Phones (Voice over IP), Domains etc. The first time I just restored from a backup, the next and subsequent visits, I did a point in time restore using log restores. Oh and we have a ton of SQL logins that needed to be restored as well. So once a quarter for over two years we did this, after the second time of figuring out the restore script and restoring the logins etc I thought that there has to be a better way. This is what I came up with.
The first thing I did was create a directory in the backup path so I knew it was being backed up and would be available during our restore. I put the following scripts in this directory as well as other things that were needed:
· SQLDTSFTPTask.msi which is a FTP Task for DTS packages
· SQLServer2005_DTS.msi – since we are on 2005 and running DTS package we need the DTS add on
· Current SQL Service Pack that servers are on.
· Notes.txt – General instructions, things that were encountered
· CreateDatabaselist.sql – creates a list of databases and their logic file names. Most likely the database files will have to move
· Databaselist.txt – List of databases and their logical file names. We only have a primary file group, so if you have more than one you have to try something different.
· CreateRestoreScripts.sql – this reads the databaselist.txt and given the path of the backups returns the restore scripts by reading the header information of the backups.
We always did a postmortem after wards and as things came up that would be help I added to the Notes.txt file. For example initially I did not have the SQL logins, but I did have a back up of master, so I put instructions on restoring master in the notes.TXT. You have to be in single user mode to restore master and I’m pretty sure, like msdb, the server that you are restoring to has to be at the same service pack that the backup is from. I was not too keen of restoring the master, but I was in a crunch.
/*
"CreateDatabaselist.sql"
Put the output from this into the file Databaselist.txt
*/
SET nocount ON
SELECT d.NAME + ',' + MAX(CASE WHEN m.name NOT LIKE '%log' THEN m.name
ELSE ''
END) + ','
+ MAX(CASE WHEN m.name LIKE '%log' THEN m.name
ELSE ''
END)
FROM sys.Master_files m
JOIN sys.databases d
ON m.database_id = d.database_id
WHERE d.name NOT IN ( 'tempdb', 'model', 'distribution' )
GROUP BY d.name
Here is some sample output:
database1,database1_Data,database1_Log
database2,database2_Data,database2_Log
database3,Special_Data,Special_Log
For the above script I put it in a job to create the Databaselist.txt, the same one I run sp_help_revlogin.
The Script that does the heavy lifting is CreateRestoreScripts.sql below. It has been a while since I ran this and one thing we did for our recovery test was put the backups onto USB drivers so it was easier to restore. And yes we did test the restores from tape, but we knew that worked and we had limited time. I have to say that this script evolved. I think the first time I did a point in time I scripted the restores of the .TRN files sequentially, knowing full well that the backup was done in the middle some were. So when I ran the script and tried to load the earlier log files it spit out errors until it hit the right LSN number and from there on it loaded all subsequent files. LSN is the Log sequence number and what is looked at for the sequences of files to restores. Comments are sparse in the script, sorry, so here are some notes:
· Assumes Backup path is formatted DatabaseName\backups
· Full backups are .BAK, Differentials are .DIF, and logs are .TRN
· The #Header table I took from a script online way back when and did not record the author, my bad, thanks to that unknown SQL person
· This uses DOS commands to get the list of files, here are some of the commands used
o Dir /B – gives only file names
o For a% in (PATH) do Echo … - this loops through all files in PATH and uses %a as variable, %~ta is the timestamp of that file
· This does the INSERT INTO “table” Exec proc – I try to avoid this because of performance issues I’ve run into, but this is such a limited use it’s OK.
· When restoring databases to a point in time they must restored WITH STANDBY, which this script does the last thing it does is restore the database with recovery so it’s ready for use.
SET NOCOUNT ON
-- set locations
-- backups location
DECLARE @BackUpPath VARCHAR(250)
SET @BackUpPath = 'E:\SQLSERVER\BACKUPs\'
DECLARE @NewSQLPath VARCHAR(250)
SET @NewSQLPath = 'D:\SQLSERVER\Data\'
/* tables used */
-- table of actual db data
DROP TABLE #DBINFO
CREATE TABLE #DBINFO
(
DBNAME SYSNAME,
DataFile VARCHAR(250),
PathDataFile VARCHAR(250),
LogFile VARCHAR(250),
PathLogFile VARCHAR(250)
)
BULK INSERT #DBINFO FROM 'E:\SQLSERVER\BACKUPs\DR Scripts\Databaselist.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
-- create table to insert from cmdshell
DROP TABLE #files
CREATE TABLE #files ( FILNAME VARCHAR(1000) )
-- this is used to get the list of backup files from the backup system
DROP TABLE #FileList
CREATE TABLE #FileList
(
FileDate DATETIME,
FILNAME VARCHAR(250)
)
-- Restore header only Result set
DROP TABLE #Header
CREATE TABLE #Header
(
BackupName NVARCHAR(128),
BackupDescription NVARCHAR(255),
BackupType SMALLINT,
ExpirationDate DATETIME,
Compressed TINYINT,
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) NULL,
RecoveryModel NVARCHAR(60),
DifferentialBaseLSN NUMERIC(25, 0) NULL,
DifferentialBaseGUID UNIQUEIDENTIFIER,
BackupTypeDescription NVARCHAR(60),
BackupSetGUID UNIQUEIDENTIFIER
)
DECLARE @sql VARCHAR(3000)
-- pulls double duty as a SQL command string and a DOS command string
-- loop through the databases and load them
DECLARE @dbname VARCHAR(250),
@DbbackupFilename VARCHAR(250),
@lsn VARCHAR(50),
@DataFileName VARCHAR(250),
@DataFilePath VARCHAR(250),
@LogFileName VARCHAR(250),
@logFilePath VARCHAR(250),
@filedate DATETIME
DELETE FROM #DBINFO
WHERE dbname <> @DBNAME
SET @dbname = ( SELECT MIN(dbname)
FROM #DBINFO
)
WHILE EXISTS ( SELECT TOP 1
*
FROM #DBINFO )
BEGIN
PRINT '/****** ' + @dbname
+ ' ************/'
SELECT @DataFileName = DataFile,
@DataFilePath = PathDataFile,
@LogFileName = LogFile,
@logFilePath = PathLogFile
FROM #DBINFO
WHERE dbname = @dbname
-- get the list of backup files
SET @sql = 'dir ' + @BackUpPath + @dbname + ' /B'
INSERT INTO #files
EXEC master..xp_cmdshell @sql
-- CLEAN UP NULL VALUE
DELETE FROM #files
WHERE filname IS NULL
-- find the lastest .BAK file
SET @DbbackupFilename = ( SELECT TOP 1
FILNAME
FROM #files
WHERE filname LIKE '%.bak'
ORDER BY FILNAME DESC
)
-- get last Lsn number from backup
SET @sql = 'restore headeronly from disk=''' + @BackUpPath + @dbname
+ '\' + @DbbackupFilename + ''''
DELETE FROM #Header
INSERT INTO #Header
EXEC ( @sql
)
SET @lsn = ( SELECT MAX(LastLSN)
FROM #header
)
SET @filedate = ( SELECT BackupFinishDate
FROM #header
)
SET @SQL = 'RESTORE DATABASE ' + @DBname + ' FROM DISK = '''
+ @BackUpPath + @dbname + '\' + @DbbackupFilename + '''
WITH MOVE ''' + @DataFileName + ''' TO ''' + @NewSQLPath + @DataFileName
+ '.mdf'',
MOVE ''' + @LogFileName + ''' TO ''' + @NewSQLPath + @LogFileName
+ '.ldf'',
REPLACE, STANDBY = ''' + @BackUpPath + @DBname + '\' + @DBname
+ '_Undo.dat'''
PRINT @SQL
-- remove ALL FULL BACKUPS FROM LIST
DELETE FROM #files
WHERE filname LIKE '%.bak'
TRUNCATE TABLE #files
SET @sql = 'for %a in (' + @BackUpPath + @dbname
+ '\*.dif) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'
INSERT INTO #files
EXEC master..xp_cmdshell @sql
DELETE FROM #files
WHERE ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'
WHILE EXISTS ( SELECT *
FROM #files )
BEGIN
SET @sql = ( SELECT MIN(FILNAME)
FROM #files
)
PRINT @sql -- just to verify it looks at all the .dif files
EXEC ( @sql
)
DELETE FROM #files
WHERE @sql = FILNAME
END
DELETE FROM #FileList
WHERE filedate < @filedate
-- Load differentials
WHILE EXISTS ( SELECT TOP 1
*
FROM #FileList
WHERE filname LIKE '%.dif' )
BEGIN
-- get next file name
SET @DbbackupFilename = ( SELECT TOP 1
FILNAME
FROM #FileList
WHERE filname LIKE '%.dif'
ORDER BY FILNAME ASC
)
-- get last Lsn number from backup
SET @sql = 'restore headeronly from disk='''
+ @DbbackupFilename + ''''
-- clear out header table and repopulate
DELETE FROM #Header
INSERT INTO #Header
EXEC ( @sql
)
SET @filedate = ( SELECT BackupFinishDate
FROM #header
)
-- if LSN is greater than last back up then load
IF ( SELECT LastLSN
FROM #header
) >= @LSN
BEGIN
SET @sql = 'RESTORE DATABASE ' + @DBName
+ ' FROM DISK=''' + @DbbackupFilename
+ ''' WITH STANDBY=''' + @BackUpPath + @DBname
+ '\' + @DBname + '_Undo.dat'''
PRINT @SQL
SET @lsn = ( SELECT LastLSN
FROM #header
)
END
DELETE FROM #FileList
WHERE filname = @DbbackupFilename
END
-- load logs
TRUNCATE TABLE #files
TRUNCATE TABLE #FileList
SET @sql = 'for %a in (' + @BackUpPath + @dbname
+ '\*.trn) do @echo INSERT INTO #FileList Values(''%~ta'',''%a'')'
INSERT INTO #files
EXEC master..xp_cmdshell @sql
DELETE FROM #files
WHERE ISNULL(FILNAME, 'XX') NOT LIKE 'INSERT%'
WHILE EXISTS ( SELECT *
FROM #files )
BEGIN
SET @sql = ( SELECT MIN(FILNAME)
FROM #files
)
PRINT @sql --just to verify that it looks at all .trn files
EXEC ( @sql
)
DELETE FROM #files
WHERE @sql = FILNAME
END
-- remove old files
DELETE FROM #FileList
WHERE filedate < @filedate
WHILE EXISTS ( SELECT TOP 1
*
FROM #FileList
WHERE filname LIKE '%.trn' )
BEGIN
-- get next file name
SET @DbbackupFilename = ( SELECT TOP 1
FILNAME
FROM #FileList
WHERE filname LIKE '%.trn'
ORDER BY FILNAME ASC
)
-- get last Lsn number from backup
SET @sql = 'restore headeronly from disk='''
+ @DbbackupFilename + ''''
-- clear out header table and repopulate
DELETE FROM #Header
INSERT INTO #Header
EXEC ( @sql
)
-- if LSN is greater than last back up then load
IF ( SELECT LastLSN
FROM #header
) >= @LSN
BEGIN
SET @sql = 'RESTORE LOG ' + @DBName + ' FROM DISK='''
+ @DbbackupFilename + ''' WITH STANDBY='''
+ @BackUpPath + @DBname + '\' + @DBname
+ '_Undo.dat'''
PRINT @SQL
SET @lsn = ( SELECT LastLSN
FROM #header
)
END
DELETE FROM #FileList
WHERE filname = @DbbackupFilename
END
SET @sql = 'Restore Database ' + @DBName + ' with recovery'
PRINT @SQL
DELETE FROM #DBINFO
WHERE dbname = @dbname
SET @dbname = ( SELECT MIN(dbname)
FROM #DBINFO
)
END
No comments:
Post a Comment