February 8, 2012 at 2:18 pm
I work for a small software company whose desktop app runs off a small SQL Server 2005 backend. Typically 2005 Express is installed on the user's machine. We strongly and repeatedly advise all of our customers to do regular, preferably daily, backups of their data for obvious reasons.
Well this one didn't, and in fact has never done so, and last night they had a power outage. When they restarted SQL Server, their database came up in a "suspect" state.
Attempting to run DBCC CHECKDB ('CE_DATA') WITH NO_INFOMSGS, ALL_ERRORMSGS returned:
Database 'CE_DATA' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
So I started following Gail's instructions on page two of this thread:
GilaMonster (3/19/2011)
ALTER DATABASE CE_DATA SET EMERGENCYALTER DATABASE CE_DATA SET SINGLE_USER
DBCC CHECKDB('CE_DATA', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE CE_DATA SET ONLINE
Run them one by one if any give an error post it here and do not run anything further.
We did that, but got an error on the third line:
Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Per the error message, we ran DBCC CHECKCATALOG, and got no errors. Any suggestions what we might look at next? I'm quite happy telling this customer "Hey, we told you to make backups, now you're SOL," but if we can help the guy while teaching him a lesson, all the better.
thanks!
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 8, 2012 at 2:42 pm
Tell them to start typing (to re-enter all data)
Seriously....
See if you can query any of the tables (you're in emergency mode, so the DB is 'readable'). Depending on the damage some or all will fail. If some succeed (in full or in part), you may be able to script objects, export data and partially recreate the DB. This is often a time-consuming job as if there's corrupt data in the tables, you'd have to query bit-by-bit (range on the clustered index key)
That said, I don't hold much hope. That error is a harsh one, the DB is so badly damaged that some low-level portion of the storage engine hit a fatal error and didn't allow CheckDB to handle the error.
If checkDB can't complete there is no way that DB will be coming out of suspect state.
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
February 8, 2012 at 2:44 pm
I agree, it sounds pretty hopeless, and the customer realizes it. They're going to pay someone to try and restore the data, and more power to them. If nothing else, they've learned a valuable lesson about making regular backups. I just hope this doesn't cost them too much business (this is an electrical contractor who needed to have a bid out 30 minutes from now... eek).
Thanks for the feedback.
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 8, 2012 at 2:48 pm
ronmoses (2/8/2012)
I agree, it sounds pretty hopeless, and the customer realizes it. They're going to pay someone to try and restore the data, and more power to them.
For what its worth, if they're talking disk recovery, I don't recall ever seeing a case where someone tried that and it succeeded to the point that the DB was usable. Maybe I just see the lost causes though.
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
February 8, 2012 at 5:01 pm
ronmoses (2/8/2012)
I work for a small software company whose desktop app runs off a small SQL Server 2005 backend. Typically 2005 Express is installed on the user's machine. We strongly and repeatedly advise all of our customers to do regular, preferably daily, backups of their data for obvious reasons.
This type of event could happen to more than just this one customer of your firm. MIght I suggest reading the following on scheduling backup for SQL express,
Added new link per Gal Shaw's comment
http://www.codeproject.com/Articles/113461/Schedule-a-daily-backup-with-SQL-Server-Express
Since the software is free or in your case you might be able to purchase it for a modest amount (so as to forestall any legal actions against your firm), you might include it with your systems.
The advantage of including the above in your software is of course a valid sales point your firm could / should be making with potential customers, or for getting existing customers to use the new product.
February 9, 2012 at 1:36 am
Link does not work
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
February 9, 2012 at 6:16 am
February 9, 2012 at 7:18 am
Thank you for that link! I will explore it as soon as time permits.
thanks!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 10, 2012 at 4:56 am
For our SQL Express 2008 R2 databases I wrote the script below which is run via a scheduled task .BAT file which is also below. Would be easy to modify for SQL 2005.
CREATE proc [dbo].[sp_BackupAllDatabases]
AS
declare @temp1 as table (id int, name varchar(100))
-- User Databases
insert into @temp1 (id, name)
select ROW_NUMBER() OVER(ORDER BY [database_id]) AS ID, name from sys.databases where database_id > 4
declare @icnt int = 1
declare @itotal int = (select COUNT(*) from @temp1)
declare @string varchar(1000)
declare @name as varchar(100)
while @icnt <= @itotal
begin
select @name = name from @temp1 where id = @icnt
set @string = 'backup database [' + @name + '] to disk=N''\\your destination directory here\userdbs\' + @name + '.bak'' WITH INIT'
exec(@string)
set @icnt += 1
end
-- System Databases
delete from @temp1
insert into @temp1 (id, name)
select ROW_NUMBER() OVER(ORDER BY [database_id]) AS ID, name from sys.databases where database_id < 5 and name <> 'tempdb'
set @icnt = 1
set @itotal = (select COUNT(*) from @temp1)
while @icnt <= @itotal
begin
select @name = name from @temp1 where id = @icnt
set @string = 'backup database ' + @name + ' to disk=N''\\your destination directory here\\systemdbs\' + @name + '.bak'' WITH INIT'
exec(@string)
set @icnt += 1
end
The batch file is:-
sqlcmd -E -S [Server] -Q "exec master.dbo.sp_BackupAllDatabases"
Hope that helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply