Wednesday, March 7, 2012

COPYING a DB from one server to another

I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.

Attach/Detach is out since I can only move not copy a DB using this method.
export/import, bcp and DTS take too long and are not indented for this anyways.

so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.

1. Take DB1 that's running on server1 OFFLINE
2. Manually copy all datafiles and logfiles from Server1 to server2.
3. Attach DB1 on server2.

It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...

Is this supported?
Any chance of corruption on either of the servers?
If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?

PS: Are there any other ways to do this?

Thanks so much.Just detach DB copy ( COPY do not MOVE) log & data to new server.
Attach to old server.
Attach to new server.

I have just stopped the server and copied the files, but I am afraid that sometimes it may not stop the DBs as nicely as if detaching them.

Tim S|||thanks. That makes sense.|||I don't think these methods are currently supported by MS. But if db is cleanly closed (i.e. taken offline or server orderly stopped), db files copied and attached to another server, according to several books I've read (for example Inside SQL Server) everything should be OK. I've done that many times myself without slightest problems. mojza|||Detaching is preferred simply because taking a db off-line already prevents any access to it, but leads to issues in other activities. Example could be you transaction log dumps based on maintenance plans, which will start failing if "All user databases" option is selected. Contrary, if you detach it, it gets removed from sysdatabases and everything else goes well.

EDITED: Why are you saying that these methods are not supported by MS? Which book says that?|||Kalen Delaney-Inside SQL Server 2000, in section on attaching, detaching dbs. I haven't got it here, so I cannot tell you exact page number. It was something like you're not supposed to do that but it works fine anyway. mojza

No comments:

Post a Comment