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 */

AS
 
/*
  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)

CREATE TABLE #files (
  
lname   VARCHAR(128),
  
pname   VARCHAR(128),
  
TYPE    VARCHAR(10),
  
fgroup  VARCHAR(128),
  
size    VARCHAR(50),
  
maxsize VARCHAR(50))

CREATE TABLE #dir (
  
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
WHERE  s IS NULL
   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 */
 
WHILE EXISTS (SELECT * FROM   #dir)
   
BEGIN    
   
/* -- 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
       
BEGIN    
          SELECT
@dbname = NULL
         
SELECT   TOP 1  @dbname = LEFT(s,CHARINDEX('_Log_',s) - 1)
                
FROM     #dir    
                
WHERE    CHARINDEX('_Log_',s) <> 0
         
ORDER BY s
       
END
   
/* -- 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
        
BEGIN    
          SELECT   TOP
1  @s = s
         
FROM #dir
             
WHERE s LIKE @dbname + '%'
         
ORDER BY RIGHT(s,20)    
         
IF @s = @filename
           
BEGIN
              SELECT
@s = NULL
           
END
          ELSE
            BEGIN
           
/* -- move to archive */
             
SELECT @cmd = 'move ' + @SourcePath
                      
+ @s + ' ' + @archivePath + @s
             
EXEC master..xp_cmdshell  @cmd
             
DELETE #dir WHERE  s = @s
           
END
        END
 
/* -- now we can go through each file in turn for this
                               database and restore it */
     
WHILE EXISTS
       (
SELECT * FROM   #dir
               
WHERE  s LIKE @dbname + '^_%' ESCAPE '^')
       
BEGIN    
          SELECT   TOP
1  @filename = s
              
FROM #dir
              
WHERE s LIKE @dbname + '^_%' ESCAPE '^'
         
ORDER BY RIGHT(s,20)
         
IF @filename LIKE '%^_Full^_%' ESCAPE '^'
           
BEGIN
           
/* -- restore a full backup */
             
IF EXISTS (SELECT *
                        
FROM master..sysdatabases
                        
WHERE name = @dbname)
               
BEGIN
           
/* -- Drop the database before starting the restore */
                  SELECT @cmd = 'drop database ' + @dbname
                 
EXEC( @cmd)
               
END
              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)
               
BEGIN
                  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
ELSE @LogPath END
                      
+ @s + ''''
              
END

 
/*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)    
           
END    
          ELSE    
            IF
@filename LIKE '%^Log^_%' ESCAPE '^'
             
BEGIN
             
/* -- restore a log backup */
               
SELECT @cmd = 'restore log '
                                
+ @dbname
                                
+ ' from disk = '''
                                
+ @SourcePath
                                
+ @filename

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

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 www.mindsdoor.net). 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.


Poor

OK

Good

Great

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.

thanks.

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.

Best,

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?
Thanks

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?
Thanks

 
Simple-Talk Database Delivery

DLM
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
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... 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...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... 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.