Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving the SQL 2005 System Databases


Moving the SQL 2005 System Databases

Author
Message
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
Comments posted to this topic are about the content posted at temp



vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
The silence is deafening :-) ....



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22913 Visits: 9671
Can't comment on this... I just started developping on 2005 .
colin.Leversuch-Roberts
colin.Leversuch-Roberts
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3185 Visits: 715
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.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
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



TF-245998
TF-245998
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 62

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.


vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
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



chudman
chudman
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 400

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





vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
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



vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 550
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search