copy database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

copy database

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-24 : 23:00:43

I want to copy a database and paste to same server. I coded this T-SQL, but it failed.

How to work out ? thanks.



create database TestDB;

use TestDB;
create table test(age int)

backup database TestDB to disk='C:\mssql2008\backup\TestDB.bak' with INIT

RESTORE DATABASE TestDB_1
   FROM disk='C:\mssql2008\backup\TestDB.bak'
   WITH NORECOVERY, 
      MOVE 'TestDB_Data' TO 'C:\mssql2008\backup\TestDB_1.mdf'

The error message is


Logical file 'TestDB_Data' is not part of database 'TestDB_1'. Use RESTORE FILELISTONLY to list the logical file names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 04:26:07

try this and see what is logical file name used by db

EXEC sp_helpfile

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-25 : 04:44:52

The other way to do this is to look inside the backup file

Restore Filelistonly from disk='C:\mssql2008\backup\TestDB.bak'

Check out for LogicalName column

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-25 : 05:11:31

RESTORE DATABASE TestDB_1
FROM disk='C:\mssql2008\backup\TestDB.bak'
WITH NORECOVERY,
MOVE N'TestDB' TO 'C:\mssql2008\backup\TestDB_1.mdf',
MOVE N'TestDB_log' TO 'C:\mssql2008\backup\TestDB_11.ldf'

--------------------------
http://connectsql.com/

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-25 : 23:20:10

lionofdezert solution works for me.

I add RESTORE DATABASE TestDB_1 WITH RECOVERY, it will go to online status.

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources