database problem: restored

Showing posts with label restored. Show all posts

Wednesday, March 28, 2012

Restoring to remove standy/read-only mode.

"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.

"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 to a network paths?

I'm getting the error "The file \\xxx... is on a network path that is not supported for database files. File xx cannot be restored to \\xx..... Use WITH MOVE to identify a valid location for the file. Below is my code for the restore. I've even shared the folder on the network but still no success.

Try
Dim restoreToServer As New Server("xxxx")
Dim rest As New Restore
rest.Devices.AddDevice("\\xx\x$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\myData\myData.BAK", DeviceType.File)

rest.Action = RestoreActionType.Database
rest.Database = "myDatabase"
rest.NoRecovery = False
rest.ReplaceDatabase = True

rest.RelocateFiles.Add(New RelocateFile("myData", "\\xx\data\myData.mdf"))
rest.RelocateFiles.Add(New RelocateFile("myData_log", "\\xx\logs\myData.ldf"))

rest.SqlRestore(restoreToServer)
Catch ex As Exception
MessageBox.Show(ex.InnerException.ToString)

End Try

Can you run a database from a network path?

Why would you want to do it?
|||

We are running the vb app on a desktop. To connect to the production db we issue smo commands. Those commands will backup the production database and then we need to do a restore on another database. To connect to any of the two mentioned we connect using \\host-machine\drive$ but this does not work.

|||That what is wrote for placing data files on a network shares some days ago:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477137&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Monday, March 26, 2012

restoring the master database

I read earlier posts about restoring the master database.
I am testing for failover, and I found that after I had restored the master
database to the failover server, the SQL Server service stopped immediately
after starting.
The databases on the production server are on a drive which doesn't exist on
the failover server. Is it necessary to configure the servers identically?
I think perhaps SQL Server service can't start because it can't locate the
model database to create the tempdb, or because the path the tempdb doesn't
exist.
I can't get it started even with sqlservr.exe -f, in order to Alter Database
and change the location of the databases.
The Event log had errors (17052, 17204) because the path in sysdatabases
points to a non-existent location.
I am running SQL Server 2000 Standard edition.
Thanks
BillHere is a nice article on moving db locations:
http://support.microsoft.com/defaul...b;en-us;224071.
Check the master db first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"bill" <belgie@.datamti.com> wrote in message
news:%237QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>|||Master, Model, and MSDB must be in the same path they originally came from.
Keep in mind though that for reasons unknown to me SQL will shut down after
restoring even to the correct path.
"bill" <belgie@.datamti.com> wrote in message
news:#7QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>|||Hi
The SQL server log will tell you why it fails to start, but if you are
restoring a master database from a different configuration one of the
problems will be that it can not find the databases.
You hardware should be the same configuration, see "single node failover"
section of
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#EJAA[
/url]
John
"bill" wrote:

> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the maste
r
> database to the failover server, the SQL Server service stopped immediatel
y
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers identically
?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb doesn'
t
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter Databa
se
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>sql

restoring the master database

I read earlier posts about restoring the master database.
I am testing for failover, and I found that after I had restored the master
database to the failover server, the SQL Server service stopped immediately
after starting.
The databases on the production server are on a drive which doesn't exist on
the failover server. Is it necessary to configure the servers identically?
I think perhaps SQL Server service can't start because it can't locate the
model database to create the tempdb, or because the path the tempdb doesn't
exist.
I can't get it started even with sqlservr.exe -f, in order to Alter Database
and change the location of the databases.
The Event log had errors (17052, 17204) because the path in sysdatabases
points to a non-existent location.
I am running SQL Server 2000 Standard edition.
Thanks
Bill
Here is a nice article on moving db locations:
http://support.microsoft.com/default...;en-us;224071.
Check the master db first.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"bill" <belgie@.datamti.com> wrote in message
news:%237QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>
|||Master, Model, and MSDB must be in the same path they originally came from.
Keep in mind though that for reasons unknown to me SQL will shut down after
restoring even to the correct path.
"bill" <belgie@.datamti.com> wrote in message
news:#7QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>
|||Hi
The SQL server log will tell you why it fails to start, but if you are
restoring a master database from a different configuration one of the
problems will be that it can not find the databases.
You hardware should be the same configuration, see "single node failover"
section of
http://www.microsoft.com/technet/pro...clus.mspx#EJAA
John
"bill" wrote:

> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the master
> database to the failover server, the SQL Server service stopped immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist on
> the failover server. Is it necessary to configure the servers identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>
sql

Restoring the Master Database

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?
"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

restoring the master database

I read earlier posts about restoring the master database.
I am testing for failover, and I found that after I had restored the master
database to the failover server, the SQL Server service stopped immediately
after starting.
The databases on the production server are on a drive which doesn't exist on
the failover server. Is it necessary to configure the servers identically?
I think perhaps SQL Server service can't start because it can't locate the
model database to create the tempdb, or because the path the tempdb doesn't
exist.
I can't get it started even with sqlservr.exe -f, in order to Alter Database
and change the location of the databases.
The Event log had errors (17052, 17204) because the path in sysdatabases
points to a non-existent location.
I am running SQL Server 2000 Standard edition.
Thanks
BillHere is a nice article on moving db locations:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071.
Check the master db first.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"bill" <belgie@.datamti.com> wrote in message
news:%237QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>|||Master, Model, and MSDB must be in the same path they originally came from.
Keep in mind though that for reasons unknown to me SQL will shut down after
restoring even to the correct path.
"bill" <belgie@.datamti.com> wrote in message
news:#7QDmlDwEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the
master
> database to the failover server, the SQL Server service stopped
immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist
on
> the failover server. Is it necessary to configure the servers
identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb
doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter
Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>|||Hi
The SQL server log will tell you why it fails to start, but if you are
restoring a master database from a different configuration one of the
problems will be that it can not find the databases.
You hardware should be the same configuration, see "single node failover"
section of
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#EJAA
John
"bill" wrote:
> I read earlier posts about restoring the master database.
> I am testing for failover, and I found that after I had restored the master
> database to the failover server, the SQL Server service stopped immediately
> after starting.
> The databases on the production server are on a drive which doesn't exist on
> the failover server. Is it necessary to configure the servers identically?
> I think perhaps SQL Server service can't start because it can't locate the
> model database to create the tempdb, or because the path the tempdb doesn't
> exist.
> I can't get it started even with sqlservr.exe -f, in order to Alter Database
> and change the location of the databases.
> The Event log had errors (17052, 17204) because the path in sysdatabases
> points to a non-existent location.
> I am running SQL Server 2000 Standard edition.
> Thanks
> Bill
>
>

Friday, March 23, 2012

Restoring SQL databases

We often build databases on behalf of our clients and we're still using SQL
7 as a base level as we can generate .BAK files which can be restored to SQL
7 and SQL 2000.
I assume that if we upgraded to SQL 2005 on our production server that we
will be *unable* to restore .BAK files generated on that version onto an
older server running SQL 2000 or SQL 7.
If so, how to developers normally distribute their database? Note: we can't
use DTS and we're restoring to a server on a completely separate network.
Thanks, Rob.Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> We often build databases on behalf of our clients and we're still using
> SQL 7 as a base level as we can generate .BAK files which can be
> restored to SQL 7 and SQL 2000.
> I assume that if we upgraded to SQL 2005 on our production server that we
> will be *unable* to restore .BAK files generated on that version onto an
> older server running SQL 2000 or SQL 7.
Yes, you cannot restore a backup on SQL 2005 on SQL 2000 or SQL 7.

> If so, how to developers normally distribute their database? Note: we
> can't use DTS and we're restoring to a server on a completely separate
> network.
We build our databases from scripts using our own load tool, which we
then but into an install kit. Distributing a shrink-wrapped database is good
for the first installation, but it does not fly with upgrades. So in the
end, you need an ability do to scripts.
But of course, for a first installation, attaching a complete database
makes that installation faster. For us this is not an issue, as we work
with an expensive product with a small customer base, so we upgrade
far more often that we build a new databse.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> But of course, for a first installation, attaching a complete database
> makes that installation faster. For us this is not an issue, as we work
> with an expensive product with a small customer base, so we upgrade
> far more often that we build a new databse.
Sounds a similar scenario to our app - small user base, niche product. We
typically build and configure the system locally and then take the BAK file
to the customer for pilot installation.
Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
might be an option. We can set-up a SQL 2000 server in development, DTS to
there and take the BAK from that environment.
We'll not be using any SQL 2005 specific functions but I'm looking at our
strategy for 2006 which is including major upgrades of all production
servers like SQL.
Cheers, Rob.|||Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
> might be an option. We can set-up a SQL 2000 server in development, DTS to
> there and take the BAK from that environment.
There isn't even a DTS in SQL 2005, it's called SQL Server Integration
Services. Maybe there is a way to export from SQL 2005 to SQL 2000, but
I actually tried to use the Copy Database Wizard (which uses SSIS) in that
direction the other w

, but I was told to that it was not supported.
(Not that I would recommend the Copy Database Wizard anyway. I filed a
number of bugs for it during the beta programme, and not all are fixed
in the RTM version.)
If you want to look at what we use, our toolset is available as freeware
from http://www.abaris.se/abaperls/. It's fairly complex, and not easy to
get started with, but it might give you some inspiration.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Restoring SQL 7 database to SQL 2000

Can you back up a SQL 7 database and restored it to a SQL
2000 Server?
Are there any issues with doing that?
ThanksStev,
Yes, you can.You may face some issues if the database users/logins wont
match.See the below articles for details:
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Stev" <stev@.partnervest.com> wrote in message
news:019801c355ea$dc150860$a101280a@.phx.gbl...
> Can you back up a SQL 7 database and restored it to a SQL
> 2000 Server?
> Are there any issues with doing that?
> Thanks|||In addition, if you chose a Windows collation for SQL2000 during install,
you may need to convert the database to match the server collation depending
on it's use of tempdb and/or interaction with other databases on the server
as when it is restored/attached to SQL2000 it will have a SQL collation. See
BOL for more details on collation
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Stev" <stev@.partnervest.com> wrote in message
news:019801c355ea$dc150860$a101280a@.phx.gbl...
Can you back up a SQL 7 database and restored it to a SQL
2000 Server?
Are there any issues with doing that?
Thanks

Wednesday, March 21, 2012

Restoring replication

I have a problem that needs tip: Our main replica server need to be restored.
It has been the main server within a network using merge replications between
around 10 servers with publications and push/pull subscriptions. Now listen
the server was stolen and I need to restore (I got the all the *.mdf files on
a separate ghost-disk) the server. I need some good advice on how to restore
properly. I have also backup-files from all replica-databases plus master
database and distribution database. But I have not any backup on the
MSDB-database. Suggestions?
Best wishes
Basically you are hosed. All of your merge agent job information is gone.
You will have to drop your publications and subscriptions and rebuild from
scratch.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mats" <Mats@.discussions.microsoft.com> wrote in message
news:787985FB-1CA8-47E3-8B00-0575443082D4@.microsoft.com...
>I have a problem that needs tip: Our main replica server need to be
>restored.
> It has been the main server within a network using merge replications
> between
> around 10 servers with publications and push/pull subscriptions. Now
> listen
> the server was stolen and I need to restore (I got the all the *.mdf files
> on
> a separate ghost-disk) the server. I need some good advice on how to
> restore
> properly. I have also backup-files from all replica-databases plus master
> database and distribution database. But I have not any backup on the
> MSDB-database. Suggestions?
> --
> Best wishes

restoring replicated database

Hi,
I've restored a merge replicated database (publisher) on a new server
without replication.
Now i observe that there are some default replication procedures named
*_pal in my restored database. What is the way of deleting these
replication procedures. What will be the impact of the same.
Regds,
amit
Amit,
these procedures will need cleaning up by hand. sp_removedbreplication will
remove many system objects but these procs sometimes remain. You can
hand-craft a script to delete them (using information_schema.routines) or
just do it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul thanks for you reply. We have configured this restored database
for replication and it has created new *pal. procedures. Is it ok if we
delete the earlier *pal procedure now?
|||Yes

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks, but can you just let me know if there would be any impact if i
keep the old procedures instead of deleting them as i would not like to
change the system configuration if there is not impact.
|||If you mean problems caused by name conflicts and the like, I think the
complexity of the name ensures this isn't the case. I have seen systems that
have accumulated orphaned procs like this over several years and the DBAs
didn't even notice they were there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Tuesday, March 20, 2012

restoring msdb**

Hi
I restored msdb database successfully,
because of changing
the server and needed to restore all jobs
have been schaduled for periodical backup all
user batabases.
but when I want to change the text of step1
of every job to new drive in a new server
following error happened:
""
why? what's wrong?
any help would be greatly appreciated.RM,
Do you get an error number?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RM wrote:
> Hi
> I restored msdb database successfully,
> because of changing
> the server and needed to restore all jobs
> have been schaduled for periodical backup all
> user batabases.
> but when I want to change the text of step1
> of every job to new drive in a new server
> following error happened:
> ""
> why? what's wrong?
> any help would be greatly appreciated.|||Also check SQL server error log and SQLAgent log for complete information on this behaviour.
--
--
Satya SKJ
"Mark Allison" wrote:
> RM,
> Do you get an error number?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
> > Hi
> >
> > I restored msdb database successfully,
> > because of changing
> > the server and needed to restore all jobs
> > have been schaduled for periodical backup all
> > user batabases.
> > but when I want to change the text of step1
> > of every job to new drive in a new server
> > following error happened:
> > ""
> >
> > why? what's wrong?
> > any help would be greatly appreciated.
>|||yeah,
error 14274
On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
<marka@.no.tinned.meat.mvps.org> wrote:
> RM,
> Do you get an error number?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.|||RM, I don't know why your error message text is not showing but it
should be this:
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
This means that the server has been renamed and the originating_server
column in the sysjobs table is referencing the old server name. Update
sysjobs and it should be fine.
Use this proc to do the rename:
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
This proc assumes that @.@.SERVERNAME is correct, you might want to check
that first, and correct it if it's wrong using sp_dropserver, sp_addserver
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RM wrote:
> yeah,
> error 14274
> On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
> <marka@.no.tinned.meat.mvps.org> wrote:
>> RM,
>> Do you get an error number?
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.
>|||thanks alot,
I was suprised ,very useful help**
On Tue, 13 Jul 2004 09:24:57 +0100, Mark Allison
<marka@.no.tinned.meat.mvps.org> wrote:
> RM, I don't know why your error message text is not showing but it
> should be this:
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> This means that the server has been renamed and the originating_server
> column in the sysjobs table is referencing the old server name. Update
> sysjobs and it should be fine.
> Use this proc to do the rename:
> http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
> This proc assumes that @.@.SERVERNAME is correct, you might want to check
> that first, and correct it if it's wrong using sp_dropserver,
> sp_addserver
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
>> yeah,
>> error 14274
>> On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
>> <marka@.no.tinned.meat.mvps.org> wrote:
>> RM,
>> Do you get an error number?
>> -- Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.
>>

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.

Monday, March 12, 2012

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
>>
>>
>>
>

Wednesday, March 28, 2012

Restoring to remove standy/read-only mode.

Restoring to remove standy/read-only mode.

Restoring to remove standy/read-only mode.

Restoring to a network paths?

Monday, March 26, 2012

restoring the master database

restoring the master database

Restoring the Master Database

restoring the master database

Friday, March 23, 2012

Restoring SQL databases

Restoring SQL 7 database to SQL 2000

Wednesday, March 21, 2012

Restoring replication

restoring replicated database

Tuesday, March 20, 2012

restoring msdb**

restoring msdb**

restoring msdb**

Restoring MSDB to another server

Monday, March 12, 2012

restoring master database

restoring master database

database problem

Blog Archive

About Me