The information in this article is meant to explain the proper usage of the WITH MOVE option with the RESTORE Transact-SQL statement.
- 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.
- Create the database before performing the RESTORE (as was required with SQL Server 6.5), specifying the new location.
- 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.
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)
Article ID: 221465 - Last Review: Sep 12, 2008 - Revision: 1