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
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: 8784 Visits: 16558
Comments posted to this topic are about the item Moving Database Files in SQL Server

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Pradeep Hebballi
Pradeep Hebballi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 18
Hi Perry, the article was really helpful. Thanks!

You mentioned that SP_ATTACH_DB is deprecated. Does that mean that we cannot use DETACH and ATTACH database methods to change/rename the database files in SQL Server 2008?

Thanks
Pradeep
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: 8784 Visits: 16558
Pradeep Hebballi (2/11/2013)
Hi Perry, the article was really helpful. Thanks!

Thank you, i'm glad you found it useful\helpful



Pradeep Hebballi (2/11/2013)
You mentioned that SP_ATTACH_DB is deprecated. Does that mean that we cannot use DETACH and ATTACH database methods to change/rename the database files in SQL Server 2008?

Thanks
Pradeep

The whole point is you don't need to sp_attach_db, if all you're doing is renaming or moving database files use my article. To attach a database from raw files use

CREATE DATABASE database_name FOR ATTACH



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Willem G
Willem G
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 519
Good article! One possible source of confusion: the screenshots showing 'completed with errors' and 'success' will not show after issuing the command "ALTER DATABASE [yourDB] SET ONLINE" but after performing 'Tasks - Bring Online' in the GUI.
heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
Nice artical, I must admit i'm still a fan of detatching the databases to move files as I tend to only do this on maintenance windows so the amount of time it takes me isn't an issue (plus i'm prone to the odd typ0).

One other thing, prob obvious to most readers, is that you can't do this method if your moiving the master or resource databases MS has an artical on this at http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx this one is for 2005 but the other versions are available using the other version drop down.
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: 8784 Visits: 16558
Willem Gossink (2/11/2013)
Good article! One possible source of confusion: the screenshots showing 'completed with errors' and 'success' will not show after issuing the command "ALTER DATABASE [yourDB] SET ONLINE" but after performing 'Tasks - Bring Online' in the GUI.

Thanks for your feedback.
The first 2 screenshots show a typical scenario where an operation to online the database fails, the last shows the dialog you will see when the online operation succeeds. The text does indicate this.

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

"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: 8784 Visits: 16558
SQLDBA360 (2/11/2013)
Nice artical, I must admit i'm still a fan of detatching the databases to move files

No longer necessary to detach the database. The ALTER DATABASE ... MODIFY FILE command is the preferred way to perform these actions.


SQLDBA360 (2/11/2013)
(plus i'm prone to the odd typ0).

Type slower and always check your work ;-)


SQLDBA360 (2/11/2013)
One other thing, prob obvious to most readers, is that you can't do this method if your moiving the master or resource databases MS has an artical on this at http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx this one is for 2005 but the other versions are available using the other version drop down.

In the article I make references to user databases only ;-)
However, i appreciate your point and could have made it clearer.
Note also that for the resource database you do still need to issue an

ALTER DATABASE ... MODIFY FILE



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Willem G
Willem G
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 519
Perhaps I did not explain my point clearly.
Your article suggests using the "ALTER DATABASE ... " T-SQL command to bring the database back online. This is, of course, fine. However, the following screenshot is not the result of a T-SQL command. The screenshot is the result of the 'Bring Online' task in the GUI.
Degradable
Degradable
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 154
One of the most common errors we find on moving files is security. Admins quite often forget that SQL application service will require permissions to the new location. On start-up / bring on-line the error occurs and panic follows.... Yet all we need to ensure is the relevant permissions are in place at the alternate location.
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: 8784 Visits: 16558
Willem Gossink (2/11/2013)
Perhaps I did not explain my point clearly.
Your article suggests using the "ALTER DATABASE ... " T-SQL command to bring the database back online. This is, of course, fine. However, the following screenshot is not the result of a T-SQL command. The screenshot is the result of the 'Bring Online' task in the GUI.

ok, i see. Thanks for pointing that out

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

"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