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 «««23456»»

Moving Database Files Detach/Attach or ALTER DATABASE? Expand / Collapse
Author
Message
Posted Friday, September 24, 2010 6:44 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, February 2, 2011 4:11 AM
Points: 671, Visits: 356
I didn't think you could move the log file with alter... unless you used enterprise or developer version of SQL.

Amy
Post #992721
Posted Friday, September 24, 2010 6:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #992730
Posted Friday, September 24, 2010 7:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:07 PM
Points: 1,880, Visits: 3,463
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.
Post #992761
Posted Friday, September 24, 2010 7:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #992770
Posted Friday, September 24, 2010 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 1:18 PM
Points: 4, 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.
Post #992908
Posted Friday, September 24, 2010 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 138, Visits: 456
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
Post #992927
Posted Friday, September 24, 2010 10:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #992937
Posted Friday, September 24, 2010 10:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 138, Visits: 456
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
Post #992948
Posted Friday, September 24, 2010 10:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
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

Post #992955
Posted Friday, September 24, 2010 10:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:43 AM
Points: 61, Visits: 700
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.
Post #992962
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse