Moving Database Files Detach/Attach or ALTER DATABASE?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Moving Database Files Detach/Attach or ALTER...

Add to briefcase

58 posts, Page 6 of 6

«««2 3 4 5 6

Moving Database Files Detach/Attach or ALTER DATABASE?

Expand / Collapse

Author

Message

Jonathan Kehayias

Posted Friday, September 24, 2010 11:46 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Post #992999

David BAFFALEUF

Posted Friday, September 24, 2010 1:35 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 10:15 AM
Points: 61, Visits: 691

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

BigGrin

David B.

Post #993059

Posted Friday, September 24, 2010 10:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53

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.

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]

Post #993191

Posted Monday, September 27, 2010 5:38 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:11 AM
Points: 877, Visits: 1,159

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

Post #993570

Posted Tuesday, September 28, 2010 8:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27, Visits: 421

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

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
Tongue

Tongue

Post #994469

Posted Tuesday, September 28, 2010 8:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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 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 #994517

Posted Tuesday, September 28, 2010 8:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27, Visits: 421

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
Tongue

Tongue

Post #994526

Posted Wednesday, September 28, 2011 6:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 1,060, Visits: 4,182

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?

Post #1182374

« Prev Topic | Next Topic »

Add to briefcase

58 posts, Page 6 of 6

«««2 3 4 5 6

Permissions

Expand / Collapse