28 September 2006

Pop Rivett’s SQL Server FAQ No.3: Pop restores

Pop describes a stored procedure for restoring the most recent full backup of a SQL Server database and then applying all transaction logs. Handy to have around in case of emergency...

Restoring SQL Server databases


Q: Dashed sorry to interrupt, Pop, but I seem to have accidentally deleted all the databases from the live database server. I thought I was removing them from my DEV machine you see, but just after I’d done it I glanced up at the bar at the top of the Query Analyser window and noticed that I was in the production system. Awfully rotten luck, what? Anyway, I…err…[shifting nervously] I was wondering if you could help?

Pop: Blast you to perdition, boy! Just thank your lucky stars that I put in those scripts to do the backups. Now go and restore them from before I lose my temper!

But, Pop, manually restoring each database one-by-one, applying the latest logs…it will take hours…we need those databases back up now! Isn’t there an easier way?

Pop: […Sigh…] Well, now you mention it, I did write a rather splendid stored procedure for doing a restore…[rummaging in desk]…Ah, here it is! It restores a database from a full backup, and then applies the logs. You’ll find the backups in a directory – they will all be of the .bak file type. The full backups all have the word _FULL_ in them after the name of the database, and the logs all have the word _LOG_ after the name of the database:

--s_RestoreDatabase - restore all the databases from a full backup
--and then apply logs

ALTER PROC s_restoredatabase
   @SourcePath  VARCHAR(200), --the location of the backup files
   @archivePath VARCHAR(200), --where to archive the backup files
   @DataPath    VARCHAR(200), --location to restore the data file(s)
   @LogPath     VARCHAR(200), --location to restore the log file(s)
   @recover     VARCHAR(10),  -- recover, norecovery, standby
   @recipients  VARCHAR(128= NULL,    
   @Database    VARCHAR(128= NULL /* -- only restore
                                           this database */

  For this to work, your backup files should be of the
  .BAK file type, and the files must have the word
  _FULL_ for a full backup or _LOG_ for a log file

  Example of use:   
   exec s_RestoreDatabase
      @SourcePath = 'c:\atest' ,
      @archivePath = 'c:\atest\archive' ,
      @DataPath = 'c:\atest' ,
      @LogPath = 'c:\atest' ,
      @recover = 'norecovery' ,
      @recipients = '' */

    Get all files from directory (they will be *.bak).
    The process is controlled by the files in the directory.
    If there is a full backup then restore it.

    If there is a diff then restore it next.
    Restore any logs

    Recover database if necessary

DECLARE  @dbname   VARCHAR(128),
   @cmd      VARCHAR(2000),
   @filename VARCHAR(128),
   @s        VARCHAR(128),
   @t        VARCHAR(128),
   @sql      NVARCHAR(2000)

   lname   VARCHAR(128),
   pname   VARCHAR(128),
   TYPE    VARCHAR(10),
   fgroup  VARCHAR(128),
   size    VARCHAR(50),
   maxsize VARCHAR(50))

   s VARCHAR(2000))

/* -- get list of files in directory */
SELECT @cmd = 'dir /B ' + @SourcePath + '*.bak'
INSERT #dir    
   EXEC master..xp_cmdshell  @cmd
DELETE #dir --delete anything which is not a backup
   OR s NOT LIKE '%.bak'
   OR (s NOT LIKE '%^_Full^_%' ESCAPE '^'
   AND s NOT LIKE '%^_Log^_%' ESCAPE '^')
IF @Database IS NOT NULL --if he has specified a database
   DELETE #dir
      WHERE  s NOT LIKE @Database + '^_%' ESCAPE '^'

  /* -- deal with each database in turn */
    /* -- any with a full first */
      SELECT @dbname = NULL
      SELECT   TOP @dbname = LEFT(s,CHARINDEX('_Full_',s) - 1)
             FROM     #dir
             WHERE    CHARINDEX('_Full_',s) <> 0
             ORDER BY s    
    /* -- no full - get log */
      IF @dbname IS NULL
          SELECT @dbname = NULL
          SELECT   TOP @dbname = LEFT(s,CHARINDEX('_Log_',s) - 1)
                 FROM     #dir    
                 WHERE    CHARINDEX('_Log_',s) <> 0
          ORDER BY s
    /* -- find the last full backup for this db */
      SELECT @filename = NULL
      SELECT @filename = MAX(s)FROM #dir
       WHERE s LIKE @dbname + '_Full_%.bak'
    /* -- archive everything for this db that appears
                                   before this file */

      SELECT @s = ''    
      WHILE @s IS NOT NULL AND @filename IS NOT NULL
          SELECT   TOP @s = s
          FROM #dir
              WHERE s LIKE @dbname + '%'
          ORDER BY RIGHT(s,20)    
          IF @s = @filename
              SELECT @s = NULL
            /* -- move to archive */
              SELECT @cmd = 'move ' + @SourcePath
                       + @s + ' ' + @archivePath + @s
              EXEC master..xp_cmdshell  @cmd
              DELETE #dir WHERE  s = @s
 /* -- now we can go through each file in turn for this
                               database and restore it */
       (SELECT * FROM   #dir
                WHERE  s LIKE @dbname + '^_%' ESCAPE '^')
          SELECT   TOP @filename = s
               FROM #dir
               WHERE s LIKE @dbname + '^_%' ESCAPE '^'
          ORDER BY RIGHT(s,20)
          IF @filename LIKE '%^_Full^_%' ESCAPE '^'
            /* -- restore a full backup */
              IF EXISTS (SELECT *
                         FROM master..sysdatabases
                         WHERE name = @dbname)
/* -- Drop the database before starting the restore */
                  SELECT @cmd = 'drop database ' + @dbname
                  EXEC( @cmd)
              SELECT @cmd = 'restore filelistonly  from disk = '''
                       + @SourcePath + @filename + ''''
              DELETE #files
              INSERT #files
              EXEC( @cmd)
              /* -- now build the restore */
              SELECT @cmd = NULL, @s = ''
               WHILE @s <
                 (SELECT MAX(lname) FROM #files)
                  SELECT TOP 1 @s = lname, @t = TYPE
                  FROM #files
                         WHERE lname > @s
                         ORDER BY lname
                  SELECT @cmd = COALESCE(@cmd + ',move ','')
                       + '''' + @s + ''' to '''
                       + CASE WHEN @t = 'D' THEN @DataPath
                       + @s + ''''

 /*The MOVE is used to relocate the database files
   and to avoid collisions with existing files. */

              SELECT @cmd = 'restore database '
+ @dbname
+ ' from disk = '''
+ @SourcePath
+ @filename
+ ''' with move '
+ @cmd    
                               + ', NORECOVERY'
    /* -- + ', standby = ''' + @localpath + 'standby.fil''' */
              EXEC( @cmd)    
            IF @filename LIKE '%^Log^_%' ESCAPE '^'
              /* -- restore a log backup */
                SELECT @cmd = 'restore log '
+ @dbname
+ ' from disk = '''
+ @SourcePath
+ @filename

                                 + ''' with NORECOVERY'
                EXEC( @cmd)    
              /* -- move to archive */
          SELECT @cmd = 'move '
+ @SourcePath
+ @filename
+ ' '
                          + @archivePath
+ @filename
          EXEC master..xp_cmdshell  @cmd
          DELETE #dir WHERE  s = @filename
        /* -- now set to correct recovey mode */
      IF @recover = 'recover'
          SELECT @cmd = 'restore database '
+ @dbname
+ ' with recovery'
          EXEC( @cmd)

Notice that the stored procedure will restore the latest full backup in the folder, for each database, and then apply all following transaction logs in sequence. It will also move data and log files to a specified folder, if required. Clever, what? From now on you should use this procedure to test restore every full backup because, if you ask me, a system which doesn’t include backup restore tests is at a similar risk level to one without any backups at all.

Will do, Pop. And thanks…this will save hours!

Yes, m’ boy, it is a good idea to keep my stored procedure handy for just this sort of emergency! Now run along and get those databases restored before I fire you…

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter


This post has been viewed 14686 times – thanks for reading.

Tags: , , , , , ,

  • Rate
    [Total: 17    Average: 4.6/5]
  • Share

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

View all articles by Pop Rivett

  • Anonymous

    Nearly as useful as…
    …MySql – so keep plugging away, I’m sure you’ll get there in the end.

  • Anonymous

    Bug Report
    Line 87 should be ORDER BY 1

  • nigelrivett

    Re: Bug Report
    order by s

    Good spot – looks like it was lost in formatting.


  • Tony Davis

    Bug fix
    I have fixed this bug and also included the full source code for the stored procedure for people to download. Just click on the Source Code link in the bubble to the right of th earticle title.


    Tony (Simple-Talk Ed)

  • Anonymous

    Different Servers
    Should this script work, if you want to restore the backups to a different server. i.e find all the latest backups on live server and restore to test server?

  • Anonymous

    Different Servers
    Should this script work, if you want to restore the backups to a different server. i.e find all the latest backups on live server and restore to test server?