SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Database Files in SQL Server


Moving Database Files in SQL Server

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2500 Visits: 2610
Perry Whittle (2/13/2013)
Jason Shadonix (2/13/2013)
But that's just a minor syntax change in the process.

You're joking right? Create and alter database have been enhanced extensively to include extra commands (file management, mirroring, etc). Previously, under 2000 detach\attach was the only way to move files around.

No, I was not joking. The fact that you have to type out "CREATE DATABASE ... FOR ATTACH" instead of sp_attach_db to attach a database is a minor syntax change which alone isn't a reason to scrap the Detatch/Attach approach.

Jason Shadonix (2/13/2013)
Between the two methods (which both accomplish the same thing as far as I can tell) - both will have essentially the same amount of down time as near as I can tell.

What disadvantages are there in dropping a database from the system catalogs and adding them again for a detatch/attach? All of your security/permisisons should be unchanges as long as you are keeping the database on the same server

Mirrored, snapshotted and replicated databases spring to mind here, they cant just be detached.


Ah ha! Finally something you mention that actually explains some cases where you would have an advantage by not detaching/attaching a database. That's all I was looking for. So if you are not dealing with a replicated database, it probably doesn't matter that much I guess. (But I don't think you can offline a database participating in mirroring or with snapshots can you?)

Jason Shadonix (2/13/2013)
Also, when you say "it's the supported way of moving database files"... Are you saying detatch/attach is not supported? I don't see anything to indicate that in MSDN with some quck searches: http://msdn.microsoft.com/en-us/library/ms190794.aspxThis below from MS books online

books online
However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see Move User Databases.


You may want to read this and this.

Phone MS support and tell them you're having trouble attaching a db using SP_ATTACH_DB, i guarantee I know their response ;-)


I never said I was still using sp_attach_db instead of CREATE DATABASE ... FOR ATTACH syntax. I was simply trying to get at what advantages the described method has over the Detatch/Attach method. No need to get snarky about it...I'm simply trying to learn what the differences are, not trying to question or be negative about your method.

The Redneck DBA
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19726 Visits: 17239
I'm not being snarky that comment is a subtle hint.

The old
EXEC sp_attach_db @dbname = N'AdventureWorks2012', @filename1 = 
N'C:\Somepath\AdventureWorks2012_Data.mdf', @filename2 =
N'C:\Somepath\AdventureWorks2012_log.ldf';



The new
CREATE DATABASE database_name 
ON PRIMARY (name=mydb_data, filename='d:\somepath\mydbdata.mdf')
LOG ON (name=mydb_log, filename='e:\somepath\mydblog.ldf')
FOR ATTACH



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2500 Visits: 2610
Perry Whittle (2/13/2013)
I'm not being snarky that comment is a subtle hint.

The old
EXEC sp_attach_db @dbname = N'AdventureWorks2012', @filename1 = 
N'C:\Somepath\AdventureWorks2012_Data.mdf', @filename2 =
N'C:\Somepath\AdventureWorks2012_log.ldf';



The new
CREATE DATABASE database_name 
ON PRIMARY (name=mydb_data, filename='d:\somepath\mydbdata.mdf')
LOG ON (name=mydb_log, filename='e:\somepath\mydblog.ldf')
FOR ATTACH



Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that. (for example: sp_adduser is depricated, and you now have to use CREATE USER...FOR LOGIN. But you don't go around telling people they shouldn't create users anymore just because sp_adduser is depricated.)

All I was asking what the advantage to the method described in the article was over the detatch/attach method. You (sorta) listed some when you listed some (partially incorrect) cases where you couldn't detatch a database but could take it offline. That's the kind of thing I was asking.

The Redneck DBA
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10238 Visits: 13687
Jason, for an article on the benefits of alter database method and why it is the preferred method for SQL2005 onwards see here

I would add another reason, with detach\attach the owner of the database can be changed, using alter database that will not happen.

In a nutshell any metadata about a database held in master is lost when it is detached, but not when alter database is used

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19726 Visits: 17239
Jason Shadonix (2/13/2013)
Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that.

You're the one who keeps zeroing in on syntax not me, you're missing the whole point of the article with your blind devotion to an ancient stored procedure. You dont have to detach the db or stop the sql server service to move the data files as i have seen suggested many times before on the forums.


You cant just detach a mirrored or replicated db and as already mentioned or linked things like service broker, owner and backup history meta are lost.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2500 Visits: 2610
Perry Whittle (2/13/2013)
Jason Shadonix (2/13/2013)
Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that.

You're the one who keeps zeroing in on syntax not me, you're missing the whole point of the article with your blind devotion to an ancient stored procedure. You dont have to detach the db or stop the sql server service to move the data files as i have seen suggested many times before on the forums.


You cant just detach a mirrored or replicated db and as already mentioned or linked things like service broker, owner and backup history meta are lost.


You're missing the point of my question...I was trying to get you to tell me something besides the syntax change as a reason for using your method. You did (finally). So I'm good. Not sure why you think I'm "blindly devoted" to that stored procedure. I don't use it anymore and haven't for years. All I did was point out that the fact that we can't use it anymore was NOT an argument for changing methodology. The other points you later made are good points though, so I did get something out of it.

The Redneck DBA
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2500 Visits: 2610
george sibbald (2/13/2013)
Jason, for an article on the benefits of alter database method and why it is the preferred method for SQL2005 onwards see here

I would add another reason, with detach\attach the owner of the database can be changed, using alter database that will not happen.

In a nutshell any metadata about a database held in master is lost when it is detached, but not when alter database is used


Thanks for the good points...hadn't thought of metadata getting lost. I bet a few things in msdb get nuked too now that I think about it.

The Redneck DBA
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10238 Visits: 13687
pretty sure nothing in msdb is affected by a detach, it would be too time consuming to remove backup history for example.

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19726 Visits: 17239
No but since the database id may change history would be lost here

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10238 Visits: 13687
another pro alter database argument, if the dbid were to change that would affect the default database setting for logins (presuming the default was the user database)

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search