A RESTORE statement always produces a database that is identical to the one that was backed up. The database produced by the RESTORE will have the same number of files, and the files will be of the same size, as determined by the BACKUP. The backup file contains information on the name, number, size, and location of all files for the database at the time the backup was created. When you RESTORE a backup and allow SQL Server to re-create the database for you, the following occurs:
- If a file exists in the same location as in the backup, it is used and it will be identical to the file in the backup (identical file size, attributes, and so on).
- If a file exists, but at a different location than specified in the backup set, the location on the server is used (thereby implicitly doing a "MOVE").
- If a file exists on the server with no corresponding file on the backup set, it will be silently dropped.
- If a file required by the backup set does not exist on the server, it is created.
Therefore, if you want to change the location of the files when restoring the database, you have the following options:
- Create the database before performing the RESTORE (as was required with SQL Server 6.5), specifying the new location.
-or- - Use the RESTORE statement with the MOVE clause. This avoids the extra step of creating the database first. It creates the database and files in the location specified.
Note that SQL Server may require you to use the MOVE clause if restoring a file would overwrite an existing file that is in use by another database. You will receive the following messages in this case:
Server: Msg 3156, Level 16, State 2, Line 1
The file 'D:\MSSQL7\data\Filename_Data.MDF' cannot be used by RESTORE.
Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
REFERENCES
SQL Server Books Online; RESTORE (T-SQL)