Click here to monitor SSC
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
Yulingxu
Yulingxu
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
Thanks Perry for this article.

I see MS SQL Server is more like Oracle Database to handle those physical files.

Thanks
Yuling


Yuling luke XU
sismb8
sismb8
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 31
Nice article. Thanks! Introduction of the move file technique has been a welcome advance in SQL Server. In the bad old days (SQLServer 6.5/7.0) I used to add a file to a filegroup on the new disk location, set the quota on the other files to zero, let the DB daemon siphon all the data to the new file and then drop the old ones when empty. Clunky but it worked. The move is miles better.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
Yulingxu (2/11/2013)
Thanks Perry for this article.

I see MS SQL Server is more like Oracle Database to handle those physical files.

Thanks
Yuling

As a SQL Server\Oracle DBA i disagree. Oracle has no concept of filegroups and from what i have learned they are totally different

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
sismb8 (2/12/2013)
Nice article. Thanks! Introduction of the move file technique has been a welcome advance in SQL Server. In the bad old days (SQLServer 6.5/7.0) I used to add a file to a filegroup on the new disk location, set the quota on the other files to zero, let the DB daemon siphon all the data to the new file and then drop the old ones when empty. Clunky but it worked. The move is miles better.


that is not what move file does!!
You're referring to emptying data from a file, correct?

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 923
Although this is a very good article with regards to moving user databases, what happens if you want to move the master database somewhere else? This is something I have had to do often due to badly installed instances and it would certainly help the admins that realise perhaps the database files don't really belong alongside the operating system!
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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" ;-)
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 2592
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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" ;-)
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 2592
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8843 Visits: 16579
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.


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)
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

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" ;-)
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