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 Detach/Attach or ALTER DATABASE? Expand / Collapse
Author
Message
Posted Wednesday, May 27, 2009 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 05, 2010 10:14 AM
Points: 3, Visits: 49
On occasion a log file will fill up to the problem point and we could use detach/attach to delete the old log and start over. Not something that we would normally want to do but something that was an option in an emergency. Is there a simple way to do that once detach/attach goes away?
Post #723994
Posted Wednesday, May 27, 2009 6:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 2,105, Visits: 3,560
Jonathan Kehayias (5/27/2009)
Brian Munier (5/27/2009)
Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?


You should use CREATE DATABASE [databasename] FOR ATTACH as shown in the quote from the Books Online. This has a much more verbose syntax and allows for the options needed to be configured while the database is created to include, who the owner should be, one of the other problems listed in the comments here.


...and there is a great script for this HERE


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #723998
Posted Wednesday, May 27, 2009 6:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
GRE (5/27/2009)
Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

http://msdn.microsoft.com/en-us/library/ms188031.aspx
Post #724008
Posted Wednesday, May 27, 2009 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:13 AM
Points: 257, Visits: 80
David Benoit (5/27/2009)
Jonathan Kehayias (5/27/2009)
Brian Munier (5/27/2009)
Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?


You should use CREATE DATABASE [databasename] FOR ATTACH as shown in the quote from the Books Online. This has a much more verbose syntax and allows for the options needed to be configured while the database is created to include, who the owner should be, one of the other problems listed in the comments here.


...and there is a great script for this HERE


Thanks,
I think my colleague uses this script all the time.

Brian
Post #724017
Posted Wednesday, May 27, 2009 6:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
jboc (5/27/2009)
On occasion a log file will fill up to the problem point and we could use detach/attach to delete the old log and start over. Not something that we would normally want to do but something that was an option in an emergency. Is there a simple way to do that once detach/attach goes away?


This should never be used as an option. It is essentially ripping the transactional heart out of your database. Despite being successful doing this numerous times in the past, it only takes once for the disasterous effects of doing this to ruin your day, job, career when the database fails to attach without its log.

I'd recommend reading Paul Randal's article:

http://technet.microsoft.com/en-us/magazine/cc895648.aspx

and his blog posts on the Transaction Log:

http://sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx

Specifically:

http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx
http://sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.


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 #724019
Posted Wednesday, May 27, 2009 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
David Benoit (5/27/2009)
Jonathan Kehayias (5/27/2009)
Brian Munier (5/27/2009)
Interesting article, thank you. My question is if detach/attach is going away, and I want to move a database from one server to another server does that mean I now have to use backup/restore which can be slower and require more effort to work?


You should use CREATE DATABASE [databasename] FOR ATTACH as shown in the quote from the Books Online. This has a much more verbose syntax and allows for the options needed to be configured while the database is created to include, who the owner should be, one of the other problems listed in the comments here.


...and there is a great script for this HERE


Excellent script David. Thanks for pointing that out.


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 #724023
Posted Wednesday, May 27, 2009 6:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 5:23 PM
Points: 1,030, Visits: 2,790
GRE (5/27/2009)
--------------------------------------------------------------------------------
Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

http://msdn.microsoft.com/en-us/library/ms188031.aspx


Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new script?


Gethyn Ellis

gethynellis.com
Post #724038
Posted Wednesday, May 27, 2009 6:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 5:23 PM
Points: 1,030, Visits: 2,790
Noel McKinney (5/27/2009)
GRE (5/27/2009)
Have i missed a trick here? I understand that the old way to attach a database is be depreciated, But is SP_Detach_DB bein decreciated too? or will that remain?

Yes, both the attach and detach stored procedures are deprecated, here is the page for sp_detach_db from Books Online (see the Important box just after the syntax section):

http://msdn.microsoft.com/en-us/library/ms188031.aspx


Yes i saw that....but It offers no alternative,like the SP_Attach_Db BOL entry does, so if both these sp are being depreciated, how are you going detach a db in the first place to warrant attaching it using this new approach?


Gethyn Ellis

gethynellis.com
Post #724040
Posted Wednesday, May 27, 2009 7:13 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:56 PM
Points: 136, Visits: 640
Jonathan Kehayias (5/27/2009)
jboc (5/27/2009)
On occasion a log file will fill up to the problem point and we could use detach/attach to delete the old log and start over. Not something that we would normally want to do but something that was an option in an emergency. Is there a simple way to do that once detach/attach goes away?


This should never be used as an option. It is essentially ripping the transactional heart out of your database. Despite being successful doing this numerous times in the past, it only takes once for the disasterous effects of doing this to ruin your day, job, career when the database fails to attach without its log.

I'd recommend reading Paul Randal's article:

http://technet.microsoft.com/en-us/magazine/cc895648.aspx

and his blog posts on the Transaction Log:

http://sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx

Specifically:

http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx
http://sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.


Please please listen to Jonathan and Paul's advice on this. Do not EVER delete the transaction log like that. Recently I had to deal with that mess and Paul helped me through it. He wrote a blog post about it: http://www.sqlskills.com/BLOGS/PAUL/post/A-sad-tale-of-mis-steps-and-corruption-(from-today).aspx

I highly suggest you read that post to understand why you never want to do this.


=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Post #724055
Posted Wednesday, May 27, 2009 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 1:30 PM
Points: 34, Visits: 82
It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.

The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.

Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.

In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.

NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.
Post #724144
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse