Moving Database Files Detach/Attach or ALTER DATABASE?

  • Mad Myche

    SSCommitted

    Points: 1573

    I don't get to play with all of the latest sql/os combinations yet (currently 2000 on 2003 server), but could scripting be created to integrate with CLR or Powershell to do the alterDB and the filemovements within a home rolled SProc?

    Director of Transmogrification Services
  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Mad Myche (9/24/2010)


    I don't get to play with all of the latest sql/os combinations yet (currently 2000 on 2003 server), but could scripting be created to integrate with CLR or Powershell to do the alterDB and the filemovements within a home rolled SProc?

    Anything is possible, but why would you want to? If you used Powershell, just use it from Powershell, why would you want it to be in a stored procedure?

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Mad Myche

    SSCommitted

    Points: 1573

    Jonathan Kehayias (9/24/2010)


    Mad Myche (9/24/2010)


    I don't get to play with all of the latest sql/os combinations yet (currently 2000 on 2003 server), but could scripting be created to integrate with CLR or Powershell to do the alterDB and the filemovements within a home rolled SProc?

    Anything is possible, but why would you want to? If you used Powershell, just use it from Powershell, why would you want it to be in a stored procedure?

    As I said, at this time I only get to read about new features, dont get to play them yet. More importantly, this does show the techniques could be integrated to the point that you dont need to go from Sql Management to the OS and then back to Sql to alterDB file location

    Director of Transmogrification Services
  • Gail Shaw

    SSC Guru

    Points: 1004474

    Nils Gustav Stråbø (9/24/2010)


    So if you delete the transaction log file and the database was not shut down properly there is no chance in h*** you can attach the database again.

    Or am I missing something? Not that I would ever do something like that.

    It won't attach, but there are ways to (in most cases) get it back.

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • David Baffaleuf

    SSCrazy

    Points: 2159

    Hi Jonathan,

    Another benefit of the backup/restore method over attach/detach or offline/online could be that the backup does not flush the plan cache for the database considered.

    And I believe there's another subtle difference between setting the database to OFFLINE and detaching it. When you reattach the database, it goes into the recovery process, whereas when the database goes back from OFFLINE to ONLINE, it does not. OFFLINE is a persisted state, and you can verify this by going directly from OFFLINE to EMERGENCY, and thus bypassing the recovery. I have been bitten by some interesting cases of transaction log corruptions not discovered yet because the recovery is not run when the database comes back ONLINE.

    Chances that recovery is needed are very unlikely to happen though because you would need either to have exclusive access to the database when taking it OFFLINE or use the WITH ROLLBACK option, which would clean opened transactions before allowing the database to change its state. But as the files are unlocked, they remain unprotected and the tlog could be scribbled by anything, even in user mode.

    Another funny thing about the OFFLINE state is that you can drop an offline database, it will do exactly the same as detach: remove the entries from the system tables, and leave the files unlocked on the FS.

    Thanks for the article,

    David B.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Another benefit of the backup/restore method over attach/detach or offline/online could be that the backup does not flush the plan cache for the database considered.

    For moving files between two different servers or on the same server? How/why would you use backup/restore to move the database files from say D to E on the same server which is what this article was about? I have a different article called Use Backup/Restore to Minimize Upgrade Downtimes[/url] that would apply to moving the database to a different server.

    And I believe there's another subtle difference between setting the database to OFFLINE and detaching it. When you reattach the database, it goes into the recovery process, whereas when the database goes back from OFFLINE to ONLINE, it does not. OFFLINE is a persisted state, and you can verify this by going directly from OFFLINE to EMERGENCY, and thus bypassing the recovery. I have been bitten by some interesting cases of transaction log corruptions not discovered yet because the recovery is not run when the database comes back ONLINE.

    I am sorry, but you are incorrect here. When you go from OFFLINE to ONLINE the database does go through recovery again. You can actually see this by monitoring the database state_desc column in a loop while setting the database to ONLINE. Create a database on a dev server named test and set it to OFFLINE. Then run the below code in a query window and then bring the database ONLINE again.

    create table #db_state_changes

    (state_desc sysname, collecttime datetime default(getdate()))

    WHILE 1=1

    begin

    insert into #db_state_changes (state_desc)

    select state_desc

    from sys.databases

    where name = 'test'

    waitfor delay '00:00:00.005'

    end

    Once the database is ONLINE stop the execution of the above and then do:

    select * from #db_state_changes

    If you scroll through the results you should come to something like:

    state_desc collecttime

    OFFLINE 2010-09-24 13:34:27.640

    OFFLINE 2010-09-24 13:34:27.647

    ONLINE 2010-09-24 13:34:27.650

    RECOVERING 2010-09-24 13:34:27.677

    RECOVERING 2010-09-24 13:34:27.683

    RECOVERING 2010-09-24 13:34:27.690

    RECOVERING 2010-09-24 13:34:27.693

    RECOVERING 2010-09-24 13:34:27.700

    ONLINE 2010-09-24 13:34:27.707

    ONLINE 2010-09-24 13:34:27.713

    ONLINE 2010-09-24 13:34:27.720

    If the log file wasn't available and the database was in full recovery, the database would not go online, it would go into Recovery_Pending like this:

    state_desc collecttime

    OFFLINE 2010-09-24 13:42:58.457

    OFFLINE 2010-09-24 13:42:58.460

    ONLINE 2010-09-24 13:42:58.467

    RECOVERING 2010-09-24 13:42:58.493

    RECOVERING 2010-09-24 13:42:58.500

    RECOVERY_PENDING 2010-09-24 13:42:58.507

    RECOVERY_PENDING 2010-09-24 13:42:58.510

    RECOVERY_PENDING 2010-09-24 13:42:58.517

    RECOVERY_PENDING 2010-09-24 13:42:58.523

    The only exception I have found to this is for SIMPLE Recovery Model databases that shutdown cleanly where it creates a new log file, but that is like playing Russian Roulette at best and not something you'd want to gamble with.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • David Baffaleuf

    SSCrazy

    Points: 2159

    Jonathan Kehayias (9/24/2010)


    Another benefit of the backup/restore method over attach/detach or offline/online could be that the backup does not flush the plan cache for the database considered.

    For moving files between two different servers or on the same server? How/why would you use backup/restore to move the database files from say D to E on the same server which is what this article was about? I have a different article called Use Backup/Restore to Minimize Upgrade Downtimes[/url] that would apply to moving the database to a different server.

    Yes I was referring to moving from one server to another.

    Your demonstration about recovery is very interesting, that's another myth you clarified. I guess I was mistaken because the database goes OFFLINE, then ONLINE and only then into the recovery process. Apologies for the misunderstanding, next time I'll double check before I open my mouth. 😀

    David B.

  • Pritam Salvi

    SSC Eights!

    Points: 978

    Hi all,

    I am using sql server 2000

    I am trying to use alter database option to move datafiles.

    But it showed me error below-

    Server: Msg 5037, Level 16, State 1, Line 1

    MODIFY FILE failed. Do not specify physical name.

    Look at the my qry

    ALTER DATABASE NewTestDB

    MODIFY FILE (NAME = NewTestDB_Data, FILENAME = 'D:\SQLData\NewTestDB_Data.mdf');

    ALTER DATABASE NewTestDB

    MODIFY FILE (NAME = NewTestDB_Log, FILENAME = 'D:\SQLData\NewTestDB_Log.ldf');

    Whats wrong in this?

    Pls let me know why alter database is not happening.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Thanks for nice article.

    But, BOL is also using "sp_detach_db" command to describe attach database command as below:

    USE master;

    GO

    sp_detach_db Archive;

    GO

    CREATE DATABASE Archive

    ON (FILENAME = 'D:\SalesData\archdat1.mdf')

    FOR ATTACH ;

    GO

    Thanks

  • jswong05

    Hall of Fame

    Points: 3503

    There is no magic, it is suggested you fool the engine by renaming mdf file or use backup file. You lost what is in the transaction log that hasn't been backup if you choose the first option. If you choose second option, you can revert back to point-in-time before this happened (provided you have good backups). Many questions need not be answered (already on BOL).

    Jason
    http://dbace.us
    😛

  • Gail Shaw

    SSC Guru

    Points: 1004474

    jswong05 (9/28/2010)


    There is no magic, it is suggested you fool the engine by renaming mdf file or use backup file. You lost what is in the transaction log that hasn't been backup if you choose the first option. If you choose second option, you can revert back to point-in-time before this happened (provided you have good backups).

    I'm not sure what point you're trying to make.

    I posted that in reply to the statement

    So if you delete the transaction log file and the database was not shut down properly there is no chance in h*** you can attach the database again.

    Sure, you lose anything that hasn't been committed to the data file. I thought that was made fairly clear. That blog post covers the case where there is no backup, the DB has not been shut down cleanly and the log is missing. It's a last resort for maybe getting back online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • jswong05

    Hall of Fame

    Points: 3503

    I'm not sure what point you're trying to make.

    The point is to emphasize again, what you lose and don't lose on each method (the data that wasn't taken care of by checkpoint and Lazy writer are lost, so the h*** guy doesn't think it is a full recovery), so those people won't be confused before they attempt a solution. The article is good. It is nice people takes time to write an article explaining to people who don't understand. Myself, I am not a kindergarden teacher. Like the college professors, I write, the reader does not understand. It is not my problem. If editor does not understand, I publish to another site.

    Jonathan spent time and effort, wrote a nice tutorial article. He did not need to defend himself. The editor who reviewed article should pick up the tab and explain to those who don't understand how it works.

    Jason
    http://dbace.us
    😛

  • jts2013

    Hall of Fame

    Points: 3226

    Hi there, we have a report server where the data is replicated from our primary server. But I have spotted that both the MDF and LDF are on the same LUN and we have plenty of room on another LUN to host the LDF file.

    I was going to use the ALTER command but was concerned because the data is a replicated copy. For a database that is the secondary in a replication pair do I need to do anything different to what this article proposes?

  • nfink

    Valued Member

    Points: 67

    Great article! Thanks for info.
    Is there a way to use sp_MSForEachDB to modify the file locations for each database in the instance? I just want to move all the databases in this instance to another location on the same server. Thank-you.
    Ah I found the script! It is in the forums here. Yeah!

Viewing 14 posts - 46 through 59 (of 59 total)

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