Stop Services and Copy Database Files verses Detach/Copy/Attach

Question

Stop Services and Copy Database Files verses Detach/Copy/Attach

HookSQLDBA7

SSChampion

Points: 10709
More actions
May 24, 2010 at 3:42 pm

#221900

We are running SQL Server 2005 64-Bit (SP3) on Windows Server 2003 64-Bit. Would stopping the sql services, copying the database files (.mdf) to another server and then attaching the database files work similar to a true Detach/Copy/Attach or backup/restore?
Just curious. Just curious if this is another way to take a quick spurr of the moment backup.
Thanks in advance, Kevin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

I'd hardly call stopping a production server a "spur of the moment" thing, but it would work. But how do the users feel about the database going off line for a while.

Also taking the db off line losses some database settings, like db-ownership chaining (if you use this).

Why not just take a backup? I have a folder with a whole bunch of scripts I just open and adapt to the situation. Or typing BACKUP DATABASE [dbname] to DISK = 'g:\.....' WITH stats = 5;

doesn't take that long, and the DB will still be available.

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 2

Thanks for the response.

FYI, I not doing this on a production server. I am just curious if it would work or work the same as a true Detach/Copy/Attach or backup/restore?

Thanks again, Kevin

Leo.Miller SSChampion Points: 13051 More actions · Answer 3

You would need to copy the ldf file as well to do the job properly.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

In theory it should work. Once you move the files though, the server may still show those databases in SSMS and try to bring them online which may cause confusion and headaches down the road. I would just detach, copy then reattach.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events