Restoring/moving model?

Hi
I want to alter Model so that its transaction logs and
datafiles are on different drives,so that new dbs created
on this server get the same configuration.
When I try to restore Model with a with_move option it
says I can't do that.
How can I acheive what I want?
Would remaning Model, and the creating a new db called
Model in the config I want work?Have a look at this old post :-
http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=co4joN3eCHA.1308%40cpmsftngxa08
--
HTH
Ryan Waight, MCDBA, MCSE
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||To move the model database, SQL Server must be started
with trace flag 3608 so that
it does not recover any database except
the master.
NOTE: You will not be able to access any user databases at
this time.
You should not perform any operations
other than the steps below while using
this trace flag.
To add trace flag 3608 as a SQL Server startup
parameter:
After adding trace flag 3608, perform the following
steps:
1. Stop and restart SQL Server.
2. Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7
\Data to
E:\Sqldata(or any other drives).
4. Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\mod
ellog.ldf'
go
--e:\ or any other drives
5. Remove the -T3608 trace flag from the startup
parameters box in the
Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change
in
file locations using
sp_helpfile:
use model
go
sp_helpfile
go
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi
>I want to alter Model so that its transaction logs and
>datafiles are on different drives,so that new dbs created
>on this server get the same configuration.
>When I try to restore Model with a with_move option it
>says I can't do that.
>How can I acheive what I want?
>Would remaning Model, and the creating a new db called
>Model in the config I want work?
>.
>|||To add to the other responses, the default location of new database data
and log files is not determined by the model database file locations.
The default file locations can be specified via Enterprise Manager under
server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||Thanks Dan,
Much more simple than I thought then! Your suggestion
worked a treat.
Felix
>--Original Message--
>To add to the other responses, the default location of
new database data
>and log files is not determined by the model database
file locations.
>The default file locations can be specified via
Enterprise Manager under
>server properties --> database settings.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"felix" <felix@.hotmail.com> wrote in message
>news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
>> Hi
>> I want to alter Model so that its transaction logs and
>> datafiles are on different drives,so that new dbs
created
>> on this server get the same configuration.
>> When I try to restore Model with a with_move option it
>> says I can't do that.
>> How can I acheive what I want?
>> Would remaning Model, and the creating a new db called
>> Model in the config I want work?
>
>.
>

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 SQL 6.5 DBs

Hi
I am trying to restore several databases to a single server due to restricti
ons on hardware. Each server had previously had a database that was named ex
actly the same on each server. Therefore, the new instances will be appended
with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created
a new database and named it accordingly. I have a backup of the database I
want to restore to this server. When I try and restore it by entering the pa
th of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh

Restoring SQL 6.5 DBs

H
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix to distinguish it from the already existing database on the server
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'restore now' option is not highlighted
Can anyone help point me in the right direction
Many thank
KalpeshHi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
--
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh

Restoring SQL 6.5 DBs

Hi
I am trying to restore several databases to a single server due to restrictions on hardware. Each server had previously had a database that was named exactly the same on each server. Therefore, the new instances will be appended with an appropriate suffix
to distinguish it from the already existing database on the server.
The problem is actually trying to restore the first database. I have created a new database and named it accordingly. I have a backup of the database I want to restore to this server. When I try and restore it by entering the path of the .dat file, the 'r
estore now' option is not highlighted.
Can anyone help point me in the right direction.
Many thanks
Kalpesh
Hi Kalpesh,
Not sure what happend with Enterprise manager. The "Restore Now" button will
be enabled only after you
select the destination database , "Add file" button , and select the
correct backup file to restore and click "close".
(Now the Restore Now button will be enabled)...
ISQL_W
Why dont you try executing the below command to restore the database from
ISQL_W .
LOAD Database <dbname> from disk='C:\backup\dbname.DMP' with stats=10
(Do the same step for all the databases)
Note:
1. Change the file name and directory based on your requirement.
2. Ensure that SQL 6.5 is patched with SP5a + SP5a Post update
Thanks
Hari
MCDBA
"Kalpesh" <kalpeshvaghela@.eu.spherion.com> wrote in message
news:F1798555-BB00-435F-A9E2-AE546950EAFF@.microsoft.com...
> Hi
> I am trying to restore several databases to a single server due to
restrictions on hardware. Each server had previously had a database that was
named exactly the same on each server. Therefore, the new instances will be
appended with an appropriate suffix to distinguish it from the already
existing database on the server.
> The problem is actually trying to restore the first database. I have
created a new database and named it accordingly. I have a backup of the
database I want to restore to this server. When I try and restore it by
entering the path of the .dat file, the 'restore now' option is not
highlighted.
> Can anyone help point me in the right direction.
> Many thanks
> Kalpesh

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring DBs as different names for testing

Good morning,
There is a job that runs daily to restore the production databases to a
different server for testing purposes so that the test site can have live
data on a daily basis. There is a script that runs after the database
restore to ensure views/procedures/triggers are not referencing the
production REPORTING server... however it errors out everyday. As I did not
build this job and the old DBA left months ago... I'm stumped as to how I
can resolve this.
The script is below... cphprod is the restored database (the prod db is
cph...) The error I receive everyday is: Error string: The column prefix
'cphprod' does not match with a table name or alias name used in the query.
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0 Error Detail Records: Error: -2147217900
(80040E14); Provider Error: 107 (6B) ...
Any idea how I can work around and/or resolve this?
Does anyone have a better way to change all references to a prod reporting
environment?
Thanks in advance for your help...
----
--
use cphprod
go
declare @.name varchar(255), @.type char(2), @.id int
,@.contline int
,@.text varchar(8000),@.text2 varchar(8000)
,@.text3 varchar(8000)
,@.text4 varchar(8000)
,@.text5 varchar(8000)
,@.text6 varchar(8000)
,@.sql varchar(8000)
,@.char10 char(1)
set @.char10 = @.char10
declare mycursor insensitive cursor for
select distinct a.name,type, a.id
from sysobjects a
join syscomments b on a.id = b.id
where type in ('p','v','fn')
and (text like '%cph.%' or text like '%ss002repl.Billing.%' )
--and name = 'ar_oa_Invoice_Master'
--order by a.id,colid
order by type desc
open mycursor
--select max(colid) from syscomments
fetch next from mycursor into @.name, @.type, @.id
while @.@.fetch_status = 0
begin
set @.text = @.char10
set @.text2 = @.char10
set @.text3 = @.char10
set @.text4 = @.char10
set @.text5 = @.char10
set @.text6 = @.char10
set @.contline = 0
select top 1 @.text = text, @.contline = colid
from syscomments where id = @.id
while (select top 1 colid
from syscomments
where id = @.id and colid > @.contline) is not null
begin
if @.contline = 1
select top 1 @.text2 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 2
select top 1 @.text3 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 3
select top 1 @.text4 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 4
select top 1 @.text5 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 5
select top 1 @.text6 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
end
set @.text = replace(@.text,'cph.','cphprod.')
set @.text2 = replace(@.text2,'cph.','cphprod.')
set @.text3 = replace(@.text3,'cph.','cphprod.')
set @.text4 = replace(@.text4,'cph.','cphprod.')
set @.text5 = replace(@.text5,'cph.','cphprod.')
set @.text6 = replace(@.text6,'cph.','cphprod.')
--ss002repl.Billing.
set @.text = replace(@.text,'ss002repl.Billing.','Billingprod.')
set @.text2 = replace(@.text2,'ss002repl.Billing.','Billingprod.')
set @.text3 = replace(@.text3,'ss002repl.Billing.','Billingprod.')
set @.text4 = replace(@.text4,'ss002repl.Billing.','Billingprod.')
set @.text5 = replace(@.text5,'ss002repl.Billing.','Billingprod.')
set @.text6 = replace(@.text6,'ss002repl.Billing.','Billingprod.')
if @.type = 'p'
begin
set @.sql = 'drop proc ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'v'
begin
set @.sql = 'drop view ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'fn'
begin
set @.sql = 'drop function ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
fetch next from mycursor into @.name, @.type, @.id
end
close mycursor
deallocate mycursor
go
set xact_abort on
----
-->> There is a script that runs after the database restore to ensure
I could be wrong here, but are you updating the system tables here? Can you
explain what logic is being employed here?
On a cursory glance, I see some meaningless statements like "set @.char10 =
@.char10" and excessive usage of TOP clauses in your script.
The error message suggests that you are trying to use a table name/alias
which is already replaced in the FROM clause, but still exists in some other
section of a stored procedure, view or function. Without a detailed
inspection of the code and test it out, it is hard to spot out which
procedure, view or function is the real culprint here.
Anith