How do you backup and restore a database as a copy on the same server?

Question

I have a SQL2005 Express database that I would like to create a copy of on the same instance. How do you go about doing this with a script?

I already have a script for generating the backup, but the restore is failing...

THE ERROR:

Msg 3234, Level 16, State 2, Line 2 Logical file 'MyDB_data' is not part of database 'MyDB_Test'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

THE RESOLUTION:

RESTORE DATABASE [MyDB_Test]
FROM DISK = 'C:\temp\SQL\MyDB.bak'
WITH
MOVE 'MyDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDB_Test.mdf'
, MOVE 'MyDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDB_Test_log.ldf'
, REPLACE;

THE REASON:
I did not identify the logical path correctly in my first attempt.

Answer 1

RESTORE FILELISTONLY is an informational command and is not required to perform a restore. A user can use this to figure out what the logical names are for the data files, that can be used with the MOVE commands to restore the database to a new location.

As suggested by the error message you need to use RESTORE FILELISTONLY to see what the logical names for the database are. Your restore command has these wrong.

Here is a working example of what you need to do:

--backup the database
backup database test1 to disk='c:\test1_full.bak'

-- use the filelistonly command to work out  what the logical names 
-- are to use in the MOVE commands.  the logical name needs to
-- stay the same, the physical name can change
restore filelistonly from disk='c:\test1_full.bak'
 --------------------------------------------------
|  LogicalName  |           PhysicalName           |
 --------------------------------------------------
| test1         | C:\mssql\data\test1.mdf          |
| test1_log     | C:\mssql\data\test1_log.ldf      |
 -------------------------------------------------

restore database test2 from disk='c:\test1_full.bak'
with move 'test1' to 'C:\mssql\data\test2.mdf',
move 'test1_log' to 'C:\mssql\data\test2.ldf'

Answer 2

How to: Restore a Database to a New Location and Name (Transact-SQL)