Click here to monitor SSC
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
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1824 Visits: 1807

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
David BAFFALEUF
David BAFFALEUF
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

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

David B.
Pritam Salvi
Pritam Salvi
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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]

Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
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
jswong05
jswong05
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 476


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
:-P
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47351 Visits: 44392
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, 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


jswong05
jswong05
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 476
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
:-P
jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1176 Visits: 5009
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?
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