sp_detach_db

Question

sp_detach_db

Darek Tomyn

SSCrazy

Points: 2551
More actions
March 3, 2004 at 9:42 am

#65999

Up until now when I have migrated a database from one server to another I have used sp_detach_db... moved the files and then used sp_attach_db on the source server. What I am wondering is, is this really necessary? That is, can I not stop SQL Server and then move the files (if there is no activity in the database)? Or does running sp_detach_db result in more stable data files?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

fhanlon SSChampion Points: 13131 More actions · Answer 1

This may be just terminology but if you stop SQL Server and move the files (As opposed to copying them) what happens when you start up SQL Server and the expected database file is missing? This is what detach and attach do. Plus you don't have to stop SQL to do it this way. A file is a file.. of course you can copy it if you want, but attach/detach tells the master database whether it should expect to see this database or not.

Francis

Michael David Right there with Babe Points: 788 More actions · Answer 2

You cannot copy/move the DB files while they are attached to SqlServer. You will get a violation error.

fhanlon SSChampion Points: 13131 More actions · Answer 3

You will get a violation only if SQL Server is up. If SQL Server is not running you can copy/move anything you want. Of course if you move (as opposed to copy) you may have problems restarting SQL Server.

Francis

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 4

Thanks for the responses. I already know that you cannot / should not copy data files while they are in use (while SQL Server was running), this is why I mentioned the part about starting SQL Server first. What I was more getting at was wondering if there were certain extra check points made while detaching a database then there is when one stops the SQL Server. Give the responses I have had so far it looks like I will change the way I do things since I always get a little nervous during a "promotion weekend" when I detach a database from its source. So, if instead I can just stop the server and then copy the files over to its new server than this will make me happy.

Thanks again.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Personal experience.

I have had problems (suspect databases) when SQL Server services were stopped and files moved and then the services restarted.

I have NEVER had problems with detaching the database(s), moving the files and attaching them.

In the first method, SQL Server expects the database files to be where they were when the services were stopped.

In the second method, SQL Server explicitly asks WHERE the files are when you try to attach them.

-SQLBill

jgebri Grasshopper Points: 11 More actions · Answer 6

Why don’t you create a script that would

1: perform a full DB backup on Server A (you will get all the committed transitions)

2: Drop the DB on Server A (if you REALLY need to)

3: Copy the Full DB Dump from Server A to Server B (with sp_cmdshell)

4: On Server B create the empty DB

5: Restore the full DB backup into the empty DB

6: Fix up your SQL user access on Server B

This way would be must safer that detaching the DB.

Yoka

Alamir SSCertifiable Points: 6085 More actions · Answer 7

yes if you want the DB to keep running .. then the best solution is to make FGull backup in this moment .. then create the same DAtabase NAme on another server .. and then restore the data on that server.

Alamir Mohamed
Alamir_mohamed@yahoo.com

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 8

I don't care if the original database is kept running or not. Also, doing a backup and then a restore (no need to create the database first) is certainly an option, but doubles the time to perform this task. I do have a window to do these types of moves, but not *that* big of a window.

Again, what I was more asking is if sp_detach_db was necessary to get a more stable data file then just stopping the server and moving the data files over. In my case, if the "move" (which is in reality a copy) does not succeed for whatever reason, I want to be able to get back to the original setup easily. Currently I prep a sp_attach_db to bring the database back.

Alamir SSCertifiable Points: 6085 More actions · Answer 9

you can write 2 stored procedure (1 for de attach and 1 for attach)

and make a Visual Basic Program that :

1- call deattach stored procedure

2- copy the file ... and chech that the operaion is complete ...

then check if files exist ... if ok go to step 3

3- call attach stored procedure

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

*Prashant Thakwani SSCommitted Points: 1767 More actions · Answer 10

I think u can Backup the entire DB and then make the Restore on the separate location.

This u can do through the script also.

Regards,

Prashant Thakwanithakwani_prashant@yahoo.co.in

hoo-t SSChampion Points: 12713 More actions · Answer 11

Dareck,

Based on everything that you have said, I would continue to use the attach/detach stored procs. Its just safer that way. Since you are simply copying the files, you could do it either way. But if anything happened, ie accidental delete, rename, move... you're covered because SQL is not expecting those files to be there.

Steve

jmcgarvey Default port Points: 1489 More actions · Answer 12

In short, don't tempt fate. You will not like the outcome. I learned this the hard way at the price of a long sleepless night. When you attempt to take a shortcut by stopping sql to move a db file the internal cleanup in sql is not performed. Meaning that will the db will give you an error on the other server when you attempt to attach it. The file is marked as invalid as far as sql is concerned.

Best practice is to detach/copy/attach. You can be comfortable that everything will work when you are finished. You only clean up will be to sync your DB users.

You are doing good now, don't change.

Kay C. Chan SSC Rookie Points: 49 More actions · Answer 13

I am new in the DBA role and I was always told to use backup/restore to move a database. I was told that detach/attach will not bring the DB users permission over. I would like to try the detach/attach way since it sound very neat. Can some one show me how to fix up the DB user permission after I attach a database to the new server??

Thanks.