Be prepared for a crisis

By SQLRx Admin | SQL Administration

May 28

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.

About the Author

>