{"id":658,"date":"2009-08-28T00:00:00","date_gmt":"2009-08-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/disaster-recovery-for-sql-server-databases\/"},"modified":"2021-08-24T13:40:36","modified_gmt":"2021-08-24T13:40:36","slug":"disaster-recovery-for-sql-server-databases","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/disaster-recovery-for-sql-server-databases\/","title":{"rendered":"Disaster Recovery for SQL Server Databases"},"content":{"rendered":"<\/p>\n
\n

In this article, I’ll lay out the technical details of implementing a simple Disaster Recovery Plan (DRP) for production applications running Microsoft SQL Server. My goal is to provide you with generic documentation to use as the basis of your own production system failover strategy. You will, of course, need to alter it with your own details and keep it updated any time that changes are made to your production systems, but this should give you a good departure point from which to build your own strategy.<\/p>\n

I’ll describe the steps to follow in the event of the failure of a database production system, and annotate the process as I go along. This is largely based on a Disaster Recovery Plan I had to design recently (all the better for you to download and personalize), so it is deliberately written in the style of a business strategy document. I’ll also explain the advantages of automatic restoration of compressed backup files from a failover server. I’ll also be the first to admit that this topic might seem a little dry, but having a DRS will make it worth the read – I promise.<\/p>\n

Part 1 of this article will describe the basic steps necessary to set up a ‘hot’ standby server (the recovery method I used when drafting this DRP), and Part 2 is an annotated transcript of Disaster Recovery document, including steps to be taken in the event of a disaster, and information for the unfortunate DBA tasked with recovering from it. Here we go:<\/p>\n

\n

PART 1 – Automatic Restoration of backup files to a failover server <\/h2>\n

SQL Servers’ norecovery<\/strong> mode keeps the database stable and ready to accept the changes you’re progressively applying as part of the backup process. This means that it’s only necessary to apply the latest differential or log backup before the database is ready to be accessed by users and applications.<\/p>\n

The disaster recovery method used is to have a ‘hot’ standby server (SQL2<\/em>), which is already installed, stable and, most importantly, is an exact<\/em> copy of the production server’s configuration. The standby server should already have the most recent operational databases fully-restored in norecovery<\/strong> mode.<\/p>\n

Implementing a Hot Standby Server<\/h3>\n

After SQL Server has been installed on the failover server, you need to check that Robocopy<\/b> is installed in the sysroot\\windows\\system32<\/em> folder. Secondly, Red Gate’s SQL Backup<\/a><\/strong> software must connect to the server and be configured by clicking the small grey square next to server listing in left pane – this is for instance auto-configuration, if it has not been done already.<\/p>\n

\"791-SQB_installingservercomponents.gif\"<\/p>\n

Figure 1 – SQL Backup’s auto-configuration system.<\/em><\/p>\n

Robocopy<\/b> is much better than ( the soon-to-be-discontinued) Xcopy<\/b>, by the way. And since Windows Server 2003, Robocopy<\/b> has been the recommended \/ future-proofed tool of choice. As far as I know, Xcopy<\/b> will no longer be available in future versions of Windows Server. <\/p>\n

Next, for the stored procedures that execute Robocopy<\/b> (we place these procedures in a local database on each server called DBA_tools), you need to allow the advanced option xp_cmdshell <\/code>to run:<\/p>\n

-- To allow advanced options to be changed. \r\n\r\nEXEC sp_configure 'show advanced options', 1 \r\nGO \r\n-- To update the currently configured value \r\n-- for advanced options.\r\n            \r\nRECONFIGURE \r\n\r\nGO\r\n\r\n-- To enable the feature.\r\n\r\nEXEC sp_configure 'xp_cmdshell', 1 \r\nGO \r\n\r\n-- To update the currently configured value for this feature.\r\n            \r\nRECONFIGURE \r\n\r\nGO \r\n\r\n\r\n<\/pre>\n
In order to copy the backup files, each database on the standby server needs a database-specific SQL Server Agent job running Robocopy<\/b> at the required interval to copy full and differential backups from the production server to the standby server. These jobs can be run at whatever frequency needed, be it daily, hourly or even more often if your operations require it.<\/p>\n
Robocopy<\/b> is the first step in all automated restore jobs, unless you want to add validation steps prior to the backup file copy. The following example copies all differential database backups from a production server to a DRP server:<\/p>\n
EXEC dbo.usp_RoboCopy '\\\\PRODserver\\drive$\\ProdServerBackupShare\\Diff',  '\\\\DRPserver\\Drive$\\ProdServerDbBackupFolder\\Diff', 'database1_* database2_*' \r\n\r\n-- This case just handles the differential folder, so we're assuming you'll also have \r\n-- a Tlog and Full folder.  \r\n<\/pre>\n
A database-specific SQL Server Job will restore these backups daily to the hot standby server (DRP) using stored procedures specifically created for this setup, such as:<\/p>\n
    \n
  • usp_DB_Restore_Master or usp_DB_Restore_Master_Multi<\/code>  <\/li>\n
  • usp_DB_Restore<\/code>  <\/li>\n
  • usp_DB_Restore_NoRecovery<\/code>  <\/li>\n
  • usp_DB_Restore_differential<\/code>  <\/li>\n
  • usp_DB_Restore_Log <\/code><\/li>\n<\/ul>\n
    A consideration for the DBA regarding the level of database recovery <\/h3>\n
    If you are currently in Simple Recovery mode, and provided there are regular Transaction Log and differential backups (as in, several times a day), you can switch your recovery model over to Bulk-Logged in production to restore up to a specific point in time. This will naturally minimize the amount of data lost from the work session prior to any downtime.<\/p>\n
    Full Recovery mode is recommended for critical databases that require auditing compliance.<\/p>\n
    In the event of failure, the most recent log or differential backup is ready to be applied to the standby database sitting in norecovery<\/b> mode, and you’re up and running quickly with minimal down-time.<\/p>\n
    An alternative method for a much smaller database, where the total restore time is below five minutes, is to apply the complete restore every hour to the failover server, in which case you don’t need to worry about norecovery<\/b> mode.<\/p>\n
    PART 2 – Instructions to follow in the event of a disaster to the production system<\/h2>\n
      \n
    1. If you haven’t heard from them directly already, please contact FIRST LINE DBA SUPPORT<\/strong> at [INSERT NUMBER<\/em>] or SECONDARY DBA<\/strong> at [INSERT NUMBER<\/em>]  <\/li>\n
    2. After the production\/original data publisher server failure (SQL1), the restore \/ backup-subscriber server (SQL2) will be used as the primary database server (a.k.a. DRP server). Inform everyone in the department by E-mail (It’s also worth thinking about who will inform internal\/external clients)<\/span>.  <\/li>\n
    3. Once the switch occurs to the DRP server and the downtime of SQL1 actually happens, all application connection strings need to be changed to access SQL2. The CGI should handle this step automatically.  <\/li>\n
    4. Disable Automatic Restore SQL Agents on SQL2.  <\/li>\n
    5. Disable all SQL Agent jobs on failed server SQL1 if possible.  <\/li>\n
    6. Enable all maintenance and backup jobs on newly active server SQL2 <\/li>\n<\/ol>\n
      Please note that restoring a log backup is not possible if the production database recovery model is set to Simple. For fine-grained restoration, the database needs to have been using the Full recovery model – Thankfully, the default setting is<\/strong> the Full Recovery model<\/span>. If point in time recoveries are requested by management on a regular basis, then we can also change the database recovery level to Bulk-Logged, if space is an issue, and Full otherwise – Perhaps with deserved hesitation from the side of the Database Administrators, as Bulk-logged recovery is much<\/em> more space efficient.<\/span><\/p>\n
      How the automation of the restore from compressed backup is benefitial to your production environment. <\/em><\/strong>Ideally you should keep two full backups, one on the Test server and one on the DRP server. Having this second copy of the production databases will allow you to do some useful and intensive work which you don’t want to have to run on live databases, such as a full DBCC CheckDB – console commands that can check the integrity of your exact database restore copy.<\/p>\n
      A log of what has been restored shall be placed in the following directory:<\/p>\n
      \\\\DatabaseServerName\\drive$\\prodBackupDir\\DBlog\\<\/em><\/strong><\/strong><\/p>\n
      As soon as a restore is completed, we should have an automatic purge of old backups – done perhaps every week (maximum 14 days manually, or automatically in a SQL Server Maintenance Plan<\/span>), and which can be automated using a batch file or PowerShell Script.<\/p>\n
      To ensure a smooth restore process, we should read the restore parameters directly from the backup log system tables – such as BackupHistory, BackupSet, BackupFile or<\/p>\n
      Backuplog – unless a backuplog table is explicitly created in a local database or exists in msdb. This is to ensure that the essential restore parameters (such as the backup file name and position) are immediately available.<\/p>\n
      As I often set them, the SQL Agent Restore jobs have their parameters manually set during testing and are usually left that way – but of course it’s best to pull the meta-data directly from the system in case you move files around and forget to update the restore scripts.<\/p>\n
      SQL1 & SQL2 (Prod. & DRP) Server Hardware Configuration<\/h3>\n
      This is the configuration for the servers this document was originally written for (I don’t remember the System Models for that setup, but that’s not to say you shouldn’t record yours) Update the following with your own server properties. <\/p>\n
      SQL1 (production instance)<\/h4>\n\n\n\n\n\n\n\n\n
      \n
      1.1<\/p>\n<\/td>\n
      		\n
      Server Type<\/p>\n<\/td>\n
      		\n
      Windows 2008 (standard x64 edition)<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.2<\/p>\n<\/td>\n
      		\n
      System Model<\/p>\n<\/td>\n
      		\n
      [Server Model Number, Product Type]<\/em><\/p>\n<\/td>\n<\/tr>\n
      \n
      1.3<\/p>\n<\/td>\n
      		\n
      RAM Memory<\/p>\n<\/td>\n
      		\n
      8 Gig<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.4<\/p>\n<\/td>\n
      		\n
      No. of CPU’s<\/p>\n<\/td>\n
      		\n
      2<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.5<\/p>\n<\/td>\n
      		\n
      CPU & Speed<\/p>\n<\/td>\n
      		\n
      AMD (x64)<\/p>\n<\/td>\n<\/tr>\n
      \n
      Drives<\/p>\n<\/td>\n
      		\n
      Hard Disk Space<\/p>\n<\/td>\n
      		\n
      C(#G);D(#G)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      \n
      <\/div>\n<\/div>\n
      SQL2 (storage replication partner \/ hot standby restore-subscriber) <\/h4>\n\n\n\n\n\n\n\n\n
      \n
      1.1<\/p>\n<\/td>\n
      		\n
      Server Type<\/p>\n<\/td>\n
      		\n
      Windows 2008 ( standard x64 edition )<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.2<\/p>\n<\/td>\n
      		\n
      System Model<\/p>\n<\/td>\n
      		\n
      [Server Model Number, Product Type] – Same as SQL1<\/em><\/p>\n<\/td>\n<\/tr>\n
      \n
      1.3<\/p>\n<\/td>\n
      		\n
      RAM Memory<\/p>\n<\/td>\n
      		\n
      9 Gig<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.4<\/p>\n<\/td>\n
      		\n
      No. of CPU’s<\/p>\n<\/td>\n
      		\n
      2<\/p>\n<\/td>\n<\/tr>\n
      \n
      1.5<\/p>\n<\/td>\n
      		\n
      CPU & Speed<\/p>\n<\/td>\n
      		\n
      AMD (x64) Opteron Processor 280<\/p>\n<\/td>\n<\/tr>\n
      \n
      Drives<\/p>\n<\/td>\n
      		\n
      Hard Disk Space<\/p>\n<\/td>\n
      		\n
      C(#G); D(#G); F(2TB); G(250GB); H (1.5TB); Z(20GB)<\/p>\n
      This server should have terabytes and terabytes of space, depending on your archiving needs.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      SQL Server Configuration<\/h3>\n
      For a previous client our production build of SQL Server was 9.0.3152, so naturally the DRP server had to be the exact same build – both systems must be as identical as possible.<\/p>\n
      Our servers are using 64-bit versions of the SQL Database Engine 2005\/8, with at least service pack 2 (2005), cu3 (2008) installed, and the collation type is Latin1_General_CI_AS (accent sensitive is recommended).   It is preferable to have at least Cumulative Rollup package 8 or SP3 for SQL Server 2005, and it’s important to do an update to production build levels of SQL on a regular basis.<\/p>\n
      Detailed information for the server and databases is included in the compiled help file located on both servers SQL1 and SQL2 <\/p>\n
      D:\\DRP\\ServerName.chm <\/strong>(i.e. make it very easy to find DRP info) <\/span><\/p>\n
      Critical SQL Server User Database Details <\/h3>\n
      1. List of databases<\/h4>\n
      Database1<\/strong><\/p>\n
      Database2<\/strong><\/p>\n
      …<\/strong><\/p>\n
      NB:  We will not be doing master, msdb, model or temp – these are backed up on a regular basis and will be copied by Robocopy<\/b> although not restored onto the database restore replication subscriber directly.<\/em><\/p>\n
       2. Database Maintenance Plan and Auto-Restore.<\/h4>\n
      In general, our database restore plan will reflect exactly the backup schedule and wait for backups to finish by querying the metadata from the production server. The restore jobs will check to see if the days’ full backup has completed (or daily diff.) using the backupset.backup_finish_date  column.  Once we see that Full backup has been completed on the production server, we copy the backupfile over to the hot standby server. In the second step of the job, we continue to execute the code from the appropriate usp_DB_restore combined with the metadata extraction from the system tables.<\/p>\n
      3. Database Backup schedule in production <\/strong><\/h4>\n\n\n\n\n\n\n
      \n
      Maintenance Job Name <\/strong><\/p>\n<\/td>\n
      		\n
      Maintenance Job Description <\/strong><\/p>\n<\/td>\n
      		\n
      Freq <\/strong><\/p>\n<\/td>\n
      		\n
      Time to Run <\/strong><\/p>\n<\/td>\n<\/tr>\n
      \n
      BackupFull_Database1<\/p>\n<\/td>\n
      		\n
      Full Database backup Database1<\/p>\n<\/td>\n
      		\n
      W<\/p>\n<\/td>\n
      		\n
      Sunday 6:00<\/p>\n<\/td>\n<\/tr>\n
      \n
      BackupFull_Database2<\/p>\n<\/td>\n
      		\n
      Full Database backup Database2<\/p>\n<\/td>\n
      		\n
      W<\/p>\n<\/td>\n
      		\n
      Sunday 6:30<\/p>\n<\/td>\n<\/tr>\n
      \n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      4. Restore jobs on DRP server<\/h4>\n\n\n\n\n\n\n
      \n
      Maintenance Job Name<\/strong><\/p>\n<\/td>\n
      		\n
      Maintenance Job Description <\/strong><\/p>\n<\/td>\n
      		\n
      Freq <\/strong><\/p>\n<\/td>\n
      		\n
      Time to Run <\/strong><\/p>\n<\/td>\n<\/tr>\n
      \n
      BackupFull_Database1<\/p>\n<\/td>\n
      		\n
      Full Database backup Database1<\/p>\n<\/td>\n
      		\n
      W<\/p>\n<\/td>\n
      		\n
      Sunday 6:00<\/p>\n<\/td>\n<\/tr>\n
      \n
      BackupFull_Database2<\/p>\n<\/td>\n
      		\n
      Full Database backup Database2<\/p>\n<\/td>\n
      		\n
      W<\/p>\n<\/td>\n
      		\n
      Sunday 6:30<\/p>\n<\/td>\n<\/tr>\n
      \n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n
      		\n
      …<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Critical Scripts, Procedures and Programs related to disaster recovery<\/h3>\n
      Following is a list of all the code used for the DRP process from SQL1 to SQL2:<\/p>\n
      usp_DB_Restore_Master<\/h4>\n
      CREATE PROC usp_DB_Restore_MasterRecovery \r\n\r\n-- Add the database name and input variables, instead of setting them on lines 40-23.\r\n      \r\nAS \r\nDECLARE \r\n@filename     VARCHAR(255) \r\n      ,\r\n@cmd              VARCHAR(500) \r\n      ,\r\n@cmd2             VARCHAR(500) \r\n      ,\r\n@dbNameSource sysname -- This is an input parameter, unless you are testing. \r\n      \r\n,@dbNameTarget sysname -- This is an input parameter, unless you are testing.\r\n      \r\n,@FullRestoreFolder NVARCHAR(MAX)-- This is an input parameter, unless you \r\n                                       -- are testing.\r\n      \r\n,@dbNameStatement NVARCHAR(MAX) \r\n      ,\r\n@dbNameStatementDiff NVARCHAR(MAX) \r\n      ,\r\n@LogicalName      VARCHAR(255) \r\n      ,\r\n@PhysicalName     VARCHAR(255) \r\n      ,\r\n@Type             VARCHAR(20)-- Useful if reading the backup headers, no?                                     -- As part of the validation perhaps.\r\n      \r\n,@FileGroupName    VARCHAR(255) \r\n      ,\r\n@Size             VARCHAR(20) \r\n      ,\r\n@MaxSize          VARCHAR(20) -- Check what I do above and use what's below                                      -- if relevant.\r\n      \r\n,@filelistStatmt1 VARCHAR(MAX) \r\n      ,\r\n@filelistStatmtDiff VARCHAR(MAX) \r\n\r\n\r\n\/* The following variables are set up for testing and may be taken off when sp is used afterwards (if we cannot get them reliably from sys databases automatically). *\/\r\n\r\n      \r\n,@backupFile sysname  -- will grab from local test .sqb files first. \r\n      \r\n,@logicalDataFile sysname\r\n\r\n\r\n\/* I am developing this code first assuming that we will only have one data file and logical file for each database. Later we'll add support for multiple logical and physical files (there may be, in Database1's case, more than one row for dbo.sysfiles\r\nwhere fileid=1 and groupid=1). *\/\r\n\r\n      \r\n,@logicalDataStmt1 NVARCHAR(MAX) \r\n      ,\r\n@logicalDataStmt2 NVARCHAR(MAX) \r\n      ,\r\n@logicalDataStmt3 NVARCHAR(MAX) \r\n      ,\r\n@logicalLogFile sysname  -- Returned and verified.\r\n      \r\n,@logicalLogStmt1 NVARCHAR(MAX) -- So many annoying variables. If I could just  \r\n      \r\n,@logicalLogStmt2 NVARCHAR(MAX) -- read the header it'd be easier in the future.\r\n      \r\n,@logicalLogStmt3 NVARCHAR(MAX)\r\n      ,\r\n@physicalDataFile sysname -- Easy to grab since it was in master. \r\n      \r\n,@physicalLogFile sysname -- Need two variables.\r\n      \r\n,@physicalLogFileStmt1 NVARCHAR(MAX) \r\n      ,\r\n@physicalLogFileStmt2 NVARCHAR(MAX) \r\n      ,\r\n@physicalLogFileStmt3 NVARCHAR(MAX) \r\n  \r\nSET NOCOUNT ON -- Following best practices, although we're not throwing around big \r\n                 -- counts anyway.\r\n\r\n-- Parameters and variables set by Hugo for testing.\r\n  \r\nSET @FullRestoreFolder='\\\\testServer\\Drive$\\ProdServerBackupFolder\\full\\' \r\n  \r\nSET @dbNameSource ='Database1' \r\n  \r\nSET @dbNameTarget ='Database1'  -- Sometimes we want to over-write another database \r\n                                  -- (e.g. in the case of importpdm_tst).\r\n  \r\nSET @physicalDatafile=( \r\n  \r\nSELECT filename  \r\n    \r\nFROM MASTER.dbo.sysdatabases  \r\n    \r\nWHERE NAME=@dbnameTarget) \r\n      \r\nPRINT 'The physical data FILE TO RESTORE IS '+@physicalDatafile \r\n  \r\nSET @logicalDataStmt1='select top 1 name from [' \r\n  \r\nSET @logicalDataStmt2='].dbo.sysfiles where fileid=1 and groupid=1' \r\n  \r\nSET @logicalDataStmt3 = (@logicalDataStmt1+@dbNameTarget+@logicalDataStmt2)\r\n\r\n\r\n\/* I wanted to do an execute at this line but I kept thinking that if it was within a single statement, my set statement @logicalDataFile would take the result of the query (select top 1 name from PROD_PASRAA.dbo.sysfiles where fileid)=1 and groupid=1. *\/\r\n\r\n\r\nCREATE TABLE #logicalDataFile  -- Drop table #logicaldatafile.\r\n  \r\n( \r\n        \r\nlogicalDataFile sysname \r\n  \r\n) \r\n\r\nINSERT INTO [#logicalDataFile] \r\n  \r\n( \r\n        \r\nlogicalDataFile \r\n  \r\n) \r\n  \r\nEXEC (@logicalDataStmt3) \r\n\r\n-- Now set the variable from the temp finally.\r\n  \r\nSET @logicalDataFile=( \r\n  \r\nSELECT *  \r\n    \r\nFROM #logicalDataFile) \r\n\r\n-- Check out a temp table method...need the result set from.\r\n      \r\nPRINT 'the logical data file is '+@logicalDataFile \r\n  \r\nSET @logicalLogStmt1='select top 1 name from [' \r\n  \r\nSET @logicalLogStmt2='].dbo.sysfiles where fileid=2 and groupid=0' \r\n\r\n-- Put the statement together.\r\n  \r\nSET @logicalLogStmt3 = (@logicalLogStmt1+@dbNameTarget+@logicalLogStmt2)  \r\n\r\nCREATE TABLE #logicalLogfile  -- Drop table #logicalLogfile.\r\n  \r\n( \r\n        \r\nlogicalLogFile sysname \r\n  \r\n) \r\n\r\nINSERT INTO [#logicalLogFile] \r\n  \r\n( \r\n        \r\nlogicallogFile \r\n  \r\n) \r\n  \r\nEXEC (@logicallogStmt3) \r\n\r\n-- Now set the variable from the temp finally.\r\n  \r\nSET @logicalLogFile=( \r\n  \r\nSELECT *  \r\n    \r\nFROM #logicalLogFile) \r\n      \r\nPRINT 'the logical log file is '+@logicalLogFile -- Has the right value   \r\n-- grab the last db file related variable from sysfiles.\r\n  \r\nSET @physicalLogFileStmt1='select filename from [' \r\n  \r\nSET @physicalLogFileStmt2='].dbo.sysfiles where fileid=2 and groupid=0' \r\n  \r\nSET @physicalLogFileStmt3 = (@physicalLogFileStmt1+@dbNameTarget+@physicalLogFileStmt2) \r\n\r\nCREATE TABLE #physicalLogFile  -- Drop table #physicalLogFilefile.\r\n  \r\n( \r\n        \r\nphysicalLogFile sysname \r\n  \r\n) \r\n\r\nINSERT INTO [#physicalLogFile] \r\n  \r\n( \r\n        \r\nphysicalLogFile \r\n  \r\n) \r\n  \r\nEXEC (@physicalLogFileStmt3) \r\n\r\n-- Now set the variable from the temp finally.\r\n  \r\nSET @physicalLogFile=( \r\n  \r\nSELECT *  \r\n    \r\nFROM #physicalLogFile) \r\n      \r\nPRINT 'the physical log file is '+@physicalLogFile\r\n\r\n\r\n\/* All verified up to here, and we're ready for some backup logic.Grab the\r\ncorresponding file for the restore folder, and according to following inputs\r\nfull restore file. *\/\r\n\r\n\r\nCREATE TABLE #dirList (filename NVARCHAR(MAX)) \r\n\r\nCREATE TABLE #filelist ( \r\n        \r\nLogicalName NVARCHAR(MAX) \r\n        ,\r\nPhysicalName NVARCHAR(MAX) \r\n        ,\r\n[Type] VARCHAR(20) \r\n        ,\r\nFileGroupName VARCHAR(50) \r\n        ,\r\nSize VARCHAR(20) \r\n        ,\r\nMaxSize VARCHAR(20) \r\n  ) \r\n\r\n-- Get the list of database backups that are in the restoreFromDir directory.\r\n\r\nIF @dbNameSource IS NULL -- If @OneDBName is null.\r\n-- For our purposes we are only using one database name for this.\r\n  \r\nSELECT @cmd = 'dir \/b \/on \"' +@FullRestoreFolder+ '\"' -- Select @cmd = 'dir \/b \/on '\r\n                                                        -- +@restoreFromDir+\r\n\r\nELSE \r\nSELECT \r\n@cmd = 'dir \/b \/o-d \/o-g \"' +@FullRestoreFolder+ '\"' \r\n\r\nINSERT #dirList  \r\n  \r\nEXEC master..xp_cmdshell @cmd \r\n\r\n-- Select filename from #dirlist whose list of files is good.\r\n\r\nSELECT @dbNameStatement= 'full%_'+@dbnameSource+'_200%_%.sqb'  - SQB IS FOR SQLBACKUP \r\n  \r\nSET @filelistStatmt1=( \r\n  \r\nSELECT TOP 1 filename  \r\n    \r\nFROM #dirList  \r\n    \r\nWHERE filename LIKE @dbNameStatement) \r\n      \r\nPRINT 'This is the full backup file to be restored '+@filelistStatmt1 \r\n  \r\nSET @backupfile=(@FullRestoreFolder)+(@filelistStatmt1) \r\n      \r\nPRINT 'this is the full path to the full restore file that will be restored '\r\n+\r\n@backupfile \r\n\r\nEXEC DBA_Tools.dbo.[usp_DB_Restore]  @backupfile, @dbnameTarget,  @logicalDataFile,\r\n\r\n@logicalLogFile, @physicalDataFile, @physicalLogFile\r\n<\/pre>\n
      usp_DB_Backup & usp_DB_Restore<\/h4>\n
      -- =============================================  \r\n-- Description: Restore Database  \r\n-- Parameter1: Restore File Name  \r\n-- Parameter2: Full path of file location i.e. 'DriveName:\\BackupShare\\'  \r\n-- Parameter3: RestoreType  \r\n-- FDN=full or differential no recovery, FDR = full or differential with recovery,  \r\n-- LN=log no recovery, LR=log with recovery  \r\n-- File Extensions: Full = *.bak , Differential= *.dif, T- Log= *.trn , *.sqb\r\n-- (SQLBackup)\r\n-- =============================================  \r\n\r\nCREATE PROCEDURE [dbo].[usp_DB_restore] \r\n  \r\n@RestoreFileName SYSNAME, \r\n  \r\n@LogicalNameData SYSNAME, \r\n  \r\n@LogicalNameLog SYSNAME, \r\n  \r\n@RestorePathData SYSNAME, \r\n  \r\n@RestorePathLog SYSNAME, \r\n  \r\n@ResoreType CHAR(1) \r\n      \r\nAS \r\n  BEGIN \r\n  SET \r\nNOCOUNT ON ; \r\n  \r\nDECLARE @SqlCmd NVARCHAR(2000) \r\n  \r\nDECLARE @DateTime SYSNAME \r\n  \r\nDECLARE @BakupFile NVARCHAR(1400), \r\n    \r\n@DiffFile NVARCHAR(1400), \r\n    \r\n@LogFile NVARCHAR(1400) \r\n  \r\nIF @ResoreType = 'FDN' \r\n    \r\nSET @SqlCmd = 'RESTORE DATABASE ' + QUOTENAME(@DBName) \r\n          + \r\n' TO DISK = ' + @Bakupfile + 'WITH INIT' \r\n    \r\nIF @ResoreType = 'FDR' \r\n    \r\nSET @SqlCmd = 'RESTORE DATABASE ' + QUOTENAME(@DBName) \r\n          + \r\n' TO DISK = ' + @Bakupfile + 'WITH INIT' \r\n    \r\nIF @ResoreType = 'LN' \r\n    \r\nSET @SqlCmd = 'RESTORE LOG ' + QUOTENAME(@DBName) \r\n          + \r\n' TO DISK = ' + @LogFile + \r\n    \r\nIF @ResoreType = 'LR' \r\n    \r\nSET @SqlCmd = 'RESTORE LOG ' + QUOTENAME(@DBName) \r\n          + \r\n' TO DISK = ' + @LogFile + \r\n          \r\nPRINT @SqlCmd \r\n    \r\nEXECUTE sp_executesql @SqlCmd \r\n  \r\nEND \r\n\r\n\r\n<\/pre>\n
      usp_DB_Restore_NoRecovery  <\/h4>\n
      Same as above, but for databases that need to be left in no recovery mode (e.g. waiting for a log backup to be applied or differential)<\/em><\/p>\n
      CREATE  PROC [dbo].[usp_DB_Restore_NoRecovery]  -- input variables when it all works below  \r\n  \r\n@backupfile SYSNAME, \r\n  \r\n@dbName SYSNAME, \r\n  \r\n@logicalDataFile SYSNAME, \r\n  \r\n@logicalLogFile SYSNAME, \r\n  \r\n@physicalDatafile SYSNAME, \r\n  \r\n@physicalLogFile SYSNAME \r\n      \r\nAS \r\nDECLARE \r\n@exitcode INT \r\nDECLARE \r\n@sqlerrorcode INT \r\nDECLARE \r\n@restoreStmt NVARCHAR(MAX) \r\n  \r\nSET NOCOUNT ON \r\n\r\n-- Kill any users in the database nicely? well, not really nicely.\r\n\r\nEXEC usp_KillConnections @dbName \r\n  \r\nSET @restoreStmt = N'-SQL RESTORE DATABASE ' + @dbName + '  \r\nFROM DISK = ' \r\n+ @backupfile + '  \r\nWITH NORECOVERY  \r\n,MOVE ' \r\n+ @logicalDataFile + ' TO ' + @physicalDatafile + '  \r\n,MOVE ' \r\n+ @logicalLogFile + ' TO ' + @physicalLogFile + '  \r\n,REPLACE  \r\n,LOGTO = \"\\\\ServerName\\Drive$\\SourceServerName\\DBlog<DATABASE>_<TYPE>_ <DATETIME yyyymmddhhmss>.txt\"' \r\n\r\n--PRINT @restoreStmt\r\n\r\nEXEC master..sqlbackup @restoreStmt, @exitcode OUT, @sqlerrorcode OUT \r\n\r\nIF ( @exitcode >= 500 ) \r\n      OR ( \r\n@sqlerrorcode <> 0 ) \r\n  \r\nBEGIN \r\n  RAISERROR \r\n( 'SQL Restore failed with exit code: %d SQL error code: %d', \r\n          \r\n16, 1, @exitcode, @sqlerrorcode ) \r\n  \r\nEND \r\n\r\n\r\n<\/pre>\n
      usp_DB_Restore_Differential<\/h4>\n
      -- restore directly from our copy which is automatically brought local using robocopy  \r\n-- EXEC [usp_DB_restore_Differential] \r\n-- '\\\\TestServer\\Drive$\\ProductionServer\\Diff\\\r\n-- DIFF_ServerName_DB_20080301_210001.sqb', \r\n-- 'DBname', 'LogicalDataFileName',  'LogicalLogFileName', \r\n-- 'Drive:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\Database1.mdf', \r\n-- 'Drive:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\\r\n-- Database1_log.ldf'  \r\n-- drop proc [usp_DB_restore_Differential]\r\n\r\n\r\nCREATE  PROC [dbo].[usp_DB_Restore_Differential] \r\n\r\n-- Input variables when it all works below.\r\n  \r\n@backupfile SYSNAME, \r\n  \r\n@dbName SYSNAME, \r\n  \r\n@logicalDataFile SYSNAME, \r\n  \r\n@logicalLogFile SYSNAME, \r\n  \r\n@physicalDatafile SYSNAME, \r\n  \r\n@physicalLogFile SYSNAME \r\n      \r\nAS \r\nDECLARE \r\n@exitcode INT \r\nDECLARE \r\n@sqlerrorcode INT \r\nDECLARE \r\n@restoreStmt NVARCHAR(MAX) \r\n  \r\nSET NOCOUNT ON \r\n\r\nEXEC usp_KillConnections @dbName \r\n\r\n-- WITH RECOVERY is used after a full restore is done already, and a final \r\n-- differential is applied to it (restore differential should be on a db in\r\n-- NORECOVERY MODE).\r\n  \r\nSET @restoreStmt = N'-SQL RESTORE DATABASE ' + @dbName + '  \r\nFROM DISK = ' \r\n+ @backupfile + '  \r\nWITH NORECOVERY  \r\n,MOVE ' \r\n+ @logicalDataFile + ' TO ' + @physicalDatafile + ' \r\n,MOVE ' \r\n+ @logicalLogFile + ' TO ' + @physicalLogFile + '  \r\n,REPLACE  \r\n,LOGTO = \"\\\\DRPServerName\\Drive$\\ProdServerBackupFolder\\DBlog\\<DATABASE>_<TYPE>_ <DATETIME yyyymmddhhmss>.txt\"' \r\n\r\n-- PRINT @restoreStmt  \r\n\r\nEXEC master..sqlbackup @restoreStmt, @exitcode OUT, @sqlerrorcode OUT \r\n\r\nIF ( @exitcode >= 500 ) \r\n      OR ( \r\n@sqlerrorcode <> 0 ) \r\n  \r\nBEGIN \r\n  RAISERROR \r\n( 'SQL Restore failed with exit code: %d SQL error code: %d', \r\n          \r\n16, 1, @exitcode, @sqlerrorcode ) \r\n  \r\nEND \r\n\r\n\r\n<\/pre>\n
      usp_DB_Restore_Log <\/h3>\n
      Should allow multiple logs to be automatically applied<\/em><\/p>\n
      SET ANSI_NULLS ON \r\n\r\nGO \r\n\r\nSET QUOTED_IDENTIFIER ON \r\n\r\nGO \r\n\r\n\r\n\/* EXEC [usp_DB_restore_log]  '\\\\ProdServer\\Drive$\\ProdServerDBbackups\\Full\\\r\nFULL_ServerName_DatabaseName1_20080217_030000.sqb', 'LogicalFileName', 'LogicalDataFile', 'LogicalLogFileName', \r\n'Drive:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\Database1.mdf', \r\n'Drive:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\Database1_log.ldf' \r\ndrop proc [usp_DB_restore_log] *\/\r\n\r\n\r\nCREATE  PROC [dbo].[usp_DB_restore_log] \r\n\r\n-- Input variables when it all works below.\r\n  \r\n@backupfile SYSNAME, \r\n  \r\n@dbName SYSNAME, \r\n  \r\n@logicalDataFile SYSNAME, \r\n  \r\n@logicalLogFile SYSNAME, \r\n  \r\n@physicalDatafile SYSNAME, \r\n  \r\n@physicalLogFile SYSNAME \r\n\r\n\r\n\/* System table backupfile on production server can give us LSN (log sequence number), logical_name, physical_drive and physical_name. If not, to grab the possible backup sets that are usable, see ms-help:\/\/MS.SQLCC.v9\/ MS.SQLSVR.v9.en\/tsqlref9\/html\/f1a7fc0a-f4b4-47eb-9138-eebf930dc9ac.htm. *\/\r\n      \r\nAS \r\nDECLARE \r\n@exitcode INT \r\nDECLARE \r\n@sqlerrorcode INT \r\nDECLARE \r\n@restoreStmt NVARCHAR(MAX) \r\n  \r\nSET NOCOUNT ON \r\n\r\n\r\n-- We will not need to kill connections, since the database is in restoring state \r\n-- already, waiting for a log.\r\n\r\n-- EXEC usp_KillConnections @dbName\r\n\r\n-- Transaction logs must be applied in sequential order. If there are multiple\r\n-- transaction logs to apply we have to leave the NORECOVERY option on. \r\n\r\n\r\n\/* In most cases, under the full or bulk- logged recovery models, SQL Server 2005 requires that you back up the tail of the log before restoring a database that is currently attached on the server instance. A tail-log backup captures the log that has not yet been backed up (the tail of the log) and is the last backup of interest in a recovery plan. Restoring a database without first backing up the tail of the log results is a mistake, unless the RESTORE statement contains either the WITH REPLACE or WITH STOPAT clause.\r\n\r\nA tail-log backup can be created independently of regular log backups by using the COPY_ONLY option. A copy-only backup does not affect the backup log chain. The transaction log is not truncated by the tail-log backup, and the log captured will be included in future normal log backups. This allows tail-log backups to be taken, for instance, to prepare for an online restore without affecting normal log backup procedures. For more information, see Copy-Only Backups (Full Recovery Model). *\/ \r\n\r\n\r\n\r\n-- Restore log info ms-help:\/\/MS.SQLCC.v9\/MS.SQLSVR.v9.en\/tsqlref9\/html\/\r\n877ecd57-3f2e-4237-890a-08f16e944ef1.htm.\r\n\r\n  \r\nSET @restoreStmt = N'-SQL RESTORE Log ' + @dbName + ' \r\nFROM DISK = ' \r\n+ @backupfile + ' \r\n WITH RECOVERY \r\n  \r\n,MOVE ' \r\n+ @logicalDataFile + ' TO ' + @physicalDatafile + ' \r\n,MOVE ' \r\n+ @logicalLogFile + ' TO ' + @physicalLogFile \r\n      \r\n+ ' \r\n,REPLACE \r\n,LOGTO = \"\\\\TestServer\\d$\\TtestDB\\DBLog\\<DATABASE>_<TYPE>_ <DATETIME yyyymmddhhmss>.txt\"' \r\n\r\n--PRINT @restoreStmt \r\n\r\nEXEC master..sqlbackup @restoreStmt, @exitcode OUT, @sqlerrorcode OUT \r\n\r\nIF ( @exitcode >= 500 ) \r\n      OR ( \r\n@sqlerrorcode <> 0 ) \r\n  \r\nBEGIN \r\n  RAISERROR \r\n( 'SQL Restore failed with exit code: %d SQL error code: %d', \r\n          \r\n16, 1, @exitcode, @sqlerrorcode ) \r\n  \r\nEND \r\n\r\n\r\n<\/pre>\n
      usp_RoboCopy <\/h4>\n
      CREATE   PROCEDURE [dbo].[usp_RoboCopy] \r\n  \r\n( \r\n    \r\n@srcUNC SYSNAME,    -- Source Server Name.\r\n    \r\n@dstUNC SYSNAME,    -- Destination Server Name.\r\n    \r\n@filelist VARCHAR(1024)  -- Space delimited list of files to be copied.\r\n  \r\n) \r\n      \r\nAS \/*****************************************************************\/\r\n-- Stored Procedure : usp_RoboCopy \r\n-- Creation Date : 2009-02-26 \r\n-- Written by : Stephen Mandeville, adapted by Hugo Shebbeare\r\n \/*************************************************************************\/ \r\nSET NOCOUNT ON \r\nDECLARE @ccmd VARCHAR(1500) \r\nDECLARE @logfile VARCHAR(25) \r\nDECLARE @retcode INT \r\n\/**************************************************************************\/\r\n-- This stored procedure uses ROBOCOPY.exe, which is installed on server itself\r\n-- in the sysroot\\windows\\system32 folder (default on 2008). \r\n-- The Source and Destination shares must exist.\r\n\/***************************************************************************\/\r\nSELECT  @logfile = REPLACE(SUBSTRING(( CONVERT(VARCHAR(15), GETDATE(), 121) ), \r\n          1, 10), '-', '') \r\n      + REPLACE(SUBSTRING(( CONVERT(VARCHAR(30), GETDATE(), 121) ), 12, \r\n          8), ':', '') \r\nSELECT  @ccmd = 'ROBOCOPY ' + @srcUNC + ' ' + @dstUNC + ' ' + @filelist \r\n      + ' \/NP \/LOG:' + @dstUNC + '\\transfer' + '_' + @logfile + '.txt' \r\n--PRINT @ccmd \r\nEXECUTE @retcode = master..xp_cmdshell @ccmd \r\n\r\n\/***************************************************************************\/\r\n-- The return code (@retcode) from Robocopy (version 1.74 and later) is a \r\n--bit map, defined as follows:\r\n--   \r\n-- Value    MeaningIfSet \r\n-- 16       Serious error. Robocopy did not copy any files. This is either a \r\n--                         usage error or an error due to insufficient \r\n--                         access privileges on the \r\n--                         source or  destination directories. \r\n-- 8        Some files or directories could not be copied \r\n--                         (copy errors occurred and the retry limit was \r\n--                         exceeded)Check these errors further.   \r\n-- 4        Some Mismatched files or directories were detected. \r\n--                         Examine the output log. \r\n--                         Housekeeping is probably necessary.     \r\n-- 2        Some Extra files or directories were detected. \r\n--                         Examine the output log. \r\n--                         Some housekeeping may be needed.     \r\n-- 1        One or more files were copied successfully.that is, new files  \r\n--                         have arrived).\r\n-- 0                       No errors occurred, and no copying was done. \r\n--                         The source and destination\r\n--                         directory trees are completely synchronized.\r\n\/**************************************************************************\/\r\n\r\n-- Raising error only upon @retcode > 7.\r\nIF @retcode > 7 \r\n  BEGIN \r\n  RAISERROR ( 'Error occurred while executing Robocopy', 16, 1 ) \r\n  RETURN ( @retcode ) \r\n  END --IF @retcode > 7 \r\nELSE \r\n  BEGIN \r\n  RETURN ( @retcode ) \r\n  END --ELSE \r\n\r\n<\/pre>\n
      usp_KillConnections<\/h4>\n
      IF NOT EXISTS ( \r\n  \r\nSELECT *  \r\n    \r\nFROM sys.objects  \r\n    \r\nWHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_KillConnections]')  \r\n      AND \r\ntype IN (N'P', N'PC')) \r\n  \r\nBEGIN \r\n  EXEC \r\ndbo.sp_executesql @statement = N' \r\n\/*****************************************************************  \r\n*** Procedure: usp_KillConnections  \r\n\r\n*** Usage: usp_KillConnections @dbname = ''Database Name''  \r\n\r\n*** Description: Drop all connections from a specific database  \r\n\r\n*** Input: @dbname - REQUIRED - Name of the database  \r\n*** Output: Outputs the results of the proccess  \r\n\r\n*** Revision: 1.0  \r\n*** Revision History: 1.0 First Release  \r\n*** Author: Antonio Pedrosa Linares  \r\n*** Date: 7\/25\/2007  \r\n******************************************************************\/  \r\n\r\n-- exec usp_KillConnections ''staplescpc'' \r\ncreate procedure [dbo].[usp_KillConnections]  \r\n    @dbname varchar(128)  \r\nas  \r\n    declare @spid varchar(5)  \r\n    declare @loginname nvarchar(128)  \r\n    declare @intErrorCode int  \r\n    declare @intOk int  \r\n    declare @intError int  \r\n    declare @intTotal int  \r\n\r\n    set @intErrorCode = 0  \r\n    set @intOk = 0  \r\n    set @intError = 0  \r\n    set @intTotal = 0  \r\n\r\n    select @intTotal = count(sp.spid) FROM master..sysprocesses sp  \r\n    JOIN master..sysdatabases sd ON sp.dbid = sd.dbid  \r\n    WHERE sd.name = @dbname  \r\n      \r\n    declare KILL_CONS cursor for  \r\n    SELECT cast(sp.spid as varchar(5)),rtrim(sp.loginame)  \r\n    FROM master..sysprocesses sp  \r\n    JOIN master..sysdatabases sd ON sp.dbid = sd.dbid  \r\n    WHERE sd.name = @dbname  \r\n      \r\n    OPEN KILL_CONS  \r\n\r\n    FETCH NEXT FROM KILL_CONS INTO @spid,@loginname  \r\n    WHILE @@FETCH_STATUS = 0  \r\n    BEGIN  \r\n        EXEC(''Kill ''+ @spid + '''')  \r\n        SELECT @intErrorCode = @@ERROR  \r\n        if @intErrorCode = 0  \r\n        begin  \r\n            set @intOk = @intOk + 1  \r\n            PRINT ''Process '' + @spid + '' from login '' \r\n                    + @loginname + '' has been ended.''  \r\n        end  \r\n        else  \r\n        begin  \r\n            set @intError = @intError + 1  \r\n            PRINT ''Process '' + @spid + '' from login '' \r\n                    + @loginname + '' could not be ended.''  \r\n        end  \r\n        FETCH NEXT FROM KILL_CONS INTO @spid,@loginname  \r\n    END  \r\n    CLOSE KILL_CONS  \r\n    DEALLOCATE KILL_CONS  \r\n    PRINT ''Total number of processes from database '' \r\n           + @dbname + '': '' + cast (@intTotal as varchar)  \r\n    PRINT ''Processes ended normally: '' + cast(@intOk as varchar)  \r\n    PRINT ''Processes could not be ended: '' + cast(@intError as varchar)' \r\n  \r\nEND  \r\n<\/pre>\n
      System Database Backups<\/h3>\n
      On the DRP server itself the backups of the MSDB, DBAs databases, which are critical to this whole DRP process are located here:<\/p>\n
      \\\\DRPServerName:\\DRPbackupFolder\\Full <\/strong><\/p>\n
      There should always<\/strong> be an alternative local backup location for system databases, such as on the Test server.<\/p>\n
      All DBAs and system databases are backed up as well as on:<\/p>\n
      \\\\TstServerName:\\TestSrvBackupFolder\\Full<\/strong><\/p>\n
      The following example was tested on a primary test server and exists on the restore server.  The usp_DB_restoreX<\/em> stored procedure takes 6 input parameters.  To match up with backup log metadata, we shall match up the database name by date and then pull the relevant restore file input parameter into the appropriate usp_DB_restoreX<\/em> stored procedure.  The master restore procedures, divided into single file and multiple file restore procedures, use all the sub procedures to do the actual restore process. <\/p>\n
      Please note that the usp_DB_RestoreX stored procedures are dependent on usp_KillConnections  which will help in the restoration process by killing the existing database users (that is, unless it’s a system user however).<\/em><\/p>\n
      e.g.<\/p>\n
      EXEC DBA_Tools.dbo.usp_DB_restore '\\\\TestServerName\\Drive$\\ProductionBackupFolder\\  Full\\FULL_ServerName_Database1_20080217_030000.sqb', 'DBlogicalName' 'DB_DataFile_Logicalname', 'DB_LogFileLogical_name',\r\n'DriveName:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\\r\nDBphysicalDataFileName.mdf',\r\n'DriveName:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\\r\nDBphysicalLogFileName_log.ldf'\r\n<\/pre>\n
      The stored procedure usp_DB_restore_norecovery<\/em> is the same as usp_DB_restore<\/em>, only for Databases that need to be left in norecovery mode (as described earlier in the article)<\/span><\/p>\n
      Please view the Activity History from Red Gate SQL Backup for reporting on what databases have been backed up, as the scope of this document covers the restoring process only. Although the backup information is extracted to prepare the automated restore scripts within the jobs, we are not going to create (at least at this stage) customised backup reporting information.  However, do not forget that, since we are using these scripts within a SQL Server Agent job, we will have histories for each step and a log file written to the \\DBlog\\ <\/strong>folder local to the disaster recovery server running these SQL Agent Jobs.<\/p>\n
      \"791-SQB_activityhistory.gif\"<\/p>\n
      Figure 2 – SQL Backup Activity Log<\/em><\/p>\n
      Database Restore method when applying Differential Backups.<\/h3>\n
      Please note that we use usp_restore_db_norecovery <\/em>to load a production backup from the local copy moved over using Robocopy<\/b>. Thus, if executed on the DBA database of the DRP server (SERVER NAME \/ INSTANCE NAME<\/em>):<\/p>\n
      EXEC DBA_Tools.dbo.usp_DB_restore_norecovery   \\\\DRPserver\\InstanceName\\full\\FULL_ServerName_Database_20080217_030000.sqb', 'VSOT2', 'VSOT2_data', 'VSOT2_log,\r\n'D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\VSOT2.mdf, 'D:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\VSOT2_log.ldf'\r\n<\/pre>\n
      This will be the core of what runs for the second step of an automated job which leaves the database in NoRecovery mode, and thus should call the respective RestoreDiff_dbx<\/em> next and, finally, apply the log files via RestoreLog_dbx<\/em>. <\/p>\n
      After the restore, make sure to run several tests that ensure the integrity of the data and that typical applications can run normal operations on the database.<\/p>\n
      \n
      Summary <\/h2>\n
      Is this disaster recovery method really minimizing the manual intervention after failure? Can we make it better? Yes and yes, but there’s always room to improve. More importantly, this method certainly doesn’t suit every environment. Before you take what I’ve put together here and run with it, I strongly recommend you take a look at the High Availability Options table below to get a clear picture of what methodologies might be more appropriate for you individual needs. To make an effective choice, you’re naturally going to need a detailed understanding of each clients’ needs for the restore process.<\/p>\n
      High Availability Options<\/h4>\n\n\n\n\n\n\n\n\n\n\n
      \n
      Solution<\/strong> <\/p>\n<\/td>\n
      		\n
      Cost<\/strong> <\/p>\n<\/td>\n
      		\n
      Complexity<\/strong> <\/p>\n<\/td>\n
      		\n
      Failover<\/strong> <\/p>\n<\/td>\n
      		\n
      Failback<\/strong> <\/p>\n<\/td>\n<\/tr>\n
      \n
      Hardware Clustering<\/p>\n<\/td>\n
      		\n
      High<\/p>\n<\/td>\n
      		\n
      High<\/p>\n<\/td>\n
      		\n
      Fast<\/p>\n<\/td>\n
      		\n
      Fast<\/p>\n<\/td>\n<\/tr>\n
      \n
      Software Clustering<\/p>\n<\/td>\n
      		\n
      High<\/p>\n<\/td>\n
      		\n
      High<\/p>\n<\/td>\n
      		\n
      Fast<\/p>\n<\/td>\n
      		\n
      Fast<\/p>\n<\/td>\n<\/tr>\n
      \n
      Replication<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Medium with manual
      processing<\/p>\n<\/td>\n
      		\n
      Slow with manual
      processing<\/p>\n<\/td>\n<\/tr>\n
      \n
      Continuous Data
      Protection<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Slow<\/p>\n<\/td>\n<\/tr>\n
      \n
      Log Shipping<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Medium<\/p>\n<\/td>\n
      		\n
      Slow<\/p>\n<\/td>\n<\/tr>\n
      \n
      Backup and
      Restore<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Slow<\/strong><\/p>\n<\/td>\n
      		\n
      Slow<\/strong><\/p>\n<\/td>\n<\/tr>\n
      \n
      Database Mirroring<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Low<\/p>\n<\/td>\n
      		\n
      Fast, but only at the
      database level<\/p>\n<\/td>\n
      		\n
      Fast, but only at the
      database level<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      At the time of writing, our backup-and-restore is super slow – at least 13 hours<\/strong><\/em> before we were live on the warm standby – but if optimization is run, we should be done in around 2 hours.<\/p>\n
      Nobody wants to go through a disaster without being properly prepared. When I was asked to prepare a plan for Canada’s largest institutional fund manager, I took it rather seriously, hence the length of this document.  We ran this through a real disaster recovery test over a weekend, and it all worked out just fine.  I’ve tried to share with you exactly how you can get your own disaster recovery plan in place, so that when the time comes at least the recovery step itself is not a disaster.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      High-Availability depends on how quickly you can recover a production system after an incident that has caused a failure. This requires planning, and documentation. If you get a Disaster Recovery Plan wrong, it can make an incident into a catastrophe for the business. Hugo Shebbeare discusses some essentials, describes a typical system, and provides sample documentation.…<\/p>\n","protected":false},"author":89116,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4294,4248,4249,4178,4168,4932,4598,4150,4485,4151],"coauthors":[47075],"class_list":["post-658","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-availability","tag-backup","tag-backup-and-recovery","tag-bi","tag-database","tag-disaster-recovery","tag-recovery","tag-sql","tag-sql-backup","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/89116"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=658"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/658\/revisions"}],"predecessor-version":[{"id":84894,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/658\/revisions\/84894"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=658"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}