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:
Showing posts with label failed. Show all posts
Showing posts with label failed. Show all posts
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'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
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:
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 SQL database with failed backup
I am needing to restore a SQL server 7 database. I had a backup, but the media it was on has failed. I have copies of the folder the original database was in (.mdf files and such), but I was wondering if there is a way to re-integrate these files in order to pick up where we left off. Thank you.
if you have copies of .mdf and ldf file you can attach that file. read about sp_attach_db in BOL.
Madhu
|||Thank you very much; after some tinkering (and thanks to the information you gave me), I was able to restore the database. This has saved me countless hours of downtime; thank you!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'
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'
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'
Tuesday, February 21, 2012
Restoring database in SQL server 2005 Beta 2
Hi guys,
I have a problem with restoring database in SQL server 2005 Beta 2:
Restore failed for Server '[ZEDNIK\TELBILL]'. (Microsoft.SqlServer.Smo)
-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.852.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
-
=============================================
System.Data.SqlClient.SqlError: The backed-up database has on-disk structure version 611. The server supports version 587 and cannot restore or upgrade this database. (Microsoft.SqlServer.Smo)
I have searched for some patches or something, but I haven't found anything.
Please if some of you know how to solve this problem, HELP!!!
Thanks,
Tomas
where did you obtain this database backup from? Was it from a later version of SQL Server 2005? If so, I don't think you can restore a database backup to an earlier beta version of SQL 2005.What you need to do is upgrade you version of SQL Server to the version that created the db backup, then your restore should work.
Restoring database in SQL server 2005 Beta 2
Hi guys,
I have a problem with restoring database in SQL server 2005 Beta 2:
Restore failed for Server '[ZEDNIK\TELBILL]'. (Microsoft.SqlServer.Smo)
-
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.852.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
-
=============================================
System.Data.SqlClient.SqlError: The backed-up database has on-disk structure version 611. The server supports version 587 and cannot restore or upgrade this database. (Microsoft.SqlServer.Smo)
I have searched for some patches or something, but I haven't found anything.
Please if some of you know how to solve this problem, HELP!!!
Thanks,
Tomas
where did you obtain this database backup from? Was it from a later version of SQL Server 2005? If so, I don't think you can restore a database backup to an earlier beta version of SQL 2005.What you need to do is upgrade you version of SQL Server to the version that created the db backup, then your restore should work.
Subscribe to:
Posts (Atom)