SQL server copying database via files

B

Bohica69

Gawd
Joined
Jul 12, 2005
Messages
676
We've got a SQL server db that we'd like to make a copy of, is it possible to just detach the original database, copy the files (mdf and ldf files) reattach the database the original files, then create a new database pointing the file copies? It seems that would be faster then using the backup/restore method.

Any help would be appreciated.

Thanks.
 
What version of SQL Server?
Is the source version of SQL Server running the same version as the destination server?
How many MB/GB of a database is it?
Any triggers, stored procs, views, DTS/SSIS packages, or reports?
 
SQL Server 2000
The databases are a couple of Gigs
There are triggers and sprocs, but no DTS packages (that I need to worry about)

What I want to do, is basically make a copy of the db on the same server box under a different database name so I can run some data clean up scripts on the copy of the database. There's are some time constraints on this process, so if I can save some time on the backup / restore process, that's what I'm looking for.

Thanks.
 
Yes, it's possible to detach, copy, and reattach the copy and the original db file.
 
So the steps are, detach. Copy the files, rename the files, then reattach to the new files? Is this faster than the backup restore process?
 
Why would you copy and rename?

1) Detach A.
2) Copy A to A_Copy. Remember to copy all the files, including the log.
3) Attach A again
4) Attach A_Copy
5) Done
 
Ok, thanks Mike, I would SWEAR I did it that way once and couldn't get it to work. I'll give it another try.

Am I correct that this is faster than a backup / restore process?

Thanks.
 
It's faster, yes. Backup up wites a file. Then restoring writes the file again. Copying just copies once. Attaching and detaching don't copy.

The disadvantage is that it requires downtime; you can't copy without detaching, and once detached the database is not accessible until it is attached again.
 
You must log in or register to reply here.
Back
Top