How to best create a copy of a database 1

[k108]

I apologize in advance if this is an obvious question.

I need to copy or restore (haven't decided which yet) four databases from SERVER A to SERVER B.

So, my question is, should I use the Database Copy Wizard, or should I restore the databases to SERVER B using a backup?

Is there any advantage to either way?

Thanks!

 

[SQLBill]

I've never used Database Copy Wizard. I would detach the databases, copy the .mdf and .ldf files, move the copies to the new server and attach them. (Don't forget to attach the originals back).

-SQLBill

Posting advice: FAQ481-4875

 

[k108]

I don't have the option of detaching the databases, as they are part of our production server. Nor do I think this is what I want to do. I don't want to MOVE the original database. I simply want a COPY of the database on another server.

 

[DrSql]

Take a backup from A and restore server B.
You can execute from QE


Execute on Server A
--Create backup device
[tt]
--USE master
--EXEC sp_addumpdevice 'disk',
--'PUBS', 'D:/MSSQL/Backup/PUBS.bak'

--Then backup the db
Backup database PUBS
to DISK= 'D:/MSSQL/Backup/PUBS.bak'
with init, name='PUBS Full Backup', stats = 10
[/tt]

Execute on Server B
[tt]
RESTORE DATABASE TestDB
FROM DISK = '\\ServerAName\Share\PUBS.bak'
WITH MOVE 'Test_DB' TO 'c:\test\testdb.mdf',
MOVE 'TestDB_log' TO 'c:\test\testdb.ldf'
GO
[/tt]

Hope this might help you

Dr.Sql
Good Luck.

 

[k108]

Hi DrSql,

Yes, I decided that I need to do a restore. And actually your code does help me, because I was wondering how the LOG get created on the target server.

How does this work if the LOG file is in use?

Also, I don't know if I actually want to copy the existing log file over... I just want to create a new log file on the target machine, basically empty. Don't know how to do this.

 

[k108]

According to BOL, you apply the logs, not "move" them over:

=============================

USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO

 

[DrSql]

You can restore a DB only in single usert stage. IF there any connections you need to disconnect prior to restore. You dont have to copy the log files from Server A to B becasue when you back up the DB the bak file should have all the data in the log files.

Dr.Sql
Good Luck.