Restrict User Access in MSSQL 2000

Hi,

I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.

Thanks & Regards,

Gaurav

You cannot restrict the users in SQL Server 2000 from seeing the available databases (this can be done in SQL Server 2005 by revoking VIEW ANY DATABASE to the public server role).

Also, a database owner will be able to change the file size for the databases he owns; you cannot prevent this.

Thanks
Laurentiu

restrict enterprise manager to see all the databases

Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.
Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegr oups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

restrict enterprise manager to see all the databases

Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> > Hi,
> >
> > my question is about security, but I am sure it has something to do
> > with the server configuration. It is about normal users other than SA.
> >
> > In the server, multiple users, and each user has ownership of a
> > seperate database. When any of these users connect to the sql server
> > via Enterprise Manager, they are able to see all the databases, not
> > only they owned, also other databases they do not have ownership of.
> >
> > Not that, they can see the tables or the data, but this still
> > bothers the user(s). How can I restrict the normal users to see only
> > their database(s) when they logged in thru enterprise manager with
> > their username and password ?
> >
> > I aplogize if this quesiton has asked before and answered. If so,
> > please direct me to the correct link, I could not seek it.
> >
> > Thank you.
> >
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

restrict enterprise manager to see all the databases

Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Restoring the Northwind and Pubs databases

Is there an easy way to get Northwind and Pubs back to their default state,
assuming that you were too stupid to back them up before you started mucking
around with them? Thanks.You can recreate the two databases from the two scripts: instpubs.sql, and
instnwnd.sql from the \install folder of your SQL Server installation or
the SQL Server CD.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Alternatively, you can try attach Nothwind and pubs databases from the SQL
2K CD:
x86\DATA\northwnd.mdf
x86\DATA\northwnd.ldf
x86\DATA\pubs.mdf
x86\DATA\pubs_log.ldf
Richard
"Garrett Fitzgerald" <gfitzger@.nyx.net> wrote in message
news:uO$K4EbQDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get Northwind and Pubs back to their default
state,
> assuming that you were too stupid to back them up before you started
mucking
> around with them? Thanks.
>
>|||Alan,
>> You also have to run a batch utility...
Thanks Alan. I wasnt aware of that.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:uwBiD8kQDHA.1564@.TK2MSFTNGP12.phx.gbl...
> You also have to run a batch utility to fully populate the data in pubs.
> Northwind can be resinstalled by just using the instnwnd.sql script.
> The processes to create pubs and Northwind are covered in these
Transact-SQL
> Reference topics:
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_pubs_2v8l.asp?frame=true
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
> --
> Alan Brewer [MSFT]
> Lead Technical Writer
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe HernandezMy guess is that the master database you have restored specifies that tempdb
and/or model should be
located on some other place compared to where they are on your restored syst
em.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequent
ly
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterpris
e
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service righ
t
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez|||Please look at the error log , what error you found in it?
Regards
Amish|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:

> Please look at the error log , what error you found in it?
> Regards
> Amish
>|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:

> My guess is that the master database you have restored specifies that temp
db and/or model should be
> located on some other place compared to where they are on your restored sy
stem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>|||Check sysdatabases on the old installation for the original path for those d
atabases. Or the
errorlog file. You can then create a directory as the original and have copy
the files of those
databases so that they exist in the original location. Then use KB 224071 to
move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...[vbcol=seagreen]
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:

> Check sysdatabases on the old installation for the original path for those
databases. Or the
> errorlog file. You can then create a directory as the original and have co
py the files of those
> databases so that they exist in the original location. Then use KB 224071
to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>|||For 2000, the instance name is part for the directory structure for SQL Serv
er. So, it will probably
be easier of you have the same instance name, more likely that you will have
the same directory
structure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as wel
l
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
>|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off fro
m
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:

> For 2000, the instance name is part for the directory structure for SQL Se
rver. So, it will probably
> be easier of you have the same instance name, more likely that you will ha
ve the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
>

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe Hernandez
My guess is that the master database you have restored specifies that tempdb and/or model should be
located on some other place compared to where they are on your restored system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequently
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterprise
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service right
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez
|||Please look at the error log , what error you found in it?
Regards
Amish
|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:

> Please look at the error log , what error you found in it?
> Regards
> Amish
>
|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:

> My guess is that the master database you have restored specifies that tempdb and/or model should be
> located on some other place compared to where they are on your restored system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>
|||Check sysdatabases on the old installation for the original path for those databases. Or the
errorlog file. You can then create a directory as the original and have copy the files of those
databases so that they exist in the original location. Then use KB 224071 to move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...[vbcol=seagreen]
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:

> Check sysdatabases on the old installation for the original path for those databases. Or the
> errorlog file. You can then create a directory as the original and have copy the files of those
> databases so that they exist in the original location. Then use KB 224071 to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>
|||For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
be easier of you have the same instance name, more likely that you will have the same directory
structure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as well
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish
|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off from
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:

> For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
> be easier of you have the same instance name, more likely that you will have the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
>

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe HernandezMy guess is that the master database you have restored specifies that tempdb and/or model should be
located on some other place compared to where they are on your restored system.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequently
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterprise
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service right
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez|||Please look at the error log , what error you found in it?
Regards
Amish|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:
> Please look at the error log , what error you found in it?
> Regards
> Amish
>|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:
> My guess is that the master database you have restored specifies that tempdb and/or model should be
> located on some other place compared to where they are on your restored system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> > Hello,
> >
> > I am going throught my disaster recovery. I am at the point where I have a
> > nightly databases backup and I am backing up the transaction logs frequently
> > during the day.
> >
> > I am having the problem that i am trying to restore the backedup master
> > database, but every time I restore it, I cannot get SQL to run again, so I
> > have to re-build the master database. I have tried doing it with Enterprise
> > manager and with Querry analizer, in both cases I get the message 'master
> > database succesfully recover and then it is shutting donw the service right
> > away, at this point I cannot re-start the service, am I missing something?
> >
> > Please help
> >
> > Joe Hernandez
>|||Check sysdatabases on the old installation for the original path for those databases. Or the
errorlog file. You can then create a directory as the original and have copy the files of those
databases so that they exist in the original location. Then use KB 224071 to move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
>> My guess is that the master database you have restored specifies that tempdb and/or model should
>> be
>> located on some other place compared to where they are on your restored system.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>> > Hello,
>> >
>> > I am going throught my disaster recovery. I am at the point where I have a
>> > nightly databases backup and I am backing up the transaction logs frequently
>> > during the day.
>> >
>> > I am having the problem that i am trying to restore the backedup master
>> > database, but every time I restore it, I cannot get SQL to run again, so I
>> > have to re-build the master database. I have tried doing it with Enterprise
>> > manager and with Querry analizer, in both cases I get the message 'master
>> > database succesfully recover and then it is shutting donw the service right
>> > away, at this point I cannot re-start the service, am I missing something?
>> >
>> > Please help
>> >
>> > Joe Hernandez
>>|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:
> Check sysdatabases on the old installation for the original path for those databases. Or the
> errorlog file. You can then create a directory as the original and have copy the files of those
> databases so that they exist in the original location. Then use KB 224071 to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
> >I dont know how would I tell that.
> >
> > So do I need to restore the tempdb and the model before I restore the
> > masterdb?
> >
> > thanks,
> >
> > Joe H
> >
> > "Tibor Karaszi" wrote:
> >
> >> My guess is that the master database you have restored specifies that tempdb and/or model should
> >> be
> >> located on some other place compared to where they are on your restored system.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> >> > Hello,
> >> >
> >> > I am going throught my disaster recovery. I am at the point where I have a
> >> > nightly databases backup and I am backing up the transaction logs frequently
> >> > during the day.
> >> >
> >> > I am having the problem that i am trying to restore the backedup master
> >> > database, but every time I restore it, I cannot get SQL to run again, so I
> >> > have to re-build the master database. I have tried doing it with Enterprise
> >> > manager and with Querry analizer, in both cases I get the message 'master
> >> > database succesfully recover and then it is shutting donw the service right
> >> > away, at this point I cannot re-start the service, am I missing something?
> >> >
> >> > Please help
> >> >
> >> > Joe Hernandez
> >>
> >>
>|||For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
be easier of you have the same instance name, more likely that you will have the same directory
structure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as well
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
>> Check sysdatabases on the old installation for the original path for those databases. Or the
>> errorlog file. You can then create a directory as the original and have copy the files of those
>> databases so that they exist in the original location. Then use KB 224071 to move those databases
>> to
>> the desired location. Some articles that might be helpful:
>> Have a look at this list compiled by Andrew Kelly:
>> Moving DB's between Servers
>> http://www.support.microsoft.com/?id=314546
>> Moving SQL Server Databases to a New Location with Detach/Attach
>> http://www.support.microsoft.com/?id=224071
>> Using WITH MOVE in a Restore
>> http://support.microsoft.com/?id=221465
>> How To Transfer Logins and Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=246133
>> Mapping Logins & SIDs after a Restore
>> http://www.support.microsoft.com/?id=298897
>> Utility to map logins to users
>> http://www.dbmaint.com/SyncSql Logins.asp
>> User Logon and/or Permission Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=168001
>> How to Resolve Permission Issues When a Database Is Moved Between SQL
>> Servers
>> http://www.support.microsoft.com/?id=240872
>> Restoring a .mdf
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Disaster Recovery Articles for SQL Server
>> http://www.support.microsoft.com/?id=307775
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>> >I dont know how would I tell that.
>> >
>> > So do I need to restore the tempdb and the model before I restore the
>> > masterdb?
>> >
>> > thanks,
>> >
>> > Joe H
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> My guess is that the master database you have restored specifies that tempdb and/or model
>> >> should
>> >> be
>> >> located on some other place compared to where they are on your restored system.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I am going throught my disaster recovery. I am at the point where I have a
>> >> > nightly databases backup and I am backing up the transaction logs frequently
>> >> > during the day.
>> >> >
>> >> > I am having the problem that i am trying to restore the backedup master
>> >> > database, but every time I restore it, I cannot get SQL to run again, so I
>> >> > have to re-build the master database. I have tried doing it with Enterprise
>> >> > manager and with Querry analizer, in both cases I get the message 'master
>> >> > database succesfully recover and then it is shutting donw the service right
>> >> > away, at this point I cannot re-start the service, am I missing something?
>> >> >
>> >> > Please help
>> >> >
>> >> > Joe Hernandez
>> >>
>> >>
>>|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off from
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:
> For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
> be easier of you have the same instance name, more likely that you will have the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
> > Tibor,
> >
> > Thanks for all your help.
> >
> > Do you know if it makes a difference if I am using 2 different instaces
> > names? should I have the same instance name on my production server as well
> > as my test server?
> >
> > Thanks,
> >
> > Joe Hernandez
> >
> > "Tibor Karaszi" wrote:
> >
> >> Check sysdatabases on the old installation for the original path for those databases. Or the
> >> errorlog file. You can then create a directory as the original and have copy the files of those
> >> databases so that they exist in the original location. Then use KB 224071 to move those databases
> >> to
> >> the desired location. Some articles that might be helpful:
> >>
> >> Have a look at this list compiled by Andrew Kelly:
> >>
> >> Moving DB's between Servers
> >> http://www.support.microsoft.com/?id=314546
> >>
> >> Moving SQL Server Databases to a New Location with Detach/Attach
> >> http://www.support.microsoft.com/?id=224071
> >>
> >> Using WITH MOVE in a Restore
> >> http://support.microsoft.com/?id=221465
> >>
> >> How To Transfer Logins and Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=246133
> >>
> >> Mapping Logins & SIDs after a Restore
> >> http://www.support.microsoft.com/?id=298897
> >>
> >> Utility to map logins to users
> >> http://www.dbmaint.com/SyncSql Logins.asp
> >>
> >> User Logon and/or Permission Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=168001
> >>
> >> How to Resolve Permission Issues When a Database Is Moved Between SQL
> >> Servers
> >> http://www.support.microsoft.com/?id=240872
> >>
> >> Restoring a .mdf
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >>
> >> Disaster Recovery Articles for SQL Server
> >> http://www.support.microsoft.com/?id=307775
> >>
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
> >> >I dont know how would I tell that.
> >> >
> >> > So do I need to restore the tempdb and the model before I restore the
> >> > masterdb?
> >> >
> >> > thanks,
> >> >
> >> > Joe H
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> My guess is that the master database you have restored specifies that tempdb and/or model
> >> >> should
> >> >> be
> >> >> located on some other place compared to where they are on your restored system.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> >> >> > Hello,
> >> >> >
> >> >> > I am going throught my disaster recovery. I am at the point where I have a
> >> >> > nightly databases backup and I am backing up the transaction logs frequently
> >> >> > during the day.
> >> >> >
> >> >> > I am having the problem that i am trying to restore the backedup master
> >> >> > database, but every time I restore it, I cannot get SQL to run again, so I
> >> >> > have to re-build the master database. I have tried doing it with Enterprise
> >> >> > manager and with Querry analizer, in both cases I get the message 'master
> >> >> > database succesfully recover and then it is shutting donw the service right
> >> >> > away, at this point I cannot re-start the service, am I missing something?
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Joe Hernandez
> >> >>
> >> >>
> >>
> >>
>

restoring system databases

I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before[vbcol=seagreen]
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?[vbcol=seagreen]
>sql

restoring system databases

I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?
If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>
|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with[vbcol=seagreen]
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
the[vbcol=seagreen]
and[vbcol=seagreen]
before
>
|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> with
> the
> and
> before
>
|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it[vbcol=seagreen]
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
disappear?
>

restoring system databases

I've been testing a new 2005 server. After the install, I backed up the
system databases. I've done a lot of junk testing stuff since then, and
now I'm wondering if I should do a restore of the system databases before I
put the machine into production. If so, which ones?If you want a clean start, consider running rebuildmaster - rerun setup with
rebuild flag.
e.g.
setup.exe REBUILDDATABASE=1
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> I've been testing a new 2005 server. After the install, I backed up the
> system databases. I've done a lot of junk testing stuff since then, and
> now I'm wondering if I should do a restore of the system databases before
> I
> put the machine into production. If so, which ones?
>|||Does that put it into the same state as just after an install?
If I had a test database still showing, for example, would that disappear?
"oj" <nospam_ojngo@.home.com> wrote in message
news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> If you want a clean start, consider running rebuildmaster - rerun setup
with
> rebuild flag.
> e.g.
> setup.exe REBUILDDATABASE=1
>
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> > I've been testing a new 2005 server. After the install, I backed up
the
> > system databases. I've done a lot of junk testing stuff since then,
and
> > now I'm wondering if I should do a restore of the system databases
before
> > I
> > put the machine into production. If so, which ones?
> >
> >
>|||Correct. The system would look as if it's just installed.
Since the setup does not touch your user database, you can just reattach it
after the rebuild.
--
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> Does that put it into the same state as just after an install?
> If I had a test database still showing, for example, would that disappear?
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
>> If you want a clean start, consider running rebuildmaster - rerun setup
> with
>> rebuild flag.
>> e.g.
>> setup.exe REBUILDDATABASE=1
>>
>> --
>> -oj
>>
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
>> > I've been testing a new 2005 server. After the install, I backed up
> the
>> > system databases. I've done a lot of junk testing stuff since then,
> and
>> > now I'm wondering if I should do a restore of the system databases
> before
>> > I
>> > put the machine into production. If so, which ones?
>> >
>> >
>>
>|||That's a neat trick. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OgXKirUVGHA.5900@.tk2msftngp13.phx.gbl...
> Correct. The system would look as if it's just installed.
> Since the setup does not touch your user database, you can just reattach
it
> after the rebuild.
> --
> -oj
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:0SnXf.18403$WK1.7806@.tornado.socal.rr.com...
> > Does that put it into the same state as just after an install?
> >
> > If I had a test database still showing, for example, would that
disappear?
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:e2GjwiUVGHA.5364@.tk2msftngp13.phx.gbl...
> >> If you want a clean start, consider running rebuildmaster - rerun setup
> > with
> >> rebuild flag.
> >>
> >> e.g.
> >> setup.exe REBUILDDATABASE=1
> >>
> >>
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "HK" <replywithingroup@.notreal.com> wrote in message
> >> news:BceXf.17733$WK1.10563@.tornado.socal.rr.com...
> >> > I've been testing a new 2005 server. After the install, I backed up
> > the
> >> > system databases. I've done a lot of junk testing stuff since then,
> > and
> >> > now I'm wondering if I should do a restore of the system databases
> > before
> >> > I
> >> > put the machine into production. If so, which ones?
> >> >
> >> >
> >>
> >>
> >
> >
>

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
..
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
..
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we know
that we could not recovery up to the point of failure since the current logs
would also be lost.
..
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
..
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
..
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
..
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
..
Any feedback on the proper procedure to restore the System Databases are
welcome
..
Thanks in advance
Here's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:

> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance
|||Thanks for the info.
..
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
[vbcol=seagreen]
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server perform a restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance
|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
TheSQLGuru
President
Indicium Resources, Inc.

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
.
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
.
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we know
that we could not recovery up to the point of failure since the current logs
would also be lost.
.
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
.
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
.
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
.
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
.
Any feedback on the proper procedure to restore the System Databases are
welcome
.
Thanks in advanceHere's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic :) (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||Thanks for the info.
.
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic :) (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
> > We are using SQL Server 2000, all the System and User Databases and Logs are
> > on the same RAID-5 disks. I know this might not be recommended but this is a
> > fairly small system the Database is about 5G. And that's the only disk space
> > we have.
> > .
> > We do a complete backup of the System and User databases every night
> > We also backup the Logs from the User databases every night on tape.
> > .
> > If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> > then we would have to restore the System and User databases. The User
> > databases would be a normal DB restore + the Transaction logs backup, we know
> > that we could not recovery up to the point of failure since the current logs
> > would also be lost.
> > .
> > But what would be the steps to restore the System Databases, ex: Master ?
> > I was reading some of the notes and they metionned that we would have to do
> > a startup in single user mode, then restore the Master datafile.
> > So as a test I shut down the servive, renamed the Master DB and logs, tried
> > to restart the service, it failed which is expected. Then tried to start
> > using
> > sqlservr.exe -c -m, but this also failed with error opening master.mdf
> > .
> > So if the master file and logs are gone, do I have to use rebuildm.exe
> > first,then resotre the master backup, or did I miss a step ?
> > .
> > Also as another test I rename the model datafile and it's log,when I use
> > sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> > and other databases were started except for model, but when I tried to
> > access SQL Analyzer I kept getting an error SQL server does not exist or
> > access denied
> > .
> > Basically I'm trying to test our recover strategy if we loose the RAID,
> > which would mean that we loose all datafiles and logs and would have to
> > recover from the backup done the night before.
> > .
> > Any feedback on the proper procedure to restore the System Databases are
> > welcome
> > .
> > Thanks in advance|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server perform a restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs are
> on the same RAID-5 disks. I know this might not be recommended but this is a
> fairly small system the Database is about 5G. And that's the only disk space
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we know
> that we could not recovery up to the point of failure since the current logs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to do
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, tried
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
--
TheSQLGuru
President
Indicium Resources, Inc.

Restoring System and User DBs

We are using SQL Server 2000, all the System and User Databases and Logs are
on the same RAID-5 disks. I know this might not be recommended but this is a
fairly small system the Database is about 5G. And that's the only disk space
we have.
.
We do a complete backup of the System and User databases every night
We also backup the Logs from the User databases every night on tape.
.
If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
then we would have to restore the System and User databases. The User
databases would be a normal DB restore + the Transaction logs backup, we kno
w
that we could not recovery up to the point of failure since the current logs
would also be lost.
.
But what would be the steps to restore the System Databases, ex: Master ?
I was reading some of the notes and they metionned that we would have to do
a startup in single user mode, then restore the Master datafile.
So as a test I shut down the servive, renamed the Master DB and logs, tried
to restart the service, it failed which is expected. Then tried to start
using
sqlservr.exe -c -m, but this also failed with error opening master.mdf
.
So if the master file and logs are gone, do I have to use rebuildm.exe
first,then resotre the master backup, or did I miss a step ?
.
Also as another test I rename the model datafile and it's log,when I use
sqlservr.exe -c -m to start the sqlserver, the log indicated that the master
and other databases were started except for model, but when I tried to
access SQL Analyzer I kept getting an error SQL server does not exist or
access denied
.
Basically I'm trying to test our recover strategy if we loose the RAID,
which would mean that we loose all datafiles and logs and would have to
recover from the backup done the night before.
.
Any feedback on the proper procedure to restore the System Databases are
welcome
.
Thanks in advanceHere's a good starting point for restoring system DBs:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Master remembers where all of the databases are, so after restoring it you
may see user databases show up as Suspect. Don't Panic (Thank you Doug
Adams for that lovely phrase)
"SQL Server newbie" wrote:

> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||Thanks for the info.
.
But my question remains, if the master.mdf and it's log are removed, can we
just restore from a previous backup or we have to rebuilt it first using
rebuilm.exe
and then restore.
Thanks
"James Luetkehoelter" wrote:
[vbcol=seagreen]
> Here's a good starting point for restoring system DBs:
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Master remembers where all of the databases are, so after restoring it you
> may see user databases show up as Suspect. Don't Panic (Thank you Doug
> Adams for that lovely phrase)
> "SQL Server newbie" wrote:
>|||> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup,
Correct. If you can't start SQL Server, it is difficult to have SQL Server p
erform a restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in mes
sage
news:94A87069-D833-4C84-8765-DF0D843A5718@.microsoft.com...
> We are using SQL Server 2000, all the System and User Databases and Logs a
re
> on the same RAID-5 disks. I know this might not be recommended but this is
a
> fairly small system the Database is about 5G. And that's the only disk spa
ce
> we have.
> .
> We do a complete backup of the System and User databases every night
> We also backup the Logs from the User databases every night on tape.
> .
> If we loose a disk, it's shouldn't be a problem but if we loose the Raid,
> then we would have to restore the System and User databases. The User
> databases would be a normal DB restore + the Transaction logs backup, we k
now
> that we could not recovery up to the point of failure since the current lo
gs
> would also be lost.
> .
> But what would be the steps to restore the System Databases, ex: Master ?
> I was reading some of the notes and they metionned that we would have to d
o
> a startup in single user mode, then restore the Master datafile.
> So as a test I shut down the servive, renamed the Master DB and logs, trie
d
> to restart the service, it failed which is expected. Then tried to start
> using
> sqlservr.exe -c -m, but this also failed with error opening master.mdf
> .
> So if the master file and logs are gone, do I have to use rebuildm.exe
> first,then resotre the master backup, or did I miss a step ?
> .
> Also as another test I rename the model datafile and it's log,when I use
> sqlservr.exe -c -m to start the sqlserver, the log indicated that the mast
er
> and other databases were started except for model, but when I tried to
> access SQL Analyzer I kept getting an error SQL server does not exist or
> access denied
> .
> Basically I'm trying to test our recover strategy if we loose the RAID,
> which would mean that we loose all datafiles and logs and would have to
> recover from the backup done the night before.
> .
> Any feedback on the proper procedure to restore the System Databases are
> welcome
> .
> Thanks in advance|||The SQL Server BOL have explicit steps for restoring all system databases,
including after a severe master failure. There are a number of SQL 2000 DBA
books that walk you through it too. I also recommend having an expert
either assist or at least review your disaster recovery plan. Holy Sh-t
time is NOT when you want to find out you don't have something set
up/documented correctly. :-)
TheSQLGuru
President
Indicium Resources, Inc.sql

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.
> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
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/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> > Can I use the restore command if I just have an .mdf file?
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases on
> > the SAN were marked "suspect". I've been able to reboot the sql server box in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have to
> > restart sql server. The last backup that was done was file backup by Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> 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/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases
> > on
> > the SAN were marked "suspect". I've been able to reboot the sql server box
> > in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have
> > to
> > restart sql server. The last backup that was done was file backup by
> > Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that
> > I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and
> > creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>
>