SQL Clone
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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



vince.iacoboni@db.com
vince.iacoboni@db.com
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 560
The silence is deafening :-) ....



Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117334 Visits: 9672
Can't comment on this... I just started developping on 2005 .
colin.Leversuch-Roberts
colin.Leversuch-Roberts
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: 22083 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 560
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 Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 560
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 428

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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 560
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 560
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