CODEWITHSUNDEEP
t-sqlsql-server-2005
BAD_SEED
BAD_SEED

Reputation: 5056

Create new database from backup changing mdf and ldf path

I have a series of manual job:

  1. create new database specifying alternative .mdf and .ldf file locations.
  2. restore a previous backup in the new database using alternative path defined above and overwriting everything

How can I do this? I've tried:

RESTORE DATABASE newdb 
FROM DISK = 'F:\Shared\newdb.bak'
WITH MOVE 'newdb' TO 'G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\newdb.mdf',  
     MOVE 'newdb_log' TO 'G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\newdb_log.ldf',
replace

but I got:

Logical file 'newdb' is not part of database 'newdb'.
Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.

And if I use FILELISTONLY I got another type of error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Upvotes: 0

Views: 1173

Answers (1)

marc_s
marc_s

Reputation: 754220

You need to use find out the logical names using:

RESTORE FILELISTONLY 
FROM DISK = 'F:\Shared\newdb.bak'

This gives you all the logical files contained in the backup, and their logical names.

With that information, you should be able to build up your RESTORE DATABASE command properly.

See the relevant MSDN SQL Server Books Online documentation for more details.

Upvotes: 1

Related Questions