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 Sunday, February 10, 2013 11:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
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"
Post #1418218
Posted Monday, February 11, 2013 12:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:04 AM
Points: 28, Visits: 16
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
Post #1418232
Posted Monday, February 11, 2013 1:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
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"
Post #1418242
Posted Monday, February 11, 2013 2:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:20 AM
Points: 23, Visits: 256
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.
Post #1418256
Posted Monday, February 11, 2013 2:12 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:25 AM
Points: 716, Visits: 755
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.
Post #1418258
Posted Monday, February 11, 2013 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
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"
Post #1418263
Posted Monday, February 11, 2013 2:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
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"
Post #1418266
Posted Monday, February 11, 2013 3:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:20 AM
Points: 23, Visits: 256
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.
Post #1418281
Posted Monday, February 11, 2013 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 5:59 AM
Points: 5, Visits: 61
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.
Post #1418284
Posted Monday, February 11, 2013 4:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,956, Visits: 12,838
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"
Post #1418312
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse