Where the database data and log files will be restored?

Generally, we could use Restore Database option in SSMS GUI or using RESTORE DATABASE T-SQL command when we want to restore a database from a database backup file (.bak). However, the destination database file (data and log) locations will vary depending on many factors.

Here are details:

1. If we restore the database via SSMS GUI ‘Restore Database…’ option, the database files will be restored to the original file locations if corresponding locations exists on the server. Otherwise, the database files will be restored to the default database file locations of the SQL Server instance. You could find the database file locations of SQL Server in the system registry, for more information see: File Locations for Default and Named Instances of SQL Server.

2. If we restore the database via Restore Database T-SQL command, the database files will be restored to the original file locations no matter these file locations exists or not on the server. Therefore, the database restore options may be failed if same file locations do not exist.

In either ways, we could change the database file locations as required. If using SSMS GUI option, we can change the database file locations in the Options page of Restore Database – <db_name> dialog window. If using T-SQL command, we can use WITH MOVE clause instead.

If you are interested in what are the database file locations stored in the backup file, you could run RESTORE FILELISTONLY T-SQL command to get each database original file location.

Here is an example:

RESTORE FILELISTONLY
FROM DISK = ‘D:\Tests\BackupTest2.bak’;
GO

and I got the following result:

image

HTH

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

2 Responses to Where the database data and log files will be restored?

  1. Pingback: How to restore a database when you have a SQL Server backup file? « Alex Feng's Weblog

  2. D. Grove says:

    This is great until you have multiple data files… several ndf files…How do you handle that when automating a restore?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s