February 12, 2013 at 8:47 am
kevaburg (2/12/2013)
Although this is a very good article with regards to moving user databases
That is exactly what it is targeted at!
kevaburg (2/12/2013)
what happens if you want to move the master database somewhere else?
That warrants a whole separate article, the MS kb is at this link
Note that to change the location of the resource database still requires you to use the ALTER DATABASE ... MODIFY FILE command detailed in my article
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2013 at 1:12 pm
Question - What advantages does this method have over the detatch/reattach method of moving files around?
I've always just detatched/reattached whenever I move a database, and the time consuming part is always waiting for the file to transfer, not the detatch/reattach process, which has always been pretty fast for me. It seems like there are more ways to make a mistake using the method described in this article. Or am I missing something?
The Redneck DBA
February 13, 2013 at 9:07 am
Jason Shadonix (2/12/2013)
Question - What advantages does this method have over the detatch/reattach method of moving files around?
It's the supported way of moving database files and doesn't involve dropping the database from the system catalogs.
SP_ATTACH_DB is deprecated and will be removed in a future version of SQL Server, you should use CREATE DATABASE ... FOR ATTACH to attach database files to an instance of SQL Server. The ALTER DATABASE command is used to alter database configurations.
Jason Shadonix (2/12/2013)
I've always just detatched/reattached whenever I move a database, and the time consuming part is always waiting for the file to transfer, not the detatch/reattach process, which has always been pretty fast for me. It seems like there are more ways to make a mistake using the method described in this article. Or am I missing something?
Yes, often the file copy process can take some time. If you're smart you'll copy the files first then and only then remove the originals once the database comes online successfully using the new paths.
As long as you pay attention to what you're typing as part of the ALTER DATABASE ... MODIFY FILE command there's no reason why it should fail.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2013 at 9:25 am
It's the supported way of moving database files and doesn't involve dropping the database from the system catalogs.
SP_ATTACH_DB is deprecated and will be removed in a future version of SQL Server, you should use CREATE DATABASE ... FOR ATTACH to attach database files to an instance of SQL Server. The ALTER DATABASE command is used to alter database configurations.
I understand the sp_attach_db stored procedure is deprecated and replaced with the CREATE DATABASE ... FOR ATTACH when it comes to attaching databases. But that's just a minor syntax change in the process. That alone isn't a reason to abandon the attach/detatch method of moving/renaming files in your database. I'm wondering if there are other reasons that I'm missing here is why I asked the question. 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.
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.aspx
I'm not saying your method is "bad" by any means. I just don't see why it's markedly better than the attach/detatch method, and am wondering if I am missing something.
The Redneck DBA
February 13, 2013 at 12:56 pm
Jason Shadonix (2/13/2013)
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.
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.
Jason Shadonix (2/13/2013)
This 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 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2013 at 1:31 pm
Perry Whittle (2/13/2013)
Jason Shadonix (2/13/2013)
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.
[/Quote]
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 onlinebooks 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
February 13, 2013 at 2:01 pm
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" 😉
February 13, 2013 at 2:10 pm
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_nameON 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
February 13, 2013 at 2:11 pm
Jason, for an article on the benefits of alter database method and why it is the preferred method for SQL2005 onwards see here[/url]
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
---------------------------------------------------------------------
February 13, 2013 at 3:13 pm
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" 😉
February 13, 2013 at 3:22 pm
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
February 13, 2013 at 3:25 pm
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[/url]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
February 13, 2013 at 4:01 pm
pretty sure nothing in msdb is affected by a detach, it would be too time consuming to remove backup history for example.
---------------------------------------------------------------------
February 13, 2013 at 4:21 pm
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" 😉
February 14, 2013 at 3:48 am
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)
---------------------------------------------------------------------
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply