SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Database Files Detach/Attach or ALTER DATABASE?


Moving Database Files Detach/Attach or ALTER DATABASE?

Author
Message
amarshall-568002
amarshall-568002
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 356
I didn't think you could move the log file with alter... unless you used enterprise or developer version of SQL.

Amy
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 1807
amarshall-568002 (9/24/2010)
I didn't think you could move the log file with alter... unless you used enterprise or developer version of SQL.

Amy


You can use ALTER DATABASE MODIFY FILE for log files in any edition of SQL Server including Express. Once the command has been executed, you take the database offline, move the file to the new location and then bring the database back online. This is not an Enterprise Only Feature.

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
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 3575
Jonathan Kehayias (5/27/2009)
When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.
I don't even think you can attach a database without a transaction log if the database wasn't shut down in a clean manner, and by that I mean that there would be no need for rollback or roll forwards during startup.

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.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 1807
Nils Gustav Stråbø (9/24/2010)
Jonathan Kehayias (5/27/2009)
When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.
I don't even think you can attach a database without a transaction log if the database wasn't shut down in a clean manner, and by that I mean that there would be no need for rollback or roll forwards during startup.

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.


You can get it back into the server but as I said above, it won't be guaranteed to be consistent. You may have to hack around it to get it back in, but it can certainly be done. I wouldn't advocate deleting a log file ever, but people do it for some reason.

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
jason.devereaux
jason.devereaux
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 23
Thanks for the article! I used to be a fan of the detach/attach method of moving database files around... that is, until I brought down Service Broker for Operations Manager 2007 in the Production environment! Didn't realize it either. Only caught it because the application folks started receiving failed connection messages every second or two. Had to use the SET ENABLE_BROKER command to get it back up! Ahhh, my rookie days...

ALTER DATABASE is definitely my preference now!

Jason D.
Mad Myche
Mad Myche
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 518
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
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 1807
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
Mad Myche
Mad Myche
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 518
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87741 Visits: 45272
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
David BAFFALEUF
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 712
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.
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