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?


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.

  • This would leave out all other pertinent parts of the database such as triggers, stored procedures, security, etc. I really need everything. Sep 2 '14 at 16:11
  • Can you script the database creation (with triggers, etc) from the live and then bring the data across from the mirrored copy?
    – DerekCate
    Sep 2 '14 at 16:14
  • Is there an easy way to script the entire creation without having to touch each part of the db? It is pretty huge. Thousands of tables, triggers, stored procedures, etc. Sep 2 '14 at 16:19
  • In SQL Server Management Studio, if you right click on the database, Tasks, Generate Scripts, you should be able to generate a script which does all the tables, view, triggers, etc, but leaves out the data.
    – DerekCate
    Sep 2 '14 at 16:50

I agree this should be moved to the dba stack exchange, though since I answer there as well I will post it here should it be migrated.

The only way to access a mirrored database from the mirror server is through a database snapshot, which will require enterprise edition of SQL Server. It will also be READ ONLY, so this may not work for what you want.

The other alternative is to take the backups of the primary (these, I would hope, should be available) and restore them for your use somewhere else up to the time you need.

If using already taken backups is not possible, ask for a copy_only backup to be taken on the primary and given to you.

  • The problem in my circumstances is that the mirrors are at different locations. It would be awfully inefficient to copy all of the data across a slow link again when it is already there in the mirrored copy. This would use bandwidth needed for production as well as take a considerable amount of time. Since the snapshot of the mirror is read only it would be nice to somehow be able to make a copy (read) of the data and save it locally to where the mirror is so I can then read and write to it for testing and recovery. Sep 2 '14 at 15:35
  • 1
    Unless you want to fail the mirror over and have the primary be local, and then at some point fail it back... you're not really going to get what you want. At some point you'll need to be flexible. Sep 2 '14 at 18:44

You could shut down the mirror SQL Server instance using SQL Server Configuration Manager, make a copy of the database files themselves, restart the instance, and attach the files using a different database name via the FOR ATTACH or FOR ATTACH_REBUILD_LOG options.

ON (FILENAME = 'C:\somedata.mdf')
LOG ON (FILENAME = 'C:\somelog.ldf')

Be aware that using the FOR ATTACH_REBUILD_LOG option breaks the log backup chain; however in this instance since your database is a mirror copy on SQL Server 2005 this is not an issue.

Shutting down the mirror server instance will cause the primary server to stop sending transactions to it; this may cause transaction log growth on the primary server. In order to mitigate this, you want to make sure you create a copy of the mirror database files as quickly as possible, and get the mirror up-and-running as quickly as possible.

See http://msdn.microsoft.com/en-us/library/ms176061%28v=sql.90%29.aspx for details on the CREATE DATABASE ... FOR ATTACH commands.

Another option, perhaps too obvious: You could put a backup of the main database on disk and FedEx it to the mirror location.

  • Max I dont quite agree with your approach apart from sending backup using fedex. Here is reason not to use attach rebuild log sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/… plus its always not reliable and why loose the log content
    – Shanky
    Sep 2 '14 at 22:37
  • Generally speaking, I agree its not a good idea to use that option; however in the case where we have a controlled environment (the server has had a controlled shutdown) it is acceptable to rebuild the log. That is precisely what that option is designed to address. The OP is clearly not intending on using this database as a live copy, and indeed would be happy with just a snapshot if that worked (it doesn't).
    – Hannah Vernon
    Sep 2 '14 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.