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 ««12345»»»

Moving the SQL 2005 System Databases Expand / Collapse
Author
Message
Posted Saturday, March 24, 2007 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:14 PM
Points: 1,328, Visits: 333

Thanks for the script, Vince.  This came in very handy.

One thing to add -- I had a reporting services instance that logged in when the DB went into single user mode.

Disabling it before starting in single user solved it, though.

Thanks,

Jim

 




Post #353691
Posted Monday, March 26, 2007 6:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 716, Visits: 465
Jim,

Thanks, we unfortunately don't have RS running so I didn't think of that.

Vince



Post #353812
Posted Wednesday, April 11, 2007 9:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846

Vince,

I can't even begin to tell you how much time & effort this script has saved us.

We've modified it slightly so it now incorporates installing 8 named instances of SQL 2005 Standard Edition via 'unattended' install.  The whole process now takes barely 30 minutes - fantastic.

I'm now aiming to have the script install SP2 as well after an automated reboot.  I'm so close but have come across a problem which appears to be connected to the mssqlsystemresource db.  After a fresh install of 2005 SE and a reboot I see the following error in the sql event log whilst trying to upgrade the 1st instance to SP2.

2007-04-11 15:26:52.04 spid5s      SQL Server started in single-user mode. This an informational message only. No user action is required.
2007-04-11 15:26:52.06 spid5s      Starting up database 'master'.
2007-04-11 15:26:52.14 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-04-11 15:26:52.20 spid5s      SQL Trace ID 1 was started by login "sa".
2007-04-11 15:26:52.21 spid5s      Starting up database 'mssqlsystemresource'.
2007-04-11 15:26:52.21 spid5s      The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-04-11 15:26:52.23 spid5s      Error: 5173, Severity: 16, State: 1.
2007-04-11 15:26:52.23 spid5s      One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
2007-04-11 15:26:52.23 spid5s      Error: 5173, Severity: 16, State: 1.
2007-04-11 15:26:52.23 spid5s      One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
2007-04-11 15:26:52.25 spid5s      Log file 'E:\MSSQLSERVER\MSSQL.1\MSSQL\LOGS\mssqlsystemresource.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
2007-04-11 15:26:52.25 spid5s      The log cannot be rebuilt when the primary file is read-only.
2007-04-11 15:26:52.25 spid5s      Error: 945, Severity: 14, State: 2.
2007-04-11 15:26:52.25 spid5s      Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2007-04-11 15:26:52.25 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I've noticed that the mssqlsystemresource.ldf has moved across to it's new location on the E: drive but there is a 'new' copy of the ldf in the same location as the mssqlsystemresource.mdf on the D: drive.?!

Obviously there's something wrong somewhere but I just can't see it.  The -d -e & -l parameters are all pointing to the right locations.  The other system db's have all appeared to move across to their respective locations correctly.

Without installing SP2 SQL works perfectly.  As it stands i'm unable to start the mssql$inst1 service.

Any advice would be greatly appreciated.

Cheers,

Mark

Post #357519
Posted Wednesday, April 11, 2007 9:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 716, Visits: 465
Mark,

Thanks for letting me know how much time this has saved you. Its done the same for our shop. I frankly wonder how people get along without some automated means to move the system databases. Its just so error-prone and time-consuming to do it manually.

I believe I had a similar error to yours once, referred to in the original article. You need to ensure that the old copy of mssqlsystemresource is not located in the original install directory, because the SPs will assume that's the active one if they see it. So after running the move script, ensure you don't see the .ldf and .mdf for mssqlsystemresource anywhere on D:.

Let me know if that's of help.

Vince



Post #357528
Posted Wednesday, April 11, 2007 9:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846

Thanks for the reply Vince.  Of course it makes sense if the old file still exists in the original directory!

Before the script it would take us almost half a day to get SQL installed & configured not to mention the problems we had when there were problems with moving the master db!  Now we can pretty much have a fully configured sql server in less than 1 hour!

Have you ever scripted the creation/configuration of 2005 maintenance plans?  That would be the icing on the cake!

I'll let you know how I get on.

Thanks once again.

Mark

Post #357541
Posted Wednesday, April 11, 2007 10:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846

Okay having just run SP2 again on a clean SQL 2005 installation with the mdf's & ldf's moved to their respective homes.  When SP2 was upgrading the 1st named instance I saw several new files get created in the 'original' data folder.

These were distmdl.mdf & .ldf (I delete these as part of your script as they are not needed) & mssqlsystemresource1.ldf further into the upgrade process the file gets renamed to mssqlsystemresource.ldf.  Shortly after this I see the hotfix.exe error box appear.

Does this mean I just need to change in the script when the service pack runs and move the resource db after the sp upgrade?  But surely I will then have the same problem when another sp get's released?

help...

Post #357576
Posted Wednesday, April 11, 2007 10:47 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 716, Visits: 465
Mark,

PM me at vince.iacoboni location db.com (replace location with the symbol that is above 2 on the keyboard [I don't want spam!]). I will include my latest script that checks and sets the registry for SQL 2005 instances. It might help you see if you have registry entries pointing to the original file locations that SP2 is reading.

Vince



Post #357584
Posted Thursday, April 12, 2007 2:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 5:33 AM
Points: 307, Visits: 1,846

Thanks for the reply Vince.  I have sent you a pm hopefully this will help pinpoint the problem.

Have you successfully used your script to split the locations of the system db's & mssqlsystemresource.mdf & .ldf files and then tried to install SP2?  Was everything ok?

Thanks again for all your help.

Post #357739
Posted Tuesday, May 29, 2007 3:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 505, Visits: 1,691

I should point something out that has caused me grief.

Doing a test run on my desktop worked like a charm, but when it came time to run it on our production box, I discovered that x64 machines (ha ha!) have TWO copies of CMD.exe - one that recognises the 'NET' command and one that doesn't.  As a result, the script half executed and gave me a major problem.

Eventually I got everything back up, but am now faced with:
1) I cannot "ALTER DATABASE mssqlsystemresource" because that database "does not exist"; and
2) my master and mssqlsystemresource files are in different directories (which should not be the case.)

So, at the moment everything "works", but I'm just dreading the issues further down the track.

I would like some advise on how to fix the above two problems, if anyone can help.

Thanks,

S.

Post #369425
Posted Sunday, July 22, 2007 8:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 11, 2013 1:09 PM
Points: 80, Visits: 580

This is a issue that has been submitted to Microsoft connect.

The documentation states the resource database and the master database must be in the same folder.

What it does not say is the resource database log file must be in the master database folder.

So if you put log files on one disk and database files on another disk the install will fail unless you put a copy of the resource database log where the master database data file is. After it is updated you can just copy it back to the log file location and all is well.




Post #384009
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse