January 29, 2013 at 12:07 am
Issue: The database in corrupt status
So far Tried:
ALTER DATABASE @database SET EMERGENCY;
GO
ALTER DATABASE @database SET SINGLE_USER;
GO
DBCC CHECKDB (@database, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Recieved error:
Error: The database @database is in version 611. the database need to upgarde to the latest version.
Background:
The server is migrated from sql server 2005 to 2008r2 and compitiblity mode haven't changed to 100
Also, I have tried this process from Paul's blogpost :
http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
But no luck and again with same error.
I understood 611 is sql server 2005 and stuck with unable to run checkdb on suspect dB.
I thought initially it might be log issue and teh I created new database with same name taking offline original and swapping the data file and still no luck.
Any help is appreciated !
Let me know if anyone have faced the issue,
Note: There are no backup files for this dB.
This is report database and we don't care about the data in it but needs to get all sp's
January 29, 2013 at 12:35 am
Hi,
I have face this type of issue at the time of dc dr drill but we have backup files with us .
1. try to restore the last latest backup if have [if u dont have u should the drop the database and create a new one data should not be retrived without backup]
if not
or
2. copy the mdf and ldf files try to detach the database which is in suspect mode create a new database with the same name and try to attach the mdf and ldf files and check
or
3.rename the current database if it allows and create new and copy the mdf and ldf files inthe new database path and attach with overwrite[may be ]
Thanks
Naga.Rohitkumar
January 29, 2013 at 12:48 am
January 29, 2013 at 1:10 am
as mentioned to u if there is no backup how can we restore and make the database active.some where in u r server any backup is available or not check and restore if it is the last backup thn it vl allow for restoring
if not i wont
try this code
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
Thanks
Naga.Rohitkumar
January 29, 2013 at 1:33 am
Can you run CheckDB on the 2005 database?
Do you have a backup from before the DB was upgraded to SQL 2008?
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
January 29, 2013 at 1:35 am
naga.rohitkumar (1/29/2013)
try this codeEXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
That is absolute last resort when all else has failed, there are no backups and there's no other possibility for getting the DB back. It is not a first step, the best approach or the initial option.
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
January 29, 2013 at 2:36 pm
GilaMonster (1/29/2013)
Can you run CheckDB on the 2005 database?Do you have a backup from before the DB was upgraded to SQL 2008?
Hi Gila.,
No we cannot run the checkdb as we are getting error as mentioned earlier
No backups available
Actually, we don't care of the data lost but will good if we can get that too but need the SProcedures..
January 29, 2013 at 3:26 pm
Can you run CheckDB on the 2005 database? Or do you not have a pre-upgrade copy?
Did the DB switch into emergency mode without error?
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
January 29, 2013 at 4:18 pm
Can you run CheckDB on the 2005 database? Or do you not have a pre-upgrade copy?
--Cannot run dbcc checkdb, also no pre-upgrade backups
Did the DB switch into emergency mode without error?
Yes, successfully switched to emergency mode and also to single_user mode after.
January 29, 2013 at 4:20 pm
Can you query the sys.sql_modules view?
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
January 29, 2013 at 5:18 pm
January 29, 2013 at 5:42 pm
@SQLFRNDZ (1/29/2013)
I haven't queried that and I would try querying that in an hour or so.Could you please tell me what about it?
It's one of the easiest ways to get all the build scripts out of your database to re-shell the database. I'm not sure if that's where Gail is going here though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 29, 2013 at 11:42 pm
@SQLFRNDZ (1/29/2013)
Could you please tell me what about it?
It contains the definition of all procedures, views and functions in your DB, which is what I thought you wanted out.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply