SQL Server 2005 – How to Make a Copy from a Mirror Copy

mirroringsql serversql-server-2005

I have a SQL mirror database in Microsoft SQL Server 2005. I would like to get a copy of it as a new database so I have the ability to read, write, and backup the database.

I cannot break the mirror and ALTER it to become a live database. This is a business requirement that we always have a copy on the other server.

I need a method to pull the data and all other parts of the database (triggers, stored procedures, etc) out of it into another database without breaking the mirror. I have tried SQL Server backups, which do not work on mirrors or snapshots of mirrors, and running through the copy database wizard which also did not work.

Is there any way to get a copy from the mirror database?

Best Answer

You might try something like the following, replacing the db and table names ...

into desired_copy_db_name..tablename(col1, col2, ...)
select col1, col2, ...
from live_mirror_db_name..tablename

This would be after the structure of the copy_db had been created.

Related Solutions

Sql-server – 2 databases in sql server 2005 “Production Server”

Having a test database on a production instance sounds like a very bad idea to me, even without taking performance into account.

However, a second database on the instance does not consume resources per-se. It's the user or application activity that drives load to the instance. Obviously, you will need storage to accomodate the data and log files and that could degrade the NTFS performance if the free space on the volumes goes lower than 20%.

Other than that, buffer consumption and CPU/disk/network time depend on activity, not on the database existance alone.

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

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

Best Answer

Related Solutions

Sql-server – 2 databases in sql server 2005 “Production Server”

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

Related Question