Moving Database Files in SQL Server

  • 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" 😉

  • 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

  • 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" 😉

  • 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.

  • 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.

  • 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" 😉

  • 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" 😉

  • 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.

  • 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.

  • 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" 😉

  • 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

  • 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.

  • 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" 😉

  • 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" 😉

  • 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!

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply