<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Corrupted Database
13 posts, Page 1 of 2
1
2
»»
Corrupted Database
Rate Topic
Display Mode
Topic Options
Author
Message
@SQLFRNDZ
@SQLFRNDZ
Posted Tuesday, January 29, 2013 12:07 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:05 PM
Points: 412,
Visits: 857
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
--
SQLFRNDZ
Post #1412774
naga.rohitkumar
naga.rohitkumar
Posted Tuesday, January 29, 2013 12:35 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 525,
Visits: 998
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 & Regards
NAGA.ROHITKUMAR
Post #1412780
@SQLFRNDZ
@SQLFRNDZ
Posted Tuesday, January 29, 2013 12:48 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:05 PM
Points: 412,
Visits: 857
Already tried those and no luck
--
SQLFRNDZ
Post #1412789
naga.rohitkumar
naga.rohitkumar
Posted Tuesday, January 29, 2013 1:10 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 525,
Visits: 998
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 & Regards
NAGA.ROHITKUMAR
Post #1412796
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 1:33 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665,
Visits: 29,918
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1412803
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 1:35 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665,
Visits: 29,918
naga.rohitkumar (1/29/2013)
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
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1412805
@SQLFRNDZ
@SQLFRNDZ
Posted Tuesday, January 29, 2013 2:36 PM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:05 PM
Points: 412,
Visits: 857
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..
--
SQLFRNDZ
Post #1413345
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 3:26 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665,
Visits: 29,918
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1413362
@SQLFRNDZ
@SQLFRNDZ
Posted Tuesday, January 29, 2013 4:18 PM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:05 PM
Points: 412,
Visits: 857
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.
--
SQLFRNDZ
Post #1413384
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 4:20 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665,
Visits: 29,918
Can you query the sys.sql_modules view?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1413387
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
