Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Moving Database Files in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 1:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:22 AM
Points: 1,523, Visits: 2,200
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.


Jason Shadonix
MCTS, SQL 2005
Post #1419734
Posted Wednesday, February 13, 2013 2:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 6,351, Visits: 13,681
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"
Post #1419741
Posted Wednesday, February 13, 2013 2:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:22 AM
Points: 1,523, Visits: 2,200
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.


Jason Shadonix
MCTS, SQL 2005
Post #1419744
Posted Wednesday, February 13, 2013 2:11 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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


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

Post #1419745
Posted Wednesday, February 13, 2013 3:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 6,351, Visits: 13,681
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"
Post #1419755
Posted Wednesday, February 13, 2013 3:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:22 AM
Points: 1,523, Visits: 2,200
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.



Jason Shadonix
MCTS, SQL 2005
Post #1419757
Posted Wednesday, February 13, 2013 3:25 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:22 AM
Points: 1,523, Visits: 2,200
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.


Jason Shadonix
MCTS, SQL 2005
Post #1419758
Posted Wednesday, February 13, 2013 4:01 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
pretty sure nothing in msdb is affected by a detach, it would be too time consuming to remove backup history for example.

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

Post #1419766
Posted Wednesday, February 13, 2013 4:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 6,351, Visits: 13,681
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"
Post #1419770
Posted Thursday, February 14, 2013 3:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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)

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

Post #1419934
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse