Friday, April 15, 2016

Replace or Restore MS SQLSERVER database from backup

Sometimes you want to revert a database from an earlier backup.

Here is a quick way without using the gui.

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

If you get an error stating the database is busy. For example 'Exclusive access could not be obtained because the database is in use', try the following.

EXEC sp_who2

Look for the database in the list. It's possible that a connection was not terminated. If you find any connections to the database, run


where <SPID> is the SPID for the sessions that are connected to the database.

Try the command again after all connections to the database are removed.

see the example below

Processed 528288 pages for database 'AdventureWorks', file 'AdventureWorks' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks_log' on file 1.
RESTORE DATABASE successfully processed 528290 pages in 9.067 seconds (455.195 MB/sec).