September 24, 2010 at 9:56 am
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?
September 24, 2010 at 10:10 am
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[/url]
September 24, 2010 at 10:28 am
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
September 24, 2010 at 10:39 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2010 at 10:44 am
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.
September 24, 2010 at 11:46 am
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[/url] 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_desccollecttime
OFFLINE2010-09-24 13:34:27.640
OFFLINE2010-09-24 13:34:27.647
ONLINE2010-09-24 13:34:27.650
RECOVERING2010-09-24 13:34:27.677
RECOVERING2010-09-24 13:34:27.683
RECOVERING2010-09-24 13:34:27.690
RECOVERING2010-09-24 13:34:27.693
RECOVERING2010-09-24 13:34:27.700
ONLINE2010-09-24 13:34:27.707
ONLINE2010-09-24 13:34:27.713
ONLINE2010-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_desccollecttime
OFFLINE2010-09-24 13:42:58.457
OFFLINE2010-09-24 13:42:58.460
ONLINE2010-09-24 13:42:58.467
RECOVERING2010-09-24 13:42:58.493
RECOVERING2010-09-24 13:42:58.500
RECOVERY_PENDING2010-09-24 13:42:58.507
RECOVERY_PENDING2010-09-24 13:42:58.510
RECOVERY_PENDING2010-09-24 13:42:58.517
RECOVERY_PENDING2010-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[/url]
September 24, 2010 at 1:35 pm
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[/url] 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.
September 24, 2010 at 10:29 pm
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.
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
September 27, 2010 at 5:38 am
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
September 28, 2010 at 8:01 am
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
😛
September 28, 2010 at 8:37 am
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
September 28, 2010 at 8:44 am
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
😛
September 28, 2011 at 6:00 am
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?
May 22, 2017 at 3:59 pm
Great article! Thanks for info.
Is there a way to use sp_MSForEachDB to modify the file locations for each database in the instance? I just want to move all the databases in this instance to another location on the same server. Thank-you.
Ah I found the script! It is in the forums here. Yeah!
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply