Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

IT realised there was a disk write error on our production SQL Server 2005 and hence was causing the backups to fail. By the time they had realised this the nightly backup was old, so were not able to just restore the backup on another server.

The database is still running and being used constantly. However DBCC CheckDB fails. Also the SQL Server backup task fails, Copy Database fails, Export Data Wizard fails. However it seems all the data can be read from the tables (i.e using bcp etc)

Another observation I have made is that the Transaction Log is nearly double the size of the Database. (Does that mean all the changes arent being written to the MDF?)

What would be the best plan of attack to get the database to a state where backups are working and the data is safe?

  • Take the database offline and use the MDF/LDF to somehow create the database on another sql server?
  • Export the data from the database using bcp. Create the database (use the Generate Scripts function on the corrupt db to create the schema on the new db) on another sql server and use bcp again to import the data.
  • Some other option that is the right course of action in this situation?

The IT manager says the data is safe as if the server fails, the data can be restored from the mdf/ldf. I'm not sure so insisted that we start exporting the data each night as a failsafe (using bcp for example).

IT are also having issues on the hardware side of things as supposedly the disk error in on a virtualized disk and can't be rebuilt like a normal raid array (or something like that).

Please excuse my use of incorrect terminology and incorrect assumptions on how Sql Server operates. I'm the application developer and have been called to help (as it seems IT know less about SQL Server than I do).

Many Thanks, Amit

Results of DBBC CheckDB:

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 823, Level 24, State 3, Line 1
The operating system returned error 1(error not found) to SQL Server during a write at offset 0x00000674706000 in file 'G:\AX40_Dynamics_Live.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
share|improve this question
    
Just a note on the transaction log part. Your transactions are probably being written to the database and the most likely cause of the log size is that the database is in full recovery without any log backups being done. With a full recovery database, it maintains the log files so that you can restore to any point in them. A log backup will clear out any records that have already been written to make room for new logs. So if there's no log backup, it just keeps growing. Definitely check into it to make sure nothing else bad is happening but I'm guessing that's your issue. –  Sean Howat May 18 '10 at 13:46
add comment

4 Answers

Firstly, be really really careful - you don't want to make things worse. I strongly recommend consulting Microsoft Product Support (you'll have to pay), getting an expert in, or look online for stuff from Paul Randal.

You mention that there is an old backup.....Have you tried backing up the transaction log (the GUI Management Studio allows you to do this easily), and then restoring the old backup and this new transaction log backup - on a new, backup server?

share|improve this answer
add comment
  • Fire IT. Obviously incompetent.

  • Try dropping and recreating all indices. If you are right about all data being redable, the write error must be in an index - ergo you can "work around it".

  • LDF larger MDF means logs are possibly not backed up. Given demonstrated incompetence of IT admins this is highly likely.

  • IT manager does not know what he says. If server fails there is no guarantee the mdf/ldf is still redable without external tools (read: possibly lots of money). It is unlikely, but IT manager should know he currently risks company. Well, then - he hired IT, obviously.

Try a dbcc checkdb, post the results here so that we can actually help you.

  • Check whether your database is current (patch level wise).
share|improve this answer
    
Thanks for the reply. I just checked and the backups are in SIMPLE recovery mode. (I have added the DBCC CheckDB results above) –  damitamit May 18 '10 at 14:30
1  
Come on, be fair to IT. They shouldn't have to know how to drive every product under the sun. Besides, you don't know the situation - SQL Server might not be under their jurisdiction. Example - where I am, we have somebody else who looks after SQL Server, it just happens to sit on my hardware. I know very very little about SQL Server, neither am I expected to - that's for somebody that actually knows how the thing works. Backups go to my tapes, but the SQL people must set the backups up correctly. IT have noticed there's a disk problem before it's died, so they're not totally incompetent. –  Ben Pilbrow May 18 '10 at 17:02
    
I am fair. I don't expect MY it do run every product under the sun - I expect them though to be smart enough not to ruin my database by having a totally dump backup procedure, ignore standard procedures. –  TomTom May 18 '10 at 17:20
    
The OP didn't specify that there were no other backups, just "old" backups. Now that could be read as (I hope not) only 1 backup, which is overwritten with the new one, or that the old backup is good but just contains old data. Again, it should be up to the SQL people to say "Hey Ben, I want to do a test restore of one of our SQL databases - can you please grab the tape" - not up to me to 1) do it (I'd probably restore old data into a production database :]) or 2) prompt them to do it. –  Ben Pilbrow May 18 '10 at 17:33
1  
I agree that it sounds like the backup procedure leaves something to be desired, but I wouldn't go around trashing their IT without knowing all the facts WRT who is responsible for what. If my SQL server failed tomorrow, I would get them on another box ASAP, configure as required, but it's up to them to get their data back. I'd have to trust they took due diligence WRT backups and testing restores. If it failed, I'm afraid IT aren't at fault [we did our bit and got another server], but they are. If anybody dared accuse IT of being incompetent in that situation, there would be hell to pay. –  Ben Pilbrow May 18 '10 at 17:55
show 2 more comments

That DBCC output looks like it can't get exclusive access to the database, so it's refusing to even run. If you want to throw everyone out of the database so it will actually run, use the following command:

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you've done that, DBCC CHECKDB should be willing to cooperate. After you're finished, put the database back in multi-user mode:

ALTER DATABASE database_name SET MULTI_USER
share|improve this answer
add comment

The first thing that I'd recommend doing is taking the application offline and running a checkdb to ensure that the database is not corrupt. If it isn't then things are looking up. If it is then we need to figure out just how corrupt and recover what data that we can.

To get checkdb to run at right now you'll need to put the database into single user mode probably then run dbcc checkdb for each of the databases.

What happens when try attempt to manually run a database backup?

Based on the fact that this is dynamics (and it runs your business) you might want to spend the money and get either PSS on the phone or a SQL consultant to look at it (even with a SQL consultant you may still be calling PSS).

As to the actual question in the title, you don't want to just export the data and reimport it. Odds are that won't be supported.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.