{"id":102480,"date":"2024-07-08T04:57:04","date_gmt":"2024-07-08T04:57:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102480"},"modified":"2024-07-10T14:46:59","modified_gmt":"2024-07-10T14:46:59","slug":"its-a-recovery-strategy-not-a-backup-strategy","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/its-a-recovery-strategy-not-a-backup-strategy\/","title":{"rendered":"It&#8217;s a recovery strategy, not a backup strategy"},"content":{"rendered":"<p>I&#8217;ve <a title=\"#BackToBasics \/ T-SQL Tuesday: Are your backups worthless?\" href=\"https:\/\/orangematter.solarwinds.com\/2016\/12\/13\/backups-worthless\/\" target=\"_blank\" rel=\"noopener\">talked about it before<\/a>; you shouldn&#8217;t have a backup strategy, you should have a recovery strategy. I can&#8217;t possibly care if my backups succeed if I&#8217;m not bothering to test that they can be restored. And if they can&#8217;t be restored then, both technically and practically, I don&#8217;t have backups.<\/p>\n<p>In one of the systems I manage, they built a very simple &#8220;test restore&#8221; process long before I became involved. Every night, it would pull the full backup for each database, restore it on a test system, and run <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">DBCC CHECKDB<\/code> against it. It would alert on any failure, of course, but the primary purpose was to always be confident that the backups could, in fact, be restored.<\/p>\n<p>The process could afford to be simple because it assumed the following would always be true:<\/p>\n<ul>\n<li>All databases took full backups every night.<\/li>\n<li>Each database was backed up to a single <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">.bak<\/code> file named <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">&lt;DatabaseName&gt;.FULL.&lt;YYYYMMDD&gt;.bak<\/code>.<\/li>\n<li>Each database we were restoring this way consisted of a single data file (called <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">DataFile<\/code>) and a single log file (<code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">LogFile<\/code>).<\/li>\n<li>All databases were backed up to the same location (and in a subfolder exactly matching the database name), and all databases would always be able to <em>fit<\/em> in that location.<\/li>\n<li>The biggest database, once restored, would always fit on a single drive on the test system.<\/li>\n<li>All databases could be restored and <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">CHECKDB<\/code>&#8216;d in under 24 hours.<\/li>\n<\/ul>\n<p>There is a table that tells the code which databases to restore:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE TABLE dbo.SourceDatabases\r\n (\r\n   DatabaseName sysname\r\n   \/* other columns not pertinent to this post *\/\r\n );<\/pre>\n<p>And another table to tell the code, for example, where to find backups, and where to move data\/log files on restore:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE TABLE dbo.RestoreConfig\r\n (\r\n   SourceFolder           nvarchar(4000),\r\n   DestinationDataFolder  nvarchar(4000),\r\n   DestinationLogFolder   nvarchar(4000),\r\n   TempDBFolder           nvarchar(4000),\r\n   ...\r\n );\r\n\r\n INSERT dbo.RestoreConfig\r\n (\r\n   SourceFolder,\r\n   DestinationDataFolder,\r\n   DestinationLogFolder,\r\n   TempDBFolder\r\n )\r\n VALUES\r\n (\r\n   N'\\\\corp-backup-share\\sql-backups\\', \r\n   N'D:\\SQL\\Data\\',\r\n   N'L:\\SQL\\Log\\',\r\n   N'T:\\SQL\\TempDB\\'\r\n );\r\n<\/pre>\n<p>Then the (vastly simplified) code looked something like this:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> DECLARE @Source       nvarchar(4000), \r\n         @DataFolder   nvarchar(4000),\r\n         @LogFolder    nvarchar(4000),\r\n         @dbname       sysname,\r\n         @ymd          char(8) = CONVERT(char(8), getutcdate(), 112),\r\n         @sql          nvarchar(max),\r\n         @c            cursor;\r\n\r\n  SELECT @Source     = SourceFolder,\r\n         @DataFolder = DestinationDataFolder,\r\n         @LogFolder  = DestinationLogFolder\r\n    FROM dbo.RestoreConfig;\r\n\r\n     SET @c = CURSOR STATIC READ_ONLY FORWARD_ONLY\r\n          FOR SELECT DatabaseName \r\n                FROM dbo.SourceDatabases\r\n               ORDER BY DatabaseName;\r\n\r\n OPEN @c;\r\n FETCH NEXT FROM @c INTO @dbname;\r\n\r\n WHILE @@FETCH_STATUS &lt;&gt; -1\r\n BEGIN\r\n   SET @sql = N'RESTORE DATABASE ' + QUOTENAME(@dbname + N'_testing') + N'\r\n              FROM DISK = N''' + @Source + @dbname + N'\\'\r\n            + @dbname + N'.FULL.' + @ymd + N'.bak'''\r\n              WITH REPLACE, RECOVERY,\r\n              MOVE N''DataFile'' TO N''' + @DataFolder\r\n            + @dbname + N'.' + @ymd + N'.mdf''',\r\n              MOVE N''LogFile'' TO N''' + @LogFolder\r\n            + @dbname + N'.' + @ymd + N'.ldf'';';\r\n\r\n   EXEC sys.sp_executesql @sql;\r\n\r\n   \/* then do CHECKDB things *\/\r\n\r\n   FETCH NEXT FROM @c INTO @dbname;\r\n END <\/pre>\n<p>I&#8217;ll let you guess how many of those assumptions remain facts today. If you guessed zero, you&#8217;d be right. Life got complicated, the restore system is now also being used for other purposes, and source databases have grown significantly. I&#8217;d have to help adjust the code to deal with those changes, and this post is about how.<\/p>\n<p>Here is their new reality relative to the &#8220;truths&#8221; above:<\/p>\n<ul>\n<li>They started performing full backups weekly, with differential backups in between.<\/li>\n<li>They started striping backups to multiple files.<\/li>\n<li>Many databases had additional files and filegroups; even those that didn&#8217;t started having logical file names straying from the existing convention.<\/li>\n<li>Different databases had to be backed up to different locations.<\/li>\n<li>The biggest database outgrew the <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">D:\\<\/code> drive on the test system.<\/li>\n<li>The biggest database also squeezed out all the others in terms of duration, so that they couldn&#8217;t all be backed up <em>and<\/em> <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">CHECKDB<\/code>&#8216;d within a calendar day.<\/li>\n<\/ul>\n<p>Some things did remain true, though:<\/p>\n<ul>\n<li>They always produce backups <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">WITH INIT<\/code>, so we never have to figure out which file (backup set) we need within any given <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">.bak<\/code> file.<\/li>\n<li>They don&#8217;t use features like <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">FILESTREAM<\/code>, <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">FILETABLE<\/code>, or In-Memory OLTP, so we never have to consider those differences. They do use full-text search, but there are no dedicated files for full-text catalogs involved in backup\/restore testing.<\/li>\n<li>They encrypt backups, but we&#8217;re going to leave that complication out for now.<\/li>\n<\/ul>\n<p>Also, while backups might be striped to 8 or 16 files (or any number, really), they produce file names in a predictable format, e.g. <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">&lt;DatabaseName&gt;.FULL.&lt;YYYYMMDD&gt;.&lt;file number&gt;.bak<\/code>. Backups are generated using <a href=\"https:\/\/ola.hallengren.com\/sql-server-backup.html\" target=\"_blank\" rel=\"noopener\">Ola Hallengren&#8217;s solution<\/a>, so this is easy to do by passing in the right <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">@FileName<\/code> and <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">@NumberOfFiles<\/code> arguments:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> EXEC dbo.DatabaseBackup\r\n   @FileName      = N'{DatabaseName}.{Type}.{Year}{Month}{Day}.{StripeNumber}.bak',\r\n   @NumberOfFiles = 16\r\n   \/* , ... other parameters *\/\r\n<\/pre>\n<p>We actually store that pattern in the (distributed) config table too, so we can change it in one place instead of in every SQL Server Agent backup job on every instance.<\/p>\n<p>Similarly, I created table types for things we do often enough that we don&#8217;t want to define them over and over again. Because the test server is on a different subnet, and because the backups come from servers all over the network, we can&#8217;t always query <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">msdb<\/code> on a source instance to get backup information about a given database. So, instead, we rely on looking at the folder and finding the most recent file, then examining the output of these <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">RESTORE<\/code> commands:<\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/restore-statements-labelonly-transact-sql\" target=\"_blank\" rel=\"noopener\"><code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">RESTORE LABELONLY<\/code><\/a> (for determining how many stripes exist for a given backup):<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE TYPE dbo.RestoreLabelOnlyOutput AS TABLE\r\n (\r\n   MediaName             nvarchar(128),\r\n   MediaSetID            uniqueidentifier,\r\n   FamilyCount           int,\r\n   FamilySequenceNumber  int,\r\n   MediaFamilityID       uniqueidentifier,\r\n   MediaSequenceNumber   int,\r\n   MediaLabelPresent     tinyint,\r\n   MediaDescription      nvarchar(255),\r\n   SoftwareName          nvarchar(128),\r\n   SoftwareVendorID      int,\r\n   MediaDate             datetime,\r\n   MirrorCount           int,\r\n   IsCompressed          bit\r\n );<\/pre>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/restore-statements-filelistonly-transact-sql\" target=\"_blank\" rel=\"noopener\"><code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">RESTORE FILELISTONLY<\/code><\/a> (for determining the logical data and log files present in the backup):<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE TYPE dbo.RestoreFilelistOnlyOutput AS TABLE\r\n (\r\n   LogicalName           nvarchar(128),\r\n   PhysicalName          nvarchar(260), \r\n   Type                  char(1), \r\n   FileGroupName         nvarchar(128), \r\n   Size                  numeric(20,0),\r\n   MaxSize               numeric(20,0), \r\n   FileID                bigint, \r\n   CreateLSN             numeric(25,0), \r\n   DropLSN               numeric(25,0), \r\n   UniqueID              uniqueidentifier, \r\n   ReadOnlyLSN           numeric(25,0), \r\n   ReadWriteLSN          numeric(25,0),\r\n   BackupSizeInBytes     bigint, \r\n   SourceBlockSize       int, \r\n   FileGroupID           int, \r\n   LogGroupGUID          uniqueidentifier, \r\n   DifferentialBaseLSN   numeric(25,0), \r\n   DifferentialBaseGUID  uniqueidentifier, \r\n   IsReadOnly            bit, \r\n   IsPresent             bit, \r\n   TDEThumbprint         varbinary(32), \r\n   SnapshotUrl           nvarchar(360)\r\n );<\/pre>\n<p>We don&#8217;t need all of those columns, of course, but there&#8217;s no way to say:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> SELECT &lt;only these columns&gt; FROM (RESTORE FILELISTONLY ...);<\/pre>\n<p>Next, we added columns to the <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">SourceDatabases<\/code> table to indicate where each database sends its backups and on what day of the week the fulls run. Then we used <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/snake-draft-sorting-in-sql-server-part-1\/\" target=\"_blank\" rel=\"noopener\">snake sorting<\/a> to distribute the backups and restores across days of the week as evenly as possible. Let&#8217;s say we have this:<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE TABLE dbo.SourceDatabases\r\n (\r\n   DatabaseName     sysname,\r\n   BackupFolder     nvarchar(4000),\r\n   FullBackupDay    tinyint\r\n   \/* other columns not pertinent to this post *\/\r\n );\r\n\r\n INSERT dbo.SourceDatabases\r\n (\r\n   DatabaseName,\r\n   BackupFolder,\r\n   FullBackupDay\r\n )\r\n VALUES(N'DB_A', N'\\\\backup-share-1\\SourceServerA\\Full\\', 1),\r\n       (N'DB_B', N'\\\\backup-share-1\\SourceServerA\\Full\\', 1),\r\n       (N'DB_C', N'\\\\backup-share-2\\SourceServerB\\Full\\', 2);\r\n\r\n \/* so, for example, DB_A is backed up to share 1 on Sundays *\/\r\n<\/pre>\n<p>With the table types in place and the knowledge of where and when to find backups, we could make the code a lot more dynamic and determine exactly how to restore each database &#8211; even if it has been striped to multiple backup files and contains multiple data files. If there <em>are<\/em> multiple data files, we can alternate them onto the two drives we have available &#8211; even though one drive is <em>supposed<\/em> to be for <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">tempdb<\/code>, it can serve as a temporary workaround as it has plenty of available space.<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> CREATE PROCEDURE dbo.TestRestores\r\n   @RunCheckDB   bit = 0,\r\n   @debug        bit = 0\r\n AS\r\n BEGIN\r\n   SET NOCOUNT ON;\r\n\r\n   DECLARE @DataFolderA     nvarchar(4000),\r\n           @DataFolderB     nvarchar(4000),\r\n           @LogFolder       nvarchar(4000),\r\n           @SourceRoot      nvarchar(4000),\r\n           @Source          nvarchar(4000),\r\n           @DirCommand      nvarchar(4000),\r\n           @BaseFile        nvarchar(4000),\r\n           @FileCount       tinyint,\r\n           @TestDBName      sysname,\r\n           @DatabaseName    sysname,\r\n           @Today           date = getutcdate(),\r\n           @ymd             char(8) = CONVERT(char(8), getutcdate(), 112),\r\n           @Stripe          tinyint,\r\n           @FromCommands    nvarchar(max),\r\n           @MoveCommands    nvarchar(max),\r\n           @LabelCommand    nvarchar(4000),\r\n           @FileListCommand nvarchar(4000),\r\n           @c               cursor;\r\n\r\n    SELECT @DataFolderA   = DestinationDataFolder,\r\n           @DataFolderB   = TempDBFolder,\r\n           @LogFolder     = DestinationLogFolder\r\n      FROM dbo.RestoreConfig;\r\n\r\n   \/* get only databases with full backups today *\/\r\n\r\n   SET @c = CURSOR STATIC READ_ONLY FORWARD_ONLY\r\n        FOR SELECT DatabaseName, BackupFolder\r\n              FROM dbo.SourceDatabases\r\n             WHERE FullBackupDay = DATEPART(WEEKDAY, @Today)\r\n             ORDER BY DatabaseName;\r\n   OPEN @c;\r\n   FETCH NEXT FROM @c INTO @DatabaseName, @SourceRoot;\r\n\r\n   WHILE @@FETCH_STATUS &lt;&gt; -1\r\n   BEGIN\r\n     \/* where are we looking for backups? *\/\r\n\r\n     SELECT @Source     = CONCAT(@SourceRoot, @DatabaseName, N'\\'),\r\n            @TestDBName = CONCAT(@DatabaseName, N'_Testing');\r\n   \r\n     \/* perform a directory listing, newest first, stuff into @table *\/\r\n     \/* xp_cmdshell must be enabled, and service account needs access *\/\r\n\r\n     SET @DirCommand = CONCAT('dir \/b \/O:D \"', @Source, '*.bak\"');\r\n     DECLARE @List TABLE(fn nvarchar(128));\r\n     INSERT @List EXEC master.sys.xp_cmdshell @DirCommand;\r\n\r\n     \/* is there one created today? is it striped? *\/\r\n     \/* bit messy since database names may contain delimiters *\/\r\n     \/* uses SQL Server 2022's STRING_SPLIT with ordinal *\/\r\n     \/* if on earlier version, find ordered string splitter *\/\r\n\r\n     SELECT @BaseFile = fn, @Stripe = REVERSE(value)\r\n     FROM \r\n     (\r\n       SELECT TOP (1) fn \r\n         FROM @List\r\n        WHERE fn LIKE @DatabaseName + N'.FULL.' + @ymd + N'.%bak'\r\n        ORDER BY fn DESC\r\n     ) AS y \r\n       CROSS APPLY STRING_SPLIT(REVERSE(fn), N'.', 1) AS x\r\n       WHERE ordinal = 2;\r\n\r\n     IF @BaseFile IS NOT NULL\r\n     BEGIN\r\n       IF TRY_CONVERT(tinyint, @Stripe) BETWEEN 1 AND 99\r\n       BEGIN\r\n         \/* striped backup! Need to build FROM DISK = args *\/\r\n\r\n         SET @LabelCommand = CONCAT\r\n                 (N'RESTORE LABELONLY FROM DISK = N''', @Source, @BaseFile, N''';');\r\n\r\n         DECLARE @Labels dbo.RestoreLabelOnlyOutput;\r\n         INSERT @Labels EXEC sys.sp_executesql @LabelCommand;\r\n         SELECT @FileCount = FamilyCount FROM @Labels;\r\n\r\n         \/* relies on SQL Server 2017's STRING_AGG *\/\r\n         \/* relies on SQL Server 2022's GENERATE_SERIES *\/\r\n         \/* if earlier, find group concat \/ sequence generation functions *\/\r\n\r\n         SELECT @FromCommands = N' FROM '\r\n              + STRING_AGG\r\n                (CONVERT(nvarchar(max), CONCAT(N'  \r\n                 DISK = N''', @Source, @DatabaseName, N'.FULL.', @ymd, N'.',\r\n                   value, N'.bak''')), N',')\r\n           FROM GENERATE_SERIES(1, CONVERT(int, @Stripe)) AS x;\r\n       END\r\n       ELSE\r\n       BEGIN\r\n         \/* just a single file *\/\r\n\r\n         SET @FromCommands = CONCAT(N' FROM DISK = N''', @Source, @BaseFile, N'''');\r\n       END\r\n     END\r\n\r\n     \/* generate data\/log file args from any file *\/\r\n\r\n     SET @FileListCommand = CONCAT\r\n                 (N'RESTORE FILELISTONLY FROM DISK = N''', @Source, @BaseFile, ''';');\r\n     DECLARE @LogicalFiles dbo.RestoreFilelistOnlyOutput;\r\n     INSERT @LogicalFiles EXEC sys.sp_executesql @FileListCommand;\r\n\r\n     \/* alternate data files between data folder A &amp; B *\/\r\n     \/* note: assumes LogicalName is always valid in file name *\/\r\n\r\n     SELECT @MoveCommands = STRING_AGG\r\n            (CONVERT(nvarchar(max), CONCAT(N' \r\n             MOVE N''', LogicalName, N''' TO N''', \r\n                  CASE FileID % 2 WHEN 1 THEN @DataFolderA ELSE @DataFolderB END, \r\n                  @TestDBName, N'.', LogicalName, N'.', @ymd, N'.mdf''')), N',') \r\n       FROM @LogicalFiles\r\n      WHERE Type = 'D';\r\n\r\n     \/* also add log file(s) - yes, that happens *\/\r\n\r\n     SELECT @MoveCommands += N',' + STRING_AGG\r\n            (CONVERT(nvarchar(max), CONCAT(N'\r\n             MOVE N''', LogicalName, N''' TO N''', @LogFolder, \r\n                  @TestDBName, N'.', LogicalName, N'.', @ymd, N'.ldf''')), N',')\r\n       FROM @LogicalFiles\r\n      WHERE Type = 'L';\r\n\r\n     DECLARE @RestoreCommand nvarchar(max) = CONCAT(\r\n       N'RESTORE DATABASE ', QUOTENAME(@TestDBName), N'\r\n        ', @FromCommands, N'\r\n          WITH REPLACE, RECOVERY,', @MoveCommands, N';'\r\n       );\r\n\r\n     IF @debug = 1\r\n     BEGIN\r\n       PRINT @RestoreCommand;\r\n     END\r\n     ELSE\r\n     BEGIN\r\n       EXEC sys.sp_executesql @RestoreCommand;\r\n     END\r\n\r\n     IF @RunCheckDB = 1\r\n     BEGIN\r\n       \/* also check @debug here *\/\r\n       PRINT '\/* then do CHECKDB things *\/';\r\n     END\r\n   \r\n     FETCH NEXT FROM @c INTO @DatabaseName, @SourceRoot;\r\n   END\r\n END\r\n<\/pre>\n<p>Imagine <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">DB_A<\/code> has 4 data files and a log file, and is backed up to 8 stripes, this command would restore the database using the following command (though maybe not <em>quite<\/em> as pretty, formatting-wise):<\/p>\n<pre class=\"theme:tomorrow-night font:consolas font-size:14 line-height:16 decode-attributes:false tab-convert:true lang:tsql decode:true whitespace-before:1 whitespace-after:1\"> RESTORE DATABASE [DB_A_Testing]\r\n   FROM \r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.01.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.02.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.03.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.04.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.05.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.06.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.07.bak',\r\n     DISK = N'\\\\backup-share-1\\SourceServerA\\Full\\DB_A.FULL.20240501.08.bak'\r\n   WITH REPLACE, RECOVERY,\r\n     MOVE N'A_DataFile1' TO N'D:\\SQL\\Data\\DB_A_Testing.A_DataFile1.20240501.mdf',\r\n     MOVE N'A_DataFile2' TO N'T:\\SQL\\TempDB\\DB_A_Testing.A_DataFile2.20240501.mdf',\r\n     MOVE N'A_DataFile3' TO N'D:\\SQL\\Data\\DB_A_Testing.A_DataFile3.20240501.mdf',\r\n     MOVE N'A_DataFile4' TO N'T:\\SQL\\TempDB\\DB_A_Testing.A_DataFile5.20240501.mdf',\r\n     MOVE N'A_Log'       TO N'L:\\SQL\\Log\\DB_A_Testing.A_Log.20240501.ldf';\r\n<\/pre>\n<p>This seems like complicated code, but we think it is worth the investment because we don&#8217;t just want a backup strategy. We want to always be confident that we can <em>restore<\/em> from our backups. And the only way to be truly confident is to always be doing it &#8211; and by always improving this process, it makes us that much more prepared for dealing with a disaster recovery situation when it happens for real.<\/p>\n<h3>Future considerations<\/h3>\n<p>The code could, in theory, determine in advance if a database has grown enough to come close to or even exceed the available space of <em>both<\/em> available drives. In that case, maybe it would be time to add some storage (if possible) or move these operations to a bigger server.<\/p>\n<p>We also want to add into the mix occasional full + differential + log restores, since the current process only really tests full backups and not necessarily that we could recover to a specific point in time if we had to. This would complicate the code slightly &#8211; we&#8217;d have to identify the latest differential, and all subsequent log backups (or at least the first one), in order to generate the right <em>sequence<\/em> of <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">RESTORE<\/code> commands. But, overall, the logic would remain unchanged.<\/p>\n<p>And we may later have to change it if we can no longer test all the databases <em>in a week<\/em>. Maybe we only <code style=\"font-family: consolas; background: #e4e4e4; padding: 3px 4px 1px 4px; border-radius: 3px;\">CHECKDB<\/code> any given database every <em>other<\/em> week, or distribute all the databases across two weeks, or only test each database monthly, or build additional test servers and split the work that way. That&#8217;s not a tomorrow or next week problem, but it&#8217;s coming someday.<\/p>\n<h3>Further reading<\/h3>\n<p>Some other information, particularly if you get snagged on an older version and need to swap in alternative functions:<\/p>\n<ul>\n<li><a href=\"https:\/\/stackoverflow.com\/a\/70862904\" target=\"_blank\" rel=\"noopener\">Ordered string splitters for older versions<\/a> (Stack Overflow)<\/li>\n<li><a href=\"https:\/\/sqlperformance.com\/2014\/08\/t-sql-queries\/sql-server-grouped-concatenation\" target=\"_blank\" rel=\"noopener\">Grouped concatenation for older versions<\/a> (SQLPerformance)<\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/generate-series-to-build-a-set\/\" target=\"_blank\" rel=\"noopener\">Sequence generators for older versions<\/a> (Simple Talk)<\/li>\n<li><a href=\"https:\/\/sqlperformance.com\/2012\/11\/io-subsystem\/minimize-impact-of-checkdb\" target=\"_blank\" rel=\"noopener\">Minimizing the impact of DBCC CHECKDB<\/a> (SQLPerformance)<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve talked about it before; you shouldn&#8217;t have a backup strategy, you should have a recovery strategy. I can&#8217;t possibly care if my backups succeed if I&#8217;m not bothering to test that they can be restored. And if they can&#8217;t be restored then, both technically and practically, I don&#8217;t have backups. In one of the&#8230;&hellip;<\/p>\n","protected":false},"author":341115,"featured_media":103010,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53,143524],"tags":[4151],"coauthors":[158980],"class_list":["post-102480","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-sql-server","category-featured","category-sql-server","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102480","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\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=102480"}],"version-history":[{"count":41,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102480\/revisions"}],"predecessor-version":[{"id":103069,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102480\/revisions\/103069"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103010"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=102480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=102480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=102480"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=102480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}