Restoring a database to a new name

SQL Server Administration
,
1

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?

2

Forgot to mention, If I omit the MOVE for the log file I get a different error message:

Msg 1834, Level 16, State 1, Line 1
The file 'H:\SQL10Log\CODS_1.ldf' cannot be overwritten.  It is being used by database 'CODS'.
Msg 3156, Level 16, State 4, Line 1
File 'CODS_log' cannot be restored to 'H:\SQL10Log\CODS_1.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.
3

Is there a H:\SQL10Data\ folder? In your second post, the error message is about overwriting H:\SQL10Log. So should it be
Move 'CODS_log' TO 'H:\SQL10Log\CODS_20160331_Log.ldf'

4

That was it. Copy and paste got me again! The move for the log was to the wrong folder, as you guessed

5

May be the location of both .mdf and .ldf are not same see the Joe answer here: http://serverfault.com/questions/179751/restore-database-with-override-sql-server