Moving the SQL 2005 System Databases
-
Comments posted to this topic are about the content posted at temp
-
The silence is deafening 🙂 ....
-
Can't comment on this... I just started developping on 2005
. -
It's a shame it writes to the registry, couldn't really use this in a controlled or production environment.. I slightly worry that this may prompt the unwary to start making changes they wouldn't normally consider.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
Thanks for the feedback. If you would, I'd appreciate some detail on your concerns.
Why would the utility not be able to be used in a controlled environment? Because xp_regwrite would not be accessable? If the system tables are to be moved, some utility (such as Microsoft SQL Server Configuration Manager) will need to write this same value to the registry, yes?
In order to automate the process, either xp_regwrite can be used, or some other way to update the registry can be. Whether that is REG.EXE, a REGEDIT input file, or direct API calls (perhaps through Perl or VB), a way to write to the registry gets exposed. I take it that your concern is that a SQL-familiar audience that may have been intimidated by API calls or unaware of registry-writing techniques has now found a way to write to the registry using a language they are familiar with.
My opinion is that most SQL programmers are familiar with either xp_regwrite or other techniques for updating the registry. The warnings about causing damage with direct registry writes abound and should be heeded, but that should not stop legitimate uses such as the utility I presented.
Again, though, thanks for your feedback. It gives me something to think about for the future.
Vince
-
Worked good, after figuring out not to use quotes for the input variables, and then a problem encountered when the sqlcmd couldn't log on:
Moving files...
Restarting service with /f and trace flag 3608
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service was started successfully.
Msg 18461, Level 14, State 1, Server L00013756, Line 1
Login failed for user 'dommainname\username(hidden)'. Reason: Server is in single user mode. Only one administrator can connect at this time.
Msg 18461, Level 14, State 1, Server L00013756, Line 1
Login failed for user 'dommainname\username(hidden)'. Reason: Server is in single user mode. Only one administrator can connect at this time.
Msg 18461, Level 14, State 1, Server L00013756, Line 1
Login failed for user 'dommainname\username(hidden)'. Reason: Server is in single user mode. Only one administrator can connect at this time.
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
Restarting service MSSQLSERVER in normal mode
The SQL Server (MSSQLSERVER) service is starting.
Verifying new location of system databases...
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
I started it back up using the net start /F /T3608 flags, ran the 'ALTER DATABASE mssqlsystemresource' scripts after logging on cmd line with sqlcmd. Then it was all good.
I also like the LOG directory to be in similar location, but that was easily changed.
-
TF,
I'm guessing you had some other connect that grabbed the only server connection when it was in single-user mode. I would suggest that for next time you should disable any programs that attempt to make connections to the database.
Vince
-
This is an outstanding article. I will be using this set of scripts very heavily in the future.
Thanks for taking the time to not only write the scripts, but to document them with your article.
Jeff Bennett , St. Louis, Mo
-
Jeff,
Thanks for the feedback. We've already used the script a number of times internally, so I was puzzled with the lack of response to the article. I'm glad you've found it useful also.
Vince
-
Jeff,
Thanks for the feedback. We've already used the script a number of times internally, so I was puzzled with the lack of response to the article. I'm glad you've found it useful also.
Vince
-
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
-
Jim,
Thanks, we unfortunately don't have RS running so I didn't think of that.
Vince
-
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
-
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
-
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
Viewing 15 posts - 1 through 15 (of 75 total)
You must be logged in to reply to this topic. Login to reply