Click here to monitor SSC
  • Av rating:
  • Total votes: 17
  • Total comments: 6
Pop Rivett

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

28 September 2006

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),
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
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
WHERE  s NOT LIKE @Database + '^_%' ESCAPE '^'

/* -- deal with each database in turn */
/* -- any with a full first */
SELECT @dbname = NULL
SELECT   TOP 1  @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
@dbname = NULL
SELECT   TOP 1  @dbname = LEFT(s,CHARINDEX('_Log_',s) - 1)
FROM     #dir    
WHERE    CHARINDEX('_Log_',s) <> 0
/* -- 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 = ''    
          SELECT   TOP
1  @s = s
FROM #dir
WHERE s LIKE @dbname + '%'
ORDER BY RIGHT(s,20)    
IF @s = @filename
@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
1  @filename = s
FROM #dir
WHERE s LIKE @dbname + '^_%' ESCAPE '^'
IF @filename LIKE '%^_Full^_%' ESCAPE '^'
/* -- restore a full backup */
FROM master..sysdatabases
WHERE name = @dbname)
/* -- Drop the database before starting the restore */
                  SELECT @cmd = 'drop database ' + @dbname
EXEC( @cmd)
@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    
/* -- + ', standby = ''' + @localpath + 'standby.fil''' */
EXEC( @cmd)    
@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'
@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…

Pop Rivett

Author profile:

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 Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Pop Rivett

Rate this article:   Avg rating: from a total of 17 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Nearly as useful as...
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 28, 2006 at 9:22 AM
Message: ...MySql - so keep plugging away, I'm sure you'll get there in the end.

Subject: Bug Report
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 28, 2006 at 9:47 AM
Message: Line 87 should be ORDER BY 1

Subject: Re: Bug Report
Posted by: nigelrivett (view profile)
Posted on: Thursday, September 28, 2006 at 10:06 AM
Message: well
order by s

Good spot - looks like it was lost in formatting.


Subject: Bug fix
Posted by: Tony Davis (view profile)
Posted on: Thursday, September 28, 2006 at 11:52 AM
Message: 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)

Subject: Different Servers
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 6:05 AM
Message: 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?

Subject: Different Servers
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 16, 2007 at 7:09 AM
Message: 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?

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Routine SQL DML Testing for the Unenthusiastic Tester

There are more exciting things in life than unit testing SQL Statements, checking the results, timings, and... Read more...

 View the blog

Top Rated

Jodie Beay and the Production Database Drift
 You make an example database, like NorthWind or WidgetDev in order to test out your deployment system... Read more...

SQL Server Data Tools (SSDT) and Database References
 SQL Server Data Tools (SSDT) provides, via the DacPac, interesting support for verifying not only... Read more...

Writing Build vNext tasks for Visual Studio Online
 Hosted TFS, now called Visual Studio Online (VSO), has a new way of writing build processes called... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.