July 27, 2012 at 11:59 am
backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.trn'
with NO_TRUNCATE
You do not use NORECOVERY in a disaster situation (damaged or missing data file). You use it when backing up a working and online database in preparation for a restore.
Continue after error was added in SQL 2005.
Is the DB in full recovery and do you have log backups at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2012 at 7:55 pm
when i run this...
backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.trn'
with NO_TRUNCATE
Server: Msg 3033, Level 16, State 1, Line 1
BACKUP DATABASE cannot be used on a database opened in emergency mode.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
i have no backup log...
FYI, my database (PMSdb.mdf) was corrupted before this when i attach database and the result is...
Error823: I/O error (bad page ID) detected during read at offset 0x000000e1b78000 in file
'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\PMSdb.mdf'.
actually, what is the correct step?
July 28, 2012 at 5:01 am
If you don't have any log backups, then you'll just have to restore the last full backup that you have and accept the data since then lost
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2012 at 4:14 am
if i restore the last full backup, how about the data since then lost?
July 29, 2012 at 7:00 am
Was the database in full recovery model?
Do you have log backups since that last full backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2012 at 10:47 pm
This was removed by the editor as SPAM
July 30, 2012 at 10:45 am
that is for the demo version only, it just for viewing & cannot take the data unless pay more for it 🙂
July 30, 2012 at 10:53 am
slizam (7/30/2012)
that is for the demo version only, it just for viewing & cannot take the data unless pay more for it 🙂
Of course. And some don't work after you've paid either. Buyer beware, they may get something back, they may get nothing back.
But you haven't answered my question about log backups. Do you have any?
If not, with the DB in emergency mode can you query any of the tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 7:34 pm
oh, i'm so sorry...forgot to ans it. i have old backup before hard disk failure. i just get mdf & ldf file only after retrieving. i try to set emergency mode but certain table can view correctly & some table show;
"I/O error (bad page ID) detected during read at offset 0x000000f9a70000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\PMSdb.mdf'."
after viewing. so how to repair it using SQL command?
July 31, 2012 at 1:54 am
It's not repairable. The damage is in the system tables
I suggest you export all the data that you can from the tables that you can query (bcp out or similar) then restore the latest backup that you have. Then you can use the exported data with something like Redgate's SQLDataCompare to get the restored database as close to current as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2012 at 9:25 am
yes, i know that the system tables was damaged especially sysindexes & sysindexkeys. the important thing is a data from the main table. what Redgate's SQLDataCompare can do for this? i ever use stella phoenix, kernel, systool & mdfviewer but it just viewing only & cannot export to other file...
July 31, 2012 at 10:55 am
As I said, export what data you can from the damaged database to flat files (use BCP out or just query and copy). Then restore the latest version and use some tool (any tool, datacompare's just the one I know) to sync the restored database with the data you exported.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2012 at 2:13 am
how to use BCP out? i never do this b4. can u show the related articles? tq 4 info...
August 10, 2012 at 5:10 am
Books Online, the section bcp (stands for bulk copy program)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2012 at 6:50 am
Hi,
You shoul be try any third party data recovery software for recover your data of SQL.
There are so may software you can select one of them for your information i can recommended you one of them which is "Kernel for SQL Server Repair" you can try this software to repair SQL server.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply