INF: Using the WITH MOVE Option with the RESTORE Statement (221465)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q221465

SUMMARY

SQL Server 6.5 used the DUMP and LOAD statements to make a backup copy of a database and subsequently reload it. In SQL Server 7.0 and 2000, this terminology has changed to BACKUP and RESTORE.

The information in this article is meant to explain the proper usage of the WITH MOVE option with the RESTORE Transact-SQL statement.

MORE INFORMATION

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)
Modification Type:MajorLast Reviewed:10/28/2003
Keywords:kbBackup kbinfo KB221465 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.