database problem: disk

Showing posts with label disk. Show all posts

Wednesday, March 28, 2012

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

Monday, March 26, 2012

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'bark
I think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message[vbcol=seagreen]
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.
>
>.
>
|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
|||A better suggestion would be to take the database offline. If you are restoring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situations, those connections have high privilege. Because of this, SINGLE_USER and RESTRICTED_USER may not successfully lock out users long enough to start up the restore operation. If you use the OFFLINE setting, then no one, not even you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas

"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message news:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the
database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
>>
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a écrit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, s
o you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users y
ou have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.[vbcol=seagreen]
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:|||A better suggestion would be to take the database offline. If you are resto
ring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situatio
ns, those connections have high privilege. Because of this, SINGLE_USER and
RESTRICTED_USER may not successfully lock out users long enough to start up
the restore operation. If you use the OFFLINE setting, then no one, not ev
en you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
--
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message new
s:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:

Friday, March 23, 2012

Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.

Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql

Wednesday, March 21, 2012

Restoring SQL 2000 database to another SQL Server

I get all sorts of errors using this:
RESTORE DATABASE TMW_LIVE FROM disk
= '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
server\mssql\data\tmw_live.mdf',
MOVE 'psmaster52log' to 'c:\program files\ms sql
server\mssql\data\tmw_live.ldf',
REPLACE
go
Couple of questions: my database's name is TMW_LIVE and
the logical name is psmaster52data - am I using them in
the correct spot? I get an error saying that the physical
name tmw_live.mdf may be incorrect - that is where I want
to restore to, how can it be incorrect? I've tried it
with a database named this and then deleted it. I also
get an error saying: "File 'psmaster52Data' cannot be
restored to 'c:\program ..." use MOVE instead. I was
using move. All I'm trying to do is to take a backup on
one SQL Server and restore to another (that doesn't have
the same drives), can anyone help me out on this?Larry,
Make sure that there's no files with the same name in that directory where
you're trying to restore the database. This is usually the cause of the
"File 'psmaster52Data' cannot be restored to 'c:\program ...' use MOVE
instead" error.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Larry Shulan" <lshulan@.lindencompanies.com> wrote in message
news:060b01c33fdc$d4df12d0$a001280a@.phx.gbl...
> I get all sorts of errors using this:
> RESTORE DATABASE TMW_LIVE FROM disk
> = '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
> WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
> server\mssql\data\tmw_live.mdf',
> MOVE 'psmaster52log' to 'c:\program files\ms sql
> server\mssql\data\tmw_live.ldf',
> REPLACE
> go
> Couple of questions: my database's name is TMW_LIVE and
> the logical name is psmaster52data - am I using them in
> the correct spot? I get an error saying that the physical
> name tmw_live.mdf may be incorrect - that is where I want
> to restore to, how can it be incorrect? I've tried it
> with a database named this and then deleted it. I also
> get an error saying: "File 'psmaster52Data' cannot be
> restored to 'c:\program ..." use MOVE instead. I was
> using move. All I'm trying to do is to take a backup on
> one SQL Server and restore to another (that doesn't have
> the same drives), can anyone help me out on this?

Tuesday, March 20, 2012

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

Friday, March 9, 2012

Restoring from MDF and LDF

Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JKDid you try attaching them?
http://www.aspfaq.com/
(Reverse address to reply.)
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF
and LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\data\name_Log.LDF' may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK|||Can you somehow add a D: drive, place the files in the right path and try
attaching again.
Or else, find another SQL Server with D: drive and try attaching these
databases to that server.
Note that, if you haven't detached these databases previously, you may not
be able to attach them successfully.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK|||Yes I tried using Enterprise Manager and the Attach Database as well as T-SQ
L sp_attach_db
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching them?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> and LDF files for SQL Server Database.
> 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
>
>|||I tried this too then it gives error:
An error occurred while processing the log for database 'name'
Connection broken
"Narayana Vyas Kondreddi" wrote:

> Can you somehow add a D: drive, place the files in the right path and try
> attaching again.
> Or else, find another SQL Server with D: drive and try attaching these
> databases to that server.
> Note that, if you haven't detached these databases previously, you may not
> be able to attach them successfully.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF a
nd
> LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LD
F'
> may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
>
>|||Are you at all familiar with DBCC REBUILD_LOG ? It saved my bacon once
with minimal data loss.
In article <1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com>,
JK@.discussions.microsoft.com said...
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
>|||Hi JK,
Copy the original MDF and LDF to a safe location and try attaching the
database only with MDF file. This will not work if you have more
than 1 MDF and LDF files.
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'c:\MSSQL\Data\pubs.mdf'
If the above fail then try:- ( If you have a backup for the database then
use that file to restore)
1. Start database in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode. After this database will
be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
Thanks
Hari
MCDBA
"JK" <JK@.discussions.microsoft.com> wrote in message
news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...[vbcol=seagreen]
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
try[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
'D:\MSSQL\data\name_Log.LDF'[vbcol=seagreen]|||Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com said...
[vbcol=seagreen]
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
> 1. Start database in emergency mode
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
> You might be able to use bulk copy program (bcp), simple SELECT commands,
or
> use DTS to extract
> your data while the database is in emergency mode. After this database wil
l
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
> Thanks
> Hari
> MCDBA
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'

Restoring from MDF and LDF

Hi,
Our hard disk parition failed and the only thing we can recover were MDF and LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK
Did you try attaching them?
http://www.aspfaq.com/
(Reverse address to reply.)
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF
and LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\data\name_Log.LDF' may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
|||Can you somehow add a D: drive, place the files in the right path and try
attaching again.
Or else, find another SQL Server with D: drive and try attaching these
databases to that server.
Note that, if you haven't detached these databases previously, you may not
be able to attach them successfully.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK
|||Yes I tried using Enterprise Manager and the Attach Database as well as T-SQL sp_attach_db
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching them?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> and LDF files for SQL Server Database.
> 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
>
>
|||I tried this too then it gives error:
An error occurred while processing the log for database 'name'
Connection broken
"Narayana Vyas Kondreddi" wrote:

> Can you somehow add a D: drive, place the files in the right path and try
> attaching again.
> Or else, find another SQL Server with D: drive and try attaching these
> databases to that server.
> Note that, if you haven't detached these databases previously, you may not
> be able to attach them successfully.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF and
> LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
> may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
>
>
|||Are you at all familiar with DBCC REBUILD_LOG ? It saved my bacon once
with minimal data loss.
In article <1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com>,
JK@.discussions.microsoft.com said...
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
|||Hi JK,
Copy the original MDF and LDF to a safe location and try attaching the
database only with MDF file. This will not work if you have more
than 1 MDF and LDF files.
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'c:\MSSQL\Data\pubs.mdf'
If the above fail then try:- ( If you have a backup for the database then
use that file to restore)
1. Start database in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode. After this database will
be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
Thanks
Hari
MCDBA
"JK" <JK@.discussions.microsoft.com> wrote in message
news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...[vbcol=seagreen]
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
try[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
'D:\MSSQL\data\name_Log.LDF'[vbcol=seagreen]
|||Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com said...
[vbcol=seagreen]
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
> 1. Start database in emergency mode
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
> You might be able to use bulk copy program (bcp), simple SELECT commands, or
> use DTS to extract
> your data while the database is in emergency mode. After this database will
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
> Thanks
> Hari
> MCDBA
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'

Wednesday, March 7, 2012

restoring from .bak file

Tried restoring a database from .bak file through Enterprise Manager and also by usng the following code :

RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH
REPLACE,
RECOVERY,
MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'

But, I get an error :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'd:\mssql7\data\apm.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

I have got the two filenames i.e."ap0data" and "ap0log" by using the command "restore fileslistonly from disk = c:\dba.bak".

Can anyone help me to do the things rigtly ?This should work !!!

RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'|||Thanks for your help.

I tried the code, but got error:

Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'apm' database.

I don't have the original database. I have just created a blank database and I am trying to restore.|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'

RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2

Run these two and get back with the results|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'

RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2

Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device 'c:\windows\desktop\cmpbk.BAK'.|||Try this :

RESTORE DATABASE DBB FROM DISK = N'c:\DBA.BAK' WITH FILE = 1,
RECOVERY , REPLACE ,
MOVE N'ap0data' TO N'c:\mssql\data\apm_data.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apm_log.ldf'|||Tried :

Server: Msg 3156, Level 16, State 2, Line 1
The file 'c:\mssql\data\apm_data.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.|||sp_helpdb dbb

?|||name = apm
db_size = 2.00mb
owner = sa
bdid = 8
status = select into/bulkocopy, trun. log on chkpt

name = apm_data
fileid = 1
filename = c:\mssql7\data\apm_data.mdf
filegroup = primary
maxsize = unlimited
growth = 10%
usage = data only

name = apm_log
fileid = 2
filename = c:\mssql7\data\apm_log.ldf
filegroup = null
maxsize = unlimited
growth = 10%
usage = log only|||am clutching at straws now

RESTORE DATABASE TestDB FROM DISK = N'c:\DBA.BAK' WITH
MOVE N'ap0data' TO N'c:\mssql\data\apb1_data1.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apb1_log1.ldf'

i mean try restoring to a completely new database ... let the restore statements create the db|||Hey, It worked.

Thank you very much, Sir|||now you can use the sp_renamedb command to change it to the name you want.

Wednesday, March 28, 2012

Restoring to remove standy/read-only mode.

Monday, March 26, 2012

restoring the database has failed

restoring the database has failed

restoring the database has failed

Friday, March 23, 2012

Restoring SQL2000 DB to SQLExpress on different machine

Restoring SQL2000 DB to SQLExpress on different machine

Wednesday, March 21, 2012

Restoring SQL 2000 database to another SQL Server

Tuesday, March 20, 2012

restoring msdb with SQL 2000

restoring msdb with SQL 2000

restoring msdb with SQL 2000

Friday, March 9, 2012

Restoring from MDF and LDF

Restoring from MDF and LDF

Wednesday, March 7, 2012

restoring from .bak file

database problem

Blog Archive

About Me