After restoring MASTER database, can't start SQLServer Service: Configuration block version 0 i

Now, I checked and verified that my backup version of SQL Server is the same as the version installed on the computer I'm restoring too.

I have SQL Server on a production machine that I backed up and want to test a full restore on a dev machine to make sure it will work when I need it to.

Now that I've run the restore command on my tape backup and go to restart the SQL server service I receive:

Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.

I'm afraid I don't understand why this is happening. If the builds are the same, then shouldn't restoring the MASTER database have worked normally and I'd be able to restart the service now?

Any thoughts or suggestions?

If the service cannot be started, you must rebuild the

system databases, adding information that has been lost by attaching existing tables and recreating

objects.

After this, Restoring the master database when SQL Server is accessible

With master database accessible, you should start SQL Server in single-user mode and then restore your copy of the

master database from your most recent full database backup in the usual way, as described in the

following steps.

#1 - Start SQL Server in single-user mode.

with command:

sqlservr.exe -c –m

#2 - Restore the master database from the most recent backup:

RESTORE DATABASE master

FROM masterbackup

If Master isn′t accessible:

To rebuild the master database, you should run the SQL Server setup program with the following

options:

? The /qn switch to suppress the user interface.

? The REINSTALLMODE = AMUS property to rebuild system databases.

? The REINSTALL = ALL property to set up the server with the previously installed features.

This must be used when specifying the REINSTALLMODE property.

|||

I tried rebuilding using this command:

start /wait d:\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=YourPassword

However, you are saying those were not the proper commands and switches to use during the reinstallation?

(Also I didn't want to specify an SA account and password, I wanted to use windows authentication but didn't see an option for that).

|||

To use Windows authentication, you need use this parameters:

start /wait d:\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1

SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword>

|||

Thanks.

Anyway, I tried rebuilding and that DID work.

But when I restored the master database, I was back where I started.

I KNOW the database installations are the same version as I did SELECT @.@.VERSION on both and they match.

So I don't understand why restoring a tape from one server to the other causes the other server to throw this error when the versions of the server do appear to be the same.

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right? if i'm right, anything i can do to resolve this problem
thanks for any reply
Rico
Problems about orphaned users ?
http://msdn.microsoft.com/library/de...tabse_0ttf.asp
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> glsD:uKfxmfhWFHA.3584@.TK2MSFTNGP14.phx.g bl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon fai
led
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right' if i'm right, anything i can do to resolve this problem
thanks for any reply
RicoProblems about orphaned users ?
http://msdn.microsoft.com/library/d...
tabse_0ttf.asp
http://vyaskn.tripod.com/troublesho...rphan_users.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon f
ailed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon f
ailed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> glsD:uKfxmfhWFHA.3584@.TK2MSFTNGP14.p
hx.gbl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>

After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right' if i'm right, anything i can do to resolve this problem
thanks for any reply
RicoProblems about orphaned users ?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_0ttf.asp
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right' if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> ¼¶¼g©ó¶l¥ó·s»D:uKfxmfhWFHA.3584@.TK2MSFTNGP14.phx.gbl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>> Dear all,
>> My Lab has one SQL Server which used by ApplicationA, during
>> the installation process of ApplicationA, ApplicationA will
>> add New Logins for one local user group, and add one domain
>> useraccount to be a member of this local user group, i want
>> to simulate disaster rescovery of ApplicationA, so i do the
>> steps listed bellow:
>> (1) backup all related SQL database, inclued Master DB Full Backup
>> (2) Install one new Windows 2003 Server
>> (3) create local user group used by ApplicationA
>> (4) Install SQL Server and SP3a in this new Server, and every
>> installation options is the same as old server
>> (5) Install ApplicationA
>> (6) Restore all Database from backup media
>> (7) make sure no error happend when restore
>> after restore database, domain user account which is a member
>> of that local user group can't logon go SQL Server, when use SQL
>> Query Analyzer, error message appear:
>> can't connect to server SQLServerName:
>> Server:Message18456,Level16,Status1
>> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
>> if i delete SQL logins for that local user group, and then new logins
>> for that local user group, the domain user account can logon
>> successfully,
>> so i think although the name of local user gorup is the same, but
>> the sid of local user gorup is different, because this is the new Windows
>> 2003 Server.
>> Am i right' if i'm right, anything i can do to resolve this problem
>> thanks for any reply
>> Rico
>

After Master is restored...

After restoring Master, is it necessary to retore Model and MSDB?MSDB will bring your Jobs, DTS Packages, etc. No on Model.
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106595295.357042.246450@.f14g2000cwb.googlegroups.com...
> After restoring Master, is it necessary to retore Model and MSDB?
>

After Master is restored...

After restoring Master, is it necessary to retore Model and MSDB?MSDB will bring your Jobs, DTS Packages, etc. No on Model.
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106595295.357042.246450@.f14g2000cwb.googlegroups.com...
> After restoring Master, is it necessary to retore Model and MSDB?
>

After Master is restored...

After restoring Master, is it necessary to retore Model and MSDB?
MSDB will bring your Jobs, DTS Packages, etc. No on Model.
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106595295.357042.246450@.f14g2000cwb.googlegr oups.com...
> After restoring Master, is it necessary to retore Model and MSDB?
>

After Master is restored

After Master is restored, is it necessary to also restore Model and
MSDB?
Model - only if you changed the original version.
MSDB - Yes (if you want to recover your scheduled jobs).
Keith
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegr oups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>
|||Are you only talking about restore of master or did you also do a rebuildm.exe?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegr oups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>

After Master is restored

After restoring Master, is it ALWAYS necessary to also restore MSDB?
Message posted via http://www.sqlmonster.com
Robert
No it is not always necessary, it depends on what you are doing. If for
instance you had a corrupt master database for some reason, but your other
databases were fine then there would be no need.
On the other hand if you were moving all your databases from one server to
another, you would want to restore msdb, as long as you were using it. (had
some Jobs, alerts or operators on it).
I would say only restore what you need to.
Hope this helps
John
"Robert Richards via SQLMonster.com" wrote:

> After restoring Master, is it ALWAYS necessary to also restore MSDB?
> --
> Message posted via http://www.sqlmonster.com
>

After Master is restored

After restoring Master, is it ALWAYS necessary to also restore MSDB?
Message posted via http://www.droptable.comRobert
No it is not always necessary, it depends on what you are doing. If for
instance you had a corrupt master database for some reason, but your other
databases were fine then there would be no need.
On the other hand if you were moving all your databases from one server to
another, you would want to restore msdb, as long as you were using it. (had
some Jobs, alerts or operators on it).
I would say only restore what you need to.
Hope this helps
John
"Robert Richards via droptable.com" wrote:

> After restoring Master, is it ALWAYS necessary to also restore MSDB?
> --
> Message posted via http://www.droptable.com
>

After Master is restored

After Master is restored, is it necessary to also restore Model and
MSDB?Model - only if you changed the original version.
MSDB - Yes (if you want to recover your scheduled jobs).
Keith
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegroups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>|||Are you only talking about restore of master or did you also do a rebuildm.e
xe?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegroups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>

After Master is restored

After Master is restored, is it necessary to also restore Model and
MSDB?Model - only if you changed the original version.
MSDB - Yes (if you want to recover your scheduled jobs).
--
Keith
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegroups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>|||Are you only talking about restore of master or did you also do a rebuildm.exe?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rerichards" <cbrichards@.comcast.net> wrote in message
news:1106597321.615587.143300@.c13g2000cwb.googlegroups.com...
> After Master is restored, is it necessary to also restore Model and
> MSDB?
>

After Master is restored

After restoring Master, is it ALWAYS necessary to also restore MSDB?
--
Message posted via http://www.sqlmonster.comRobert
No it is not always necessary, it depends on what you are doing. If for
instance you had a corrupt master database for some reason, but your other
databases were fine then there would be no need.
On the other hand if you were moving all your databases from one server to
another, you would want to restore msdb, as long as you were using it. (had
some Jobs, alerts or operators on it).
I would say only restore what you need to.
Hope this helps
John
"Robert Richards via SQLMonster.com" wrote:
> After restoring Master, is it ALWAYS necessary to also restore MSDB?
> --
> Message posted via http://www.sqlmonster.com
>

After creating database, running script to create tables?

I am in my apps master database and I have a Proc to create a project database. However, once that database is created, I have a long script that I need to run to create the tables, indexes, and views.

What is the best way to run this?

I'd intended to just make this part of my stored proc -- create database, then tables, then views. However, to simply create the database, I had to build a string to concatenate the passed in value of the new database. I'd hate to have to do this for every line of this script.

Should I simply take this script as is and create it as a stored proc in the new database and then run that proc?Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman

Yes. I'm trying to allow admin users on our web app the ability to create new projects on the fly. Part of this is creating the new database.|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman

Bummer: Server: Msg 154, Level 15, State 1, Procedure CreateclaimDexDB_SP, Line 22
a USE database statement is not allowed in a procedure or trigger.|||No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.

Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.

I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.

It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.

Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.

...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.

blindman|||Originally posted by blindman
No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.

Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.

I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.

It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.

Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.

...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.

blindman

I appreciate your comments, but in this case, separate databases is the right way to go. They truly are for distinct projects, clients, etc. and CAN be scaled across different servers if need be. In addition, some tables will start out the same, but can be altered by each client independently. This is tying into another existing application.

When I said master DB, I forgot there was a SQL database called master. I meant the main application database, which keeps track of the different projects, users, clients, etc.

As a general rule, I would agree with you, but in this case, we're doing the right thing.