Article ID: 221465
Article Last Modified on 10/28/2003
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
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:
REFERENCES
SQL Server Books Online; RESTORE (T-SQL)
Additional query words: tsql transql transsql err msg error message back up backed dump dumped
Keywords: kbinfo kbbackup KB221465
