Restoring to remove standy/read-only mode.

Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
Wade
That is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>
|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>
|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>
|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>

Restoring to remove standy/read-only mode.

Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx
.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>sql

Restoring to remove standy/read-only mode.

Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>

Restoring the Master Database

I am trying to restore the master database and am getting the following erro
r:
RESTORE DATABASE must be used in single user mode when trying to restore the
master database. RESTORE DATABASE is terminating abnormally.
I know a little bit about SQL, but I am no guru, so any help is appreciated.
Thanks.Hi,
(Master database can be restore while SQL server is started in Single
user Mode)
1.. Stop SQL server Service
2.. Start Microsoft SQL Server in single-user mode.
From a command prompt, enter:
sqlservr.exe -c -m
3. Login to Query analyzer as SA
4. Execute the RESTORE DATABASE statement to restore the master
database backup, specifying:
RESTORE database master from disk='c:\backup\master.bak'
Thanks
Hari
MCDBA
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1DB3B978-8131-4E92-8CFB-137D6582E367@.microsoft.com...
> I am trying to restore the master database and am getting the following
error:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database. RESTORE DATABASE is terminating abnormally.
> I know a little bit about SQL, but I am no guru, so any help is
appreciated. Thanks.|||You need to start SQL Server in single-user mode in order to restore master.
This is usually done by starting SQL Server in a command window using
sqlservr.exe. For example:
CD C:\Program Files\Microsoft SQL Server\MSSQL\Binn
SQLSERVR -c -m
After you execute the RESTORE (using OSQL or Query Analyzer), SQL Server
will automatically shutdown in the command window. You can then start it
normally.
See the Books Online for more information on the SQLSERVR application.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1DB3B978-8131-4E92-8CFB-137D6582E367@.microsoft.com...
> I am trying to restore the master database and am getting the following
error:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database. RESTORE DATABASE is terminating abnormally.
> I know a little bit about SQL, but I am no guru, so any help is
appreciated. Thanks.|||Thank you, the database was restored. Now I have another problem. I restor
ed the master database to a disaster recovery server and the database names
are different. How do I Point the master DB to the new databases?|||"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9A89A18C-6F95-4066-B08E-3D5657CDF85B@.microsoft.com...
> Thank you, the database was restored. Now I have another problem. I
restored the master database to a disaster recovery server and the database
names are different. How do I Point the master DB to the new databases?
EXEC sp_attach_db or sp_attach_single_file_db
e.g.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
--Outgoing mail is certified Virus Free.Checked by AVG anti-virus system
(http://www.grisoft.com).Version: 6.0.647 / Virus Database: 414 - Release
Date: 29/03/2004|||Hi,
Since the database names and Physical file names are different in the
restored database you may need to do the below steps:-
1. Execute sp_detach_db <dbname> to detach the database
2. Use sp_attach_db <actual_dbname>,'physical mdf file name with
path','physical LDF file name with path'
Note:
Not the above 2 steps for all the problematic databases.
Thanks
Hari
MCDBA
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9A89A18C-6F95-4066-B08E-3D5657CDF85B@.microsoft.com...
> Thank you, the database was restored. Now I have another problem. I
restored the master database to a disaster recovery server and the database
names are different. How do I Point the master DB to the new databases?

Restoring SQL Server 2005 backup to SQL server 2000

I have a sql 2005 backup that I am trying to restore in sql 2000. I get the following error..

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Is there a workaround for this?

Vyanki

No, there is no workaround. SQL Server 2005 does not support downgrades or restores to SQL Server 2000.

|||

thanks.

what way can be come true the case.

|||There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

|||

Jonathan Fife wrote:

There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

... Then what do we do once we have the DB on the SQL 2000 server but still cant import or attach it? I've received a 2005 database from my web designer and need to import it into my 2000 server.

Thanks!

Chris

|||Unfortunately, you cannot restore a SQL Server 2005 backup to SQL Server 2000. In your situation, I would install a named instance of SQL Server 2005 (alongside your default 2000 instance). Then you could restore the 2005 backup there and use it directly, or you could use SSIS to copy the data to SQL Server 2000.sql

Restoring SQL Server 2005 backup to SQL server 2000

I have a sql 2005 backup that I am trying to restore in sql 2000. I get the following error..

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Is there a workaround for this?

Vyanki

No, there is no workaround. SQL Server 2005 does not support downgrades or restores to SQL Server 2000.

|||

thanks.

what way can be come true the case.

|||There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish|||

Jonathan Fife wrote:

There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

... Then what do we do once we have the DB on the SQL 2000 server but still cant import or attach it? I've received a 2005 database from my web designer and need to import it into my 2000 server.

Thanks!

Chris

|||Unfortunately, you cannot restore a SQL Server 2005 backup to SQL Server 2000. In your situation, I would install a named instance of SQL Server 2005 (alongside your default 2000 instance). Then you could restore the 2005 backup there and use it directly, or you could use SSIS to copy the data to SQL Server 2000.

Restoring SQL Server 2005 backup to SQL server 2000

I have a sql 2005 backup that I am trying to restore in sql 2000. I get the following error..

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Is there a workaround for this?

Vyanki

No, there is no workaround. SQL Server 2005 does not support downgrades or restores to SQL Server 2000.

|||

thanks.

what way can be come true the case.

|||There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

|||

Jonathan Fife wrote:

There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

... Then what do we do once we have the DB on the SQL 2000 server but still cant import or attach it? I've received a 2005 database from my web designer and need to import it into my 2000 server.

Thanks!

Chris

|||Unfortunately, you cannot restore a SQL Server 2005 backup to SQL Server 2000. In your situation, I would install a named instance of SQL Server 2005 (alongside your default 2000 instance). Then you could restore the 2005 backup there and use it directly, or you could use SSIS to copy the data to SQL Server 2000.

Restoring SQL Server 2005 backup to SQL server 2000

I have a sql 2005 backup that I am trying to restore in sql 2000. I get the following error..

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Is there a workaround for this?

Vyanki

No, there is no workaround. SQL Server 2005 does not support downgrades or restores to SQL Server 2000.

|||

thanks.

what way can be come true the case.

|||There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish|||

Jonathan Fife wrote:

There is no "Restore" functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click "script all objects...", hit next
select any options you want, specifically changing "script for server version" to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export...
set source and hit next
set destination and hit next
select "copy data from one or more tables", hit next
select all, check "optimize for many tables" and "run in a transaction"
you may have to edit each table mapping and check "enable identity insert"
next through to finish

... Then what do we do once we have the DB on the SQL 2000 server but still cant import or attach it? I've received a 2005 database from my web designer and need to import it into my 2000 server.

Thanks!

Chris

|||Unfortunately, you cannot restore a SQL Server 2005 backup to SQL Server 2000. In your situation, I would install a named instance of SQL Server 2005 (alongside your default 2000 instance). Then you could restore the 2005 backup there and use it directly, or you could use SSIS to copy the data to SQL Server 2000.

Restoring SQL Server 2000 backup to Express

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

hi,

withers wrote:

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

try forcing the "overwrite" of the database already present in the SQLExpress instance,
RESTORE DATABASE ....
....
WITH REPLACE;

or just drop the database before restoring (obviously take a full local backup of it before dropping it, if required )..

regards

restoring old mdf file

I need to restore a Sql2000 database from an .mdf file from last July. Can
people tell me if the following procedure is correct, or am I missing
something? :
1. restore the .mdf file from backup to temp location (done)
2. backup existing database (in case I need to restore this one)
3. delete existing database (to cleanup metadata in master)
4. shutdown sqlserver
5. replace existing .mdf file with old one
6. startup sqlserver
7. attach database to .mdf -- how do I do this?
Thanks for any assistance with this.
-Frank Brown
Seattle Fire Dept
http://www.inwa.net/~frog/take a look at the sp_attach_db procedure, in BOL.
Alex Ivascu
"frank brown" <someone@.somewhere.net> wrote in message
news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> I need to restore a Sql2000 database from an .mdf file from last July.
Can
> people tell me if the following procedure is correct, or am I missing
> something? :
> 1. restore the .mdf file from backup to temp location (done)
> 2. backup existing database (in case I need to restore this one)
> 3. delete existing database (to cleanup metadata in master)
> 4. shutdown sqlserver
> 5. replace existing .mdf file with old one
> 6. startup sqlserver
> 7. attach database to .mdf -- how do I do this?
> Thanks for any assistance with this.
> -Frank Brown
> Seattle Fire Dept
> http://www.inwa.net/~frog/
>|||Hi ,
This activity do not require a SQL server shutdown. Please use the below
commands to perform:
1. drop database databasename
2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
3. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Now your old database will be ready to use.
Incase if you need the old and new database in server then u have to use
Move option along with
sp_attach_db command.
Thanks
Hari
MCDBA
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> take a look at the sp_attach_db procedure, in BOL.
> Alex Ivascu
>
> "frank brown" <someone@.somewhere.net> wrote in message
> news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > I need to restore a Sql2000 database from an .mdf file from last July.
> Can
> > people tell me if the following procedure is correct, or am I missing
> > something? :
> >
> > 1. restore the .mdf file from backup to temp location (done)
> > 2. backup existing database (in case I need to restore this one)
> > 3. delete existing database (to cleanup metadata in master)
> > 4. shutdown sqlserver
> > 5. replace existing .mdf file with old one
> > 6. startup sqlserver
> > 7. attach database to .mdf -- how do I do this?
> >
> > Thanks for any assistance with this.
> >
> > -Frank Brown
> > Seattle Fire Dept
> > http://www.inwa.net/~frog/
> >
> >
>|||Huh? If you drop the database, there are no more database file...
Also, sp_attach_db is only guaranteed to work if you first detach the database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> This activity do not require a SQL server shutdown. Please use the below
> commands to perform:
> 1. drop database databasename
> 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
> 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> @.filename1 = N'c:\mssql\data\dbname.mdf',
> @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> Now your old database will be ready to use.
> Incase if you need the old and new database in server then u have to use
> Move option along with
> sp_attach_db command.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > take a look at the sp_attach_db procedure, in BOL.
> >
> > Alex Ivascu
> >
> >
> > "frank brown" <someone@.somewhere.net> wrote in message
> > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > I need to restore a Sql2000 database from an .mdf file from last July.
> > Can
> > > people tell me if the following procedure is correct, or am I missing
> > > something? :
> > >
> > > 1. restore the .mdf file from backup to temp location (done)
> > > 2. backup existing database (in case I need to restore this one)
> > > 3. delete existing database (to cleanup metadata in master)
> > > 4. shutdown sqlserver
> > > 5. replace existing .mdf file with old one
> > > 6. startup sqlserver
> > > 7. attach database to .mdf -- how do I do this?
> > >
> > > Thanks for any assistance with this.
> > >
> > > -Frank Brown
> > > Seattle Fire Dept
> > > http://www.inwa.net/~frog/
> > >
> > >
> >
> >
>|||Hi,
Frank's mail says that he need to drop the existing database and load the
old database. That is the reason I mentioned the "drop database" command
initially.
I do agree with you , some times the SP_attachdb wont work incase if we are
not using SP_detachdb.
Frank,
What you can do is perform the below steps to put you in safer
side.
1. Perform a backup of your existing database and keep it in a safe folder.
2. drop database databasename
3. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
4. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e0RKXhfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Huh? If you drop the database, there are no more database file...
> Also, sp_attach_db is only guaranteed to work if you first detach the
database.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> >
> > This activity do not require a SQL server shutdown. Please use the below
> > commands to perform:
> >
> > 1. drop database databasename
> > 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any
directory)
> > 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> > @.filename1 = N'c:\mssql\data\dbname.mdf',
> > @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> >
> > Now your old database will be ready to use.
> >
> > Incase if you need the old and new database in server then u have to use
> > Move option along with
> > sp_attach_db command.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> > news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > > take a look at the sp_attach_db procedure, in BOL.
> > >
> > > Alex Ivascu
> > >
> > >
> > > "frank brown" <someone@.somewhere.net> wrote in message
> > > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > > I need to restore a Sql2000 database from an .mdf file from last
July.
> > > Can
> > > > people tell me if the following procedure is correct, or am I
missing
> > > > something? :
> > > >
> > > > 1. restore the .mdf file from backup to temp location (done)
> > > > 2. backup existing database (in case I need to restore this one)
> > > > 3. delete existing database (to cleanup metadata in master)
> > > > 4. shutdown sqlserver
> > > > 5. replace existing .mdf file with old one
> > > > 6. startup sqlserver
> > > > 7. attach database to .mdf -- how do I do this?
> > > >
> > > > Thanks for any assistance with this.
> > > >
> > > > -Frank Brown
> > > > Seattle Fire Dept
> > > > http://www.inwa.net/~frog/
> > > >
> > > >
> > >
> > >
> >
> >
>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
> > Hi,
> >
> > I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> > backup database msdb to disk ='c:\msdb.dat'
> >
> > then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> > norecovery
> >
> > The restore seem to succeed. But I get the following messages:
> >
> > Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> > Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> > Server: Msg 927, Level 14, State 2, Line 1
> > Database 'msdb' cannot be opened. It is in the middle of a restore.
> > Server: Msg 3009, Level 16, State 3, Line 1
> > Could not insert a backup or restore history/detail record in the msdb
> > database. This may indicate a problem with the msdb database. The
> > backup/restore operation was still successful.
> > RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> > MB/sec).
> >
> > These messages aren't generated with SQL2005, nor do you see the messages if
> > you specify 'with recovery' on the restore statement.
> >
> > I would be appreciative if someone could explain these messages. If I ignore
> > them, it is still possible to recover the database (restore database msdb
> > with recovery).
> >
> > Thanks, Howard
> >
> >
> >
> >
> >
> >|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
possible. One could argue that SQL server would be smart enough to produce only a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
enough to warrant any major effort... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mode.
> So you may still need to restore the transaction log to a PIT after restoring
> the database. Also, I understand that the msdb was indeed restored despite
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and this
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:
> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> > Hi Ben,
> >
> > Thanks for your quick reply. I did consider using 'with recovery' on the
> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
> > So you may still need to restore the transaction log to a PIT after restoring
> > the database. Also, I understand that the msdb was indeed restored despite
> > the error message. What I really want to know is what to make of the error
> > message as I work with a program that deals with other people's data and this
> > type of message makes them nervous.
> >
> > Thanks again, Howard
> >
> >
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the update. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
>> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing
>> isn't
>> possible. One could argue that SQL server would be smart enough to produce only a warning or
>> similar
>> in these situations, of course. Perhaps you want to file an entry at
>> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
>> enough to warrant any major effort... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Howard" <Howard@.discussions.microsoft.com> wrote in message
>> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>> > Hi Ben,
>> >
>> > Thanks for your quick reply. I did consider using 'with recovery' on the
>> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
>> > So you may still need to restore the transaction log to a PIT after restoring
>> > the database. Also, I understand that the msdb was indeed restored despite
>> > the error message. What I really want to know is what to make of the error
>> > message as I work with a program that deals with other people's data and this
>> > type of message makes them nervous.
>> >
>> > Thanks again, Howard
>> >
>> >
>>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, Howard
If you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>
|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>
|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard
|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connec
t
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I igno
re
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see t
he
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to conn
ect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
>|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode
.
So you may still need to restore the transaction log to a PIT after restorin
g
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and thi
s
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the rest
ore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore
history writing isn't
possible. One could argue that SQL server would be smart enough to produce o
nly a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find t
his happening often
enough to warrant any major effort... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mo
de.
> So you may still need to restore the transaction log to a PIT after restor
ing
> the database. Also, I understand that the msdb was indeed restored despit
e
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and t
his
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and tr
y
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the re
store history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that resto
re history writing isn't
> possible. One could argue that SQL server would be smart enough to produce
only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find
this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the
update. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...[vbcol=seagreen]
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and
try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>

Restoring Master Database errors on SQL 2000 SP3a

Hi All
I work in a envoriment where i restore lots of master
database for specfic reason.
The problem i am currently encountering is the following
When i attempt to restore the Master database is SQL
Enterprise Manager (in single user mode) i get a error
box with a title of sql-dmo odbc sqlstate: HY000 and
nothing else.
It then kills the SQL service which i can then restart as
normal but the master database is not restored.
If i attempt to restore via SQL Query Anylzer i just get
connect broken and the SQL service is stopped.
The only way i have found to restore my master database is
to reinstall SQL from scratch which is very time consuming.
Has anyone else seen this and now how to workaround this
issue with out reinstalling SQL
Many Thanks
Martin CheethamHi,
I have never ended up in this situation. I recommend you to try rebuilding
the MASTER database using REBUILDM.EXE rather than reinstalling SQL Server.
After rebulding the master you can restore the database
using the normal procedure
--
Thanks
Hari
MCDBA
"Martin Cheetham" <martin_cheetham@.yahoo.co.uk> wrote in message
news:237a101c45e86$4c7f5b60$a401280a@.phx.gbl...
> Hi All
> I work in a envoriment where i restore lots of master
> database for specfic reason.
> The problem i am currently encountering is the following
> When i attempt to restore the Master database is SQL
> Enterprise Manager (in single user mode) i get a error
> box with a title of sql-dmo odbc sqlstate: HY000 and
> nothing else.
> It then kills the SQL service which i can then restart as
> normal but the master database is not restored.
> If i attempt to restore via SQL Query Anylzer i just get
> connect broken and the SQL service is stopped.
> The only way i have found to restore my master database is
> to reinstall SQL from scratch which is very time consuming.
> Has anyone else seen this and now how to workaround this
> issue with out reinstalling SQL
> Many Thanks
> Martin Cheetham

restoring master database

Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
If you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>
|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas W?ckl" wrote:

> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
>
|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:

> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:

> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...[vbcol=seagreen]
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
|||Hmm. Do both have the same hotfixes?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>

restoring master database

Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andyIf you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas Wöckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> > If you are going to restore master from one server onto another, then you
> > need to have all DB's in the exact same location as they were on the
> > original server.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> > news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> > Hi group!
> >
> > I am trying to do the following for a recovery strategy: If server1 fails
> > all databases should be restored on server2. I did a restore of the master
> > database as described in some articles (starting in single user mode,
> > restoring...) after restoring my sql server does not start. An example
> > error
> > message is:
> >
> > FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> > virtual device number (VDN) 1.
> >
> > On server1 my data directory is d:\sql\mssql\data - on server2 my data
> > directory is d:\sql\mssql\mssql\data (configuration error during setup).
> > He
> > wants to load all databases but of course does not have all the
> > databases -
> > I have to recover them now - but how - I can't start the service..
> >
> > Anyone an idea?
> >
> > best regards
> >
> > andy
> >
> >
>
>|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1 fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> > hi tom!
>> >
>> > hmm - but what are the steps that I have to take - Am I right that I
>> > first
>> > have to restore all the databases and the last step is to restore the
>> > master database?
>> >
>> > best regards
>> >
>> > andy
>> >
>> >
>> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> >> If you are going to restore master from one server onto another, then
>> >> you
>> >> need to have all DB's in the exact same location as they were on the
>> >> original server.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >> .
>> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> >> Hi group!
>> >>
>> >> I am trying to do the following for a recovery strategy: If server1
>> >> fails
>> >> all databases should be restored on server2. I did a restore of the
>> >> master
>> >> database as described in some articles (starting in single user mode,
>> >> restoring...) after restoring my sql server does not start. An example
>> >> error
>> >> message is:
>> >>
>> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> >> virtual device number (VDN) 1.
>> >>
>> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> >> directory is d:\sql\mssql\mssql\data (configuration error during
>> >> setup).
>> >> He
>> >> wants to load all databases but of course does not have all the
>> >> databases -
>> >> I have to recover them now - but how - I can't start the service..
>> >>
>> >> Anyone an idea?
>> >>
>> >> best regards
>> >>
>> >> andy
>> >>
>> >>
>> >
>> >
>>|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>|||Hmm. Do both have the same hotfixes?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during
>> setup). He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>>
>