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.