# Copy database with Sql Server 2005

My situation is as follows: I have a production Sql Server 2005 database. Once a day I want to duplicate this database to another failover database which is on the same server instance. I don't want these databases to be synced more than once a day (no mirroring, for example). I know I can do this with backup/restore but was wondering if there is a one-step solution to doing this.

Anyone know a good way to do this without backup/restore?

• So, what would happen to the "synched" database after changes have been made and it comes the point to resynch? Would you want to just wipe everything out and re-synch? – Anon246 Aug 20 '09 at 0:24

Why do you want to avoid backup/restore?

You could script the backup and restore commands and create them as a SQL job, so that it runs automatically. Will anything be connecting to the copy database during normal operations? (because that may cause problems with the restore)

• I want to avoid backup/restore for the same reason that when I want to copy a file one place to another I don't want to deal with an "in-between" file location...it seems like unnecessary work! Another related reason is that doing a backup/restore requires permissions on the disk which go beyond the individual database permissions of the involved databases. I don't want the user credentials performing this action having read and write permissions to the disk if it is unnecessary. – Mike Gates Aug 19 '09 at 22:50
• To answer your second question, there will be nothing connected to the databases when this copy is performed. – Mike Gates Aug 19 '09 at 22:51
• The users don't have to have permissions on the disk for backup/restore - only the database engine needs such permissions - you can grant users access to perform the backups, or better yet, schedule the whole thing in Agent as codeulike suggests – Mike DeFehr Aug 19 '09 at 22:57
• Hmmm...I didn't know that. I was only aware that to do a backup/restore it was not enough to be DbOwner on both databases. – Mike Gates Aug 19 '09 at 23:17
• Backup is your friend, dont be scared of it! It may seem unneccessary to have an extra backup file involved, but the SQL engine does lots of clever stuff during backup to make sure the backup is consistent. Hence updates can continue to be made to the database while it is being backed up. A simple file system copy/paste (if such a feature existed for live sql databases) would not offer that sort of protection. Detach, copy, re-attach would eliminate the backup file from the equation, but the source database is unusable while detached. – codeulike Aug 19 '09 at 23:34

There are two ways to copy a database wholesale: backup/restore and detach/attach - neither of which is a one-step process, and backup/restore would definitely be preferred given the scenario you describe.

If the "failover" database needs to be kept online while it is being updated, you're pretty much stuck with replication. However, since the databases are on the same instance, you can just use cross-database queries to move data.

I hate to go outside the bounds of the question, but a "failover" database on the same instance as production does not provide much in the way of fault tolerance. What kind of failures are you hoping to protect against?

Edit: What you might be looking for, then (if you're on enterprise edition 2005+), is database snapshots as JMusgrove suggests. You can take a snapshot at any point in time, then revert the database to that snapshot if it becomes necessary. The snapshot is only read-only until and unless you revert to it. See this MSDN article on Reverting to a Database Snapshot. I would suggest that it's between snapshots and backup/restore.

• Its kind of complicated :) The application using the database is also using a filesystem storage which has to be synced with the database state. If the filesystem gets corrupted, the database is useless. In this case, I need to roll back to a database/filesystem state which is valid. Fault tolerance of the database server instance itself is handled seperately. – Mike Gates Aug 19 '09 at 23:14

Like codeulike suggests, what's wrong with backup and restore. It sounds like the closest semantic fit for what you're trying to do.

Pretty much any action you can do in SQL Server Management Studio has the option to generate a script to perform the action, instead of actually performing the action there and then.

Look for the Script button at the top of each pop-up window. The rest is easy - just craft a script using the building blocks generated for you by SSMS and create an SQL Server job to automate.

• I know I can do this. I just wanted a simple "COPY DATABASE DB_A, DB_B" command I could use without having to worry about all the options that come with a backup/restore. – Mike Gates Aug 19 '09 at 23:18

Is there a need for the resultant copy to be modified in any way, or will a read-only copy suffice? If the latter, you can use a database snapshot to generate your "copy" of the database - and this pretty much is a one step process as outlined in this Technet article

Example:

CREATE DATABASE myCopy ON (
NAME = originalLogicalName,
FILENAME = 'path\to\new\snapshotfile'
) AS SNAPSHOT OF myOriginal


If the former, then one step approach is a little trickier. For simple databases without stored procedures, complex relationships, etc, you could create an SSIS package (DTS job) to copy the data from one database to another - but that can get awfully messy pretty quickly.

• The new database cannot be readonly. It has to function in the same way as the original database. – Mike Gates Aug 19 '09 at 23:15

One way Snapshot replication can be setup. It would go from source to destination, once as day when you schedule it Once it's on the destination server, the destination will functional exactly like the source. However any changes you make to the destination will be lost when the next replication happens since at the next scheduled time the source will overwrite the destination.

So if your destination is a copy of the source so that your developers can hack away at a database with real data in it and it doesn't matter if it gets overwritten when the next copy happens, then this would work for you.

Why not setup snapshot replication? Make the production DB the publication, and the backup the subscription? Set the replication to occur every 24 hrs, and you are set.

That replicated DB will also have all the properties and permissions of the prod DB, so it could be attached as a prod with no issue.