Learn more about SQL Server tools

   
   























Latest from MSSQLTips















How to restore a SQL Server backup

(RESTORE DATABASE)


Overview

The RESTORE DATABASE option allows you to restore either a full, differential, file or filegroup backup.

Explanation

When restoring a database will need exclusive access to the database, which means no other user connections can be using the database.

The RESTORE DATABASE option can be done using either T-SQL or using SQL Server Management Studio.


T-SQL

Restore a full backup
This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup. The original location can be checked by using RESTORE FILELISTONLY.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

Restore a differential backup
To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore using a backup file that has multiple backups
Let's say we use the same backup file, AdventureWorks.BAK, to write our full backup and our differential backup. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that in position 1 we have a full backup and in position 2 we have a differential backup. The restore commands would be.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
GO






 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.