Article ID: 221465 - View products that this article applies to.
This article was previously published under Q221465
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.
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:
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.
REFERENCESSQL Server Books Online; RESTORE (T-SQL)