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 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798

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
Posted Friday, September 24, 2010 1:35 PM


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







David B.
Post #993059
Posted Friday, September 24, 2010 10:29 PM


SSC-Enthusiastic

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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475


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
Post #994469
Posted Tuesday, September 28, 2010 8:37 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: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
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
Post #994526
Posted Wednesday, September 28, 2011 6:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:26 AM
Points: 1,120, Visits: 4,909
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 «««23456

Permissions Expand / Collapse