sqlrx logo
SQLRx® Tip of the Month
 

May 2013
______________________________________________________________________________
SQL 2008    SQL Server Administration:  Restore a database from one file or multiple files. Use these commands to restore your databases. Practicing restoring databases proves that the backup files are usable and will polish up your skills so that you are ready to restore in the event of a crisis. If you have to restore database files to different drives than are available on the production server, create a script that will be easier to use than restoring using the GUI and navigating to the new file locations. Don't forget that databases from newer version of SQL cannot be restored to an older version. And of course, practice restores in a dev, test or some kind of secondary environment away from your production SQL instance.

 

Here are some basic examples of restore statements. More can be found at http://msdn.microsoft.com/en-us/library/ms186858(v=sql.110).aspx

_________________________________________________________________________________________

 

--

-- Restore a database from a single backup file

--

USE master

GO

RESTORE DATABASE AdventureWorks2012

FROM DISK = N'D:\SQLBackups\AdventureWorks2012_Full.bak'

WITH RECOVERY

GO

 

--

-- Restore a database from a single database backup file

-- and a transaction log backup file

--

USE master

GO

RESTORE DATABASE AdventureWorks2012

FROM DISK = N'D:\SQLBackups\AdventureWorks2012_Full.bak'

WITH NORECOVERY -- NORECOVERY allows more backup files to be restored

GO

RESTORE LOG AdventureWorks2012

FROM DISK = N'D:\SQLBackups\AdventureWorks2012_tlog.trn'

WITH RECOVERY

GO

 

--

-- Restore a database from multiple backup files

--

USE master

GO

RESTORE DATABASE AdventureWorks2012

FROM DISK = N'D:\SQLBackups\AdventureWorks2012_Full_1.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_2.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_3.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_4.bak'

WITH REPLACE, -- REPLACE WILL OVERWRITE EXISTING FILES!! USE VERY CAREFULLY!!!

RECOVERY

GO

 

--

-- Restore a database from multiple backup files

-- and move the database and log files to new drives

--

USE master

GO

RESTORE DATABASE AdventureWorks2012

FROM DISK = N'D:\SQLBackups\AdventureWorks2012_Full_1.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_2.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_3.bak',

DISK = N'D:\SQLBackups\AdventureWorks2012_Full_4.bak'

WITH MOVE N'AdventureWorks2012' TO N'H:\MSSQL\AdventureWorks2012.mdf',

MOVE N'AdventureWorks2012_log' TO N'L:\MSSQL\AdventureWorks2012_log.ldf',

RECOVERY

GO

 

 

Restore WITH RECOVERY to make the database ready to use. Restore WITH NORECOVERY if you want to restore more backups. Use REPLACE only when you are certain that you want to overwrite the existing files so be careful with it.

 ______________________________________________________________________________
As always, don't forget to regularly monitor the 11 VitalSigns  used by SQLRx to debug performance bottlenecks.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at lorib@isi85.com.


Sincerely,

 

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | lorib@isi85.com 

 

 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit

 
25 Years of Credibility & Trust

 

Join our email list          Check out our events          SQLRx Downloads