Moving Database Files/Folers to another Drive
-
I apologize to all if this has been covered elsewhere. I *did* search and saw many similar threads (moving databases, that sort of thing). This one is different... Or at least seems like it to me. (I posted to one of the existing threads that seemed most similar and got my "hands slapped" for not opening a new thread 😀 )
We have our SQL data and our backups stored on a RAID. Due to some logistics issues involved with moving a few servers around, we are removing the RAID from service. I need to move all of my (hmm, what would be the term) Target Folders to another drive.
Referencing what I found in other posts (and a couple of folks I know with SQL experience) I came up with the following procedure.
The procedures seem almost frighteningly straightforward... (FWIW, I'm doing/did all the Sql Server mgmt through the Mgmt Studio)
* detach all the databases
* stop the server instance
* (In Windows) copy the MSSQL folder from the source to destination drive
* (In Windows) copy the Backup folders from the source to the destination drive
* reconfig the server properties to point at the new MSSQL folder (<server>=>Properties=>Database Settings)
* restart the server instance
* attach the databases
* reconfig the maintenance plans <server>=>Management=>Maintenance Plans)
This all seems to have gone extremely well. The server is up, the client applications are seeing the data... The data files in the old location have not been changed, the files in the new location are. The backup jobs (Differential Backup) completed as expected and the .trn and .bak files are in the "new location".
BUT... I seem to have missed something. There is something that looks like a log file that was updated on the "old location" when the Diff plan ran. It looks like some sort of log file of the Diff run (Filename is Differentials_Subplan_1_<datetime>.txt)... Clearly I've missed some "report destination" or "log file" setting someplace... I've looked over various options and properties, but have missed it again...
Any ideas?
Thanx!
-
It sounds like it may be a log for the maintenance plans. If you have a Diff Maintenance plan on that server, edit it and see if you can see where it is sending the logs. You should be able to make the change there.
Chris Shaw
-
Third Time is a Charm?:-P
For a third time I went through the Maint Plans... I didnt see anything about logging again...
But, this time I started rolling over the icons in the toolbar in the Design window... Aha... One of those is "Reporting and Logging"!
Got it...
Pretty sure that will take care of it...
Thanx@!
Rich
-
So, did it helped?
-
Yes!
This morning, the Diff Maint Job log file was where it "was supposed to be"...
This AM, the RAID is now removed...
Database up, Client App happy...
I'm not thrilled with "my" database files AND the client app installables being on the same drive... but that is a different discussion... (The backups AND the live database files are all on the same drive/partitian... At least for a few days... isn't that grand!) :crying:
-
why did you detach and re attach, why didn't you just use
ALTER DATABASE MODIFY FILE
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Mostly because I'm familiar with Attach and Detach, through the Mgmt Console... It was a procedure that was suggested by someone I trust (not to mention he is a member of the team that supports the Client App in question)...
I was also concerned about being able to "completely abandon" the "old drive" and was concerned that commands run against specific databases might leave something behind (system databases?).
But I will admit that I am unfamiliar with the
ALTER DATABASEcommand. I'll look into that just for its educational value. In your opinion, what is the advantage of using it over what I did? -
richsilv (10/22/2010)
In your opinion, what is the advantage of using it over what I did?Using detach\attach to move database files is not the MS recommended way to achieve this. It's more cumbersome to script where as ALTER DATABASE is easier and recommended!
For system databases especially you need to use the alter database method! There is an MS kb that details how to move databases using the alter method.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
OK, lets get down to brass tacks...
Originally, the database files were in S:\MSSQL\Data and lets use ImagineCompanyA as one of the databases.
My plan was to move all the database files to D:\SQLData\MSSQL\Data
Is your suggestion that I could have used the command
AKTER DATABASE ImaginationCompanyA MODIFY NAME =D:\SQLData\MSSQL\Data\ImaginationCompanyAInstead of "Detach", Move data to destination, "Attach"?
Please note that MS also recommends using detach and attach (http://support.microsoft.com/kb/224071). Please note, I am not arguing, just trying to understand...
-
Firstly SP_Attach_DB is deprecated in future versions of SQL Server the following statement should be used instead
CREATE DATABASE FOR ATTACH
The link you posted is fairly dated and really only applicable to SQL 7\2000 (yes i know it mentions SQL 2005)! In fact the summary section on that page redirects you to the SQL 2008 link below.
The following links detail the recommended database move process
PS i cant see your code!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply