Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Restoring Your Database Safely-2 : Page 2




Application Security Testing: An Integral Part of DevOps

Restoring a Full Database Backup
A full database restore can be executed by simply typing this:

restore database [db_name] from [file_name]

As an example, I've backed up the Northwind database on my machine using the following command:

backup database northwind to disk = 'c:\northwind.bak'

In order to demonstrate the power of the RESTORE command, I will drop the Northwind database by executing:

drop database Northwind Deleting database file 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.ldf'. Deleting database file 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.mdf'.

The output from the DROP DATABASE command shows that the files used by the Northwind database have been deleted too.

To restore the Northwind database from that file I can simply type:

restore database northwind from disk = 'c:\northwind.bak'

SQL Server reads all the relevant information about the files used by the database from the backup and creates those files during the restore process. You can see how the files have been recreated after the restore process by executing:

SP_HELPDB Northwind

You will see the same filenames as those previously deleted during the DROP command.

In this example, the syntax for the RESTORE command is very simple because I didn't try to change anything regarding the database during the restore process. For instance, I kept the file locations the same. However, often enough during the restore process, you might want to change some of those options. As an example, your production database might exist on the D drive of the server while the database needs to be on the C drive of your backup machine.

Changing the File Location During the Restore Process
To demonstrate this technique, I will first attempt to create a second copy of the Northwind database on my machine, called Northwindrep. I'd like to have a second copy on a different set of drives that will be used for extensive reporting purposes.

However, when I execute this:

restore database Northwindrep from disk = 'c:\northwind.bak'

I get the following errors:

Server: Msg 1834, Level 16, State 1, Line 1 The file 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.mdf' cannot be overwritten. It is being used by database 'northwind'. Server: Msg 3156, Level 16, State 1, Line 1 File 'Northwind' cannot be restored to 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.mdf'. Use WITH MOVE to identify a valid location for the file. Server: Msg 1834, Level 16, State 1, Line 1 The file 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.ldf' cannot be overwritten. It is being used by database 'northwind'. Server: Msg 3156, Level 16, State 1, Line 1 File 'Northwind_log' cannot be restored to 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.ldf'. Use WITH MOVE to identify a valid location for the file. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

In order for me to execute the restore statement, I have to specify different locations for the files because the current locations are being used by the Northwind database. To do so, I need to use the MOVE option, which uses the following syntax:

move '[logical_file_name]' to '[operating_system_file_name]'

If you aren't familiar with the concept of a logical and physical filename, look up the syntax of the CREATE DATABASE statement in Books Online. When specifying a file while creating a database, you use the syntax:

name = [logical_file_name], filename = [physical_location]


name = Northwind, filename = 'e:\mssqlserver\MSSQL$JOELAX2000_1\data\northwnd.ldf'

Because I can't remember by heart what the logical filenames are, I will first use another helpful command. Instead of RESTORE DATABASE, I type this:

restore filelistonly from disk = 'c:\northwind.bak'

This command provides a list of the logical database files and their corresponding physical locations. This allows me to find that I have two logical files, one called Northwind and the other called Northwind_log.

Using this information I can now complete the restore process by typing:

restore database Northwindrep from disk = 'c:\northwind.bak' with move 'Northwind' to 'c:\Northwindrep.mdf', move 'Northwind_log' to 'c:\Northwindrep.ldf'

Notice, however, that while I was able to restore the backup to a different database name and change the location of the files, I couldn't change the logical filename. This means that while the database name (Northwindrep) will be consistent with the physical names used, the logical filenames will still be Northwind and Northwind_log.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date