Sql-server – How to Make a Copy of a database

sql server

I have an sql server database and I want to make a mirror copy of it on my local computer. I tried to use the copy database wizard but it fails. It seems me to look at the event log for the reason but all it says in the event log is

Package "CDW_4" failed.

so I have no clue why it is failing.

Is there another way to do this?

Best Answer

I am assuming that by mirror copy ==> you mean at the point when the database full backup was taken.

If that is not the case, then for near to realtime copy of your database, I would suggest transactional replication.

Also, if you are using enterprise edition, then you can leverage the benefits of database SNAPSHOTS.

Step 1: Make a FULL database backup

backup database database_name to disk = 'path to backup drive' with copy_only, stats =10
-- ,compression (sql server 2008 R2 and up, compression is available in standard edition as well).

e.g. path to backup drive = D:\backups\db_name_FULL.bak

Step 2: Restore the backup taken in step 1 with move.

restore database database_name_mirror
from disk = 'path to backup drive'
with stats =10, recovery,
move 'Logical_Data_FileName' to 'Physical data file location.mdf',
move 'Logical_Log_FileName' to 'Physical log file location.ldf'

Logical file name can be found using sp_helpdb database_name or restore filelistonly from disk = D:\backups\db_name_FULL.bak

e.g. 

restore database database_name_mirror
from disk='D:\backups\db_name_FULL.bak'
WITH move 'database_name_Data' to  'D:\Data\database_name_mirror_Data.mdf',
move 'database_name_log' to  'L:\logs\database_name_mirror_log.ldf';
GO
Related Question