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 Monday, February 11, 2013 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 4:28 PM
Points: 1, 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
Post #1418672
Posted Tuesday, February 12, 2013 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:45 AM
Points: 4, 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.

Post #1418822
Posted Tuesday, February 12, 2013 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 6,751, Visits: 14,395
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"
Post #1418844
Posted Tuesday, February 12, 2013 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 6,751, Visits: 14,395
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"
Post #1418845
Posted Tuesday, February 12, 2013 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:59 PM
Points: 333, Visits: 555
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!
Post #1418986
Posted Tuesday, February 12, 2013 8:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 6,751, Visits: 14,395
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"
Post #1419016
Posted Tuesday, February 12, 2013 1:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:28 AM
Points: 1,544, Visits: 2,274
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
Post #1419167
Posted Wednesday, February 13, 2013 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 6,751, Visits: 14,395
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"
Post #1419595
Posted Wednesday, February 13, 2013 9:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:28 AM
Points: 1,544, Visits: 2,274
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
Post #1419608
Posted Wednesday, February 13, 2013 12:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 6,751, Visits: 14,395
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"
Post #1419716
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse