recovering a database w/no log

I am trying to attach a database with no log. Receiving
the following error:
Error 1813: Could not open new database 'optima3'.
Create database is aborted. device activation error. The
physical file name 'g:\logs\optima_log.ldf may be
incorrect.Try sp_attach_single_file_db
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ababl" <anonymous@.discussions.microsoft.com> wrote in message
news:011d01c3d3aa$227af390$a101280a@.phx.gbl...
> I am trying to attach a database with no log. Receiving
> the following error:
> Error 1813: Could not open new database 'optima3'.
> Create database is aborted. device activation error. The
> physical file name 'g:\logs\optima_log.ldf may be
> incorrect.

Recover SQL Server v8 database on 2005?

I have managed to attach a database to my new instance of SQL Server 2005. The database orignally went into suspect mode on SQL Server v8.0. I have managed to get it into emergency mode but cannot see any tables. DBCC CHECKDB etc do not work. I get the error saying to upgrade the database to the newest version.

Any ideas? Thanks in advance!

What is the error you get that causes it to go suspect?

Try ALTER DATABASE <dbname> SET ONLINE to see the error.

|||

Part of attaching a database from an older version of SQL on a newer instance involves making any required alterations to the database structure needed by the new version.

If the database was suspect on SQL 2000 before it was detached, that may well prevent those alterations from happening.

Why are you trying to attach this database to a newer version of SQL instead of resolving the suspect status before moving it?

|||The mdf and ldf files were recovered from a failing RAID array before the whole thing died. So there is not actually a way to attempt a repair on the original system.
|||Kevin,
Will attaching these data files to a SQL Server 2000 make difference , could be a different hardware

Thanks
Vishal|||I agree, I would try and get the database back to a SQL2K instance, fix your problems there and then move to 2005.|||

Vishal Gandhi wrote:

Kevin,
Will attaching these data files to a SQL Server 2000 make difference , could be a different hardware

Thanks
Vishal

Yes, because the SQL 2000 instance will not need to perform the upgrade step prior to bringing the database online.

|||Hi,

If you used sp_resetstatus to make the db status to emergency mode, then you should restore the db using the latest backup available.

Recover db from .mdf and .ldf files in sql server7.0

Hello,
Have you tried the command sp_attach_single_file_db ?
This will attach you datafile, but not your log file as it
seems the log file is currupt, so you may have some data
loss.
J

>--Original Message--
>Hi,
>how to recover a user database in sql server 7.0 if I
only have the .mdf file and the .ldf file but no backup
file? Backup file of master database exists.
>Found a utiliy tool called "MSSQLRecovery" on the net
that recreates the database script out of the .mdf-file,
so far so good. But I need the information in the .ldf
file too.. How to get the information in the .ldf file?
>Thanks.
>.
>Yes,
I have tried sp_attach_single_file_db, but as you say I will get some data l
oss with this method.
My problem is the log file never seems to been backuped, so I guess I will g
et quite a lot of data loss.
Thanks,
Siri|||That really depends upon how you have programmed your
application. Depending upon the last commit statement or
chekpoint you may of lost a lot less data than you think.
I strongly doubt if it will weeks, days or hours of lost
information, personally I think it will be minutes.
Try attaching anyway then seeing if there any timestap
fields in you app, when the last one was. That should give
you an indication.
J

>--Original Message--
>Yes,
>I have tried sp_attach_single_file_db, but as you say I
will get some data loss with this method.
>My problem is the log file never seems to been backuped,
so I guess I will get quite a lot of data loss.
>Thanks,
>Siri
>.
>

Recover db from .mdf and .ldf files in sql server7.0

Hello,
Have you tried the command sp_attach_single_file_db ?
This will attach you datafile, but not your log file as it
seems the log file is currupt, so you may have some data
loss.
J

>--Original Message--
>Hi,
>how to recover a user database in sql server 7.0 if I
only have the .mdf file and the .ldf file but no backup
file? Backup file of master database exists.
>Found a utiliy tool called "MSSQLRecovery" on the net
that recreates the database script out of the .mdf-file,
so far so good. But I need the information in the .ldf
file too.. How to get the information in the .ldf file?
>Thanks.
>.
>
Yes,
I have tried sp_attach_single_file_db, but as you say I will get some data loss with this method.
My problem is the log file never seems to been backuped, so I guess I will get quite a lot of data loss.
Thanks,
Siri
|||That really depends upon how you have programmed your
application. Depending upon the last commit statement or
chekpoint you may of lost a lot less data than you think.
I strongly doubt if it will weeks, days or hours of lost
information, personally I think it will be minutes.
Try attaching anyway then seeing if there any timestap
fields in you app, when the last one was. That should give
you an indication.
J

>--Original Message--
>Yes,
>I have tried sp_attach_single_file_db, but as you say I
will get some data loss with this method.
>My problem is the log file never seems to been backuped,
so I guess I will get quite a lot of data loss.
>Thanks,
>Siri
>.
>

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datas
Hi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database
|||Or this
http://support.microsoft.com/default...b;en-us;165918
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database
|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.co m...
> Or this
> http://support.microsoft.com/default...b;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>
|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
--
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>>i done all your writes but dont work yet.database status change to suspect
>> mode and dont permit to recover database
>|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

recover crashed database

our sqlserver has crashed and try to attach mdf file to server but sql serve
r
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/defaul...kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/defaul...kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>|||Open a case with Microsoft Support and see if they have any means of salvagi
ng any data. Of,
assuming that you can access the database at all, export all data to a new h
ealthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql ser
ver
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>

recover a database without backup

You can try sp_attach_db (or, preferred in 2005: CREATE DATABASE ... FOR ATTACH). It is only
guaranteed if you first detached the database, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:8BB1D107-6E4B-42A6-B5AB-152D5693FF94@.microsoft.com...
> Hi,
> Is there any way to recover a sql database from a file system backup (.mdf
> and .ldf files copies taken every night while sql was up and running)? The
> client does not have any sql backups. I know he should ...
> Thanks very much.
> --
> Brian
It is worth a try, it is likely it will work even though you didn't detached first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:6B4403AF-8256-4086-825C-852CF5078B77@.microsoft.com...[vbcol=seagreen]
> unfortunately it was not detached... those files restored were just image
> copied during OS backup process... thanks anyway.
> --
> Brian
>
> "Tibor Karaszi" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OxIuUgpgHHA.2640@.TK2MSFTNGP06.phx.gbl...
> It is worth a try, it is likely it will work even though you didn't
> detached first.
Be forewarned, even if it it DOES work... you may have corruption.
I'd certainly run a DBCC CHECKDB on all the databases. (with all the flags)

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
> news:6B4403AF-8256-4086-825C-852CF5078B77@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html