November 20, 2009 at 11:04 am
Fresh installl of SQL 2005 on the same domain server (clustered) just added them to a brand new hard drive. Using all the default SQL 2005 (sp2) paths.
moved all system DB & other databases from the old hard drive to new hard drive onto the default DATA folder (4 am)
Ran fine for hours
At 7 AM - SQL Server services stopped
Restarted the SQL Service - everything worked fine and looked complete
However - the distribution, model, and MSDB moved themselves back to the orginal server hard drive from where they were moved. System and databases all still on the new hard drive.
Ran perfectlly the rest of the day
It even successfully Ran scheduled DB full backup jobs at 2 AM
Failed at 6 AM
- Several of the production databases are now zero bytes
Service will not start.
I copied the distribution, model, and MSDB from the old hard drive and put into the New hard drive (data directory)
Manually trying to start the MSSQL services (stopped all associated sql services such as full-text search)
No critical error - just error 7027 - indicating a compressed file (but it is not a compressed file).
December 3, 2009 at 3:18 am
I'm not sure that I understand this:
"the distribution, model, and MSDB moved themselves back to the orginal server hard drive from where they were moved"
December 3, 2009 at 9:46 am
After another meltdown... and hours of learning.
Turns out that when us smaller shops run SQL on the same server as MS Exchange....
The Exchange update does something to the effect of changing the SQL version so past backups can not be restored.
(MSDN ms190190)
Considerations for Backing Up and Restoring System Databases
O.K. That aside:
Yes, they will move themselves back on a restart.
MSDN ms345408(printer).aspx
Moving System Databases
Important: If you move a system database ...
1. For each file to be moved, run the following statement.
Alter Database... see code for new location.
So, when my 2:00 AM job stopped the server, made file backups, and re-started... these files look internally and rediscovered thier old drive name.
So, by shutting down the SQL Services, copying and moving all the system files to the new hard drive... without modify to the internal file location parameters, they moved themselves back.
After spending a few hours again on the same incident, and not wanting to make the same mistake from the previous incident, we altered the file from a cmd window.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply