I have a backup from a database taken at the end of march. I want to restore it to a new database (same server instance) and add a suffix to the db name to indicate the point in time. I have this restore command:
RESTORE DATABASE [CODS_20160331] FROM DISK = N'F:\RITM0099983 backups\CODS_Full_20160331202633.bak' WITH FILE = 1, NOUNLOAD, STATS = 5, Recovery, Move 'CODS' TO 'G:\SQL10Data\CODS_20160331.mdf', Move 'CODS_log' TO 'H:\SQL10Data\CODS_20160331_Log.ldf'
This fails with the error:
Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "H:\SQL10Data\CODS_20160331_Log.ldf" failed with the operating system error 2(The system cannot find the file specified.). Msg 3156, Level 16, State 3, Line 1 File 'CODS_log' cannot be restored to 'H:\SQL10Data\CODS_20160331_Log.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Well, the message is correct, the file doesn't exist! But that's part of what I'm doing here. Restoring to a new database name and create the files to support it.
What am I doing wrong?