0

I recovered a server in log shipping on SQL Server 2012 The log shipping works well, the backup log is done well every 15 minutes.

There are 2 bdd on this server, one of the backup bdd well it is 50 go The other database has not had a FULL backup since March 16...

It is 500 GB and at 78% it stops with this error message

Msg 3203, Level 16, State 1, Line 1
Read on "D:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\TOTO.mdf" failed: 1(Incorrect function.)

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Running a DBCC CHECKDB WITH PHYSICAL_ONLY returns no errors.

The Event Log in Windows displays the same error message.

1
  • Welcome to the DBA.SE community. Is a BDD a backup device? What is the command / tool you are using to back up the database? Could you possibly post the command in your question by hitting the edit link? Thanks.
    – John K. N.
    Dec 31, 2022 at 10:39

1 Answer 1

1

Note: as per John K. N.'s comment, adding more details (the exact commands you are running, further details about the storage devices in your server, the layout of your DB if it isn't the default one-data-file-and-one-log-file, etc) to your question will help get better answers, in writing my answer I've made assumptions regarding details we don't have.


I suspect you have physical damage on the source disk, which DBCC CHECKDB is not detecting. Note that DBCC CHECKDB is not doing a full scan for bad blocks on the databases files, even though the existence of an option named PHYSICAL_ONLY might imply this.

See the documentation for that and other options at https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql particularly:

We still recommend that a full run of DBCC CHECKDB be performed periodically [because PHYSICAL_ONLY skips some checks]

If you do have a problem with your physical storage it is important that you get the data off quickly and write as little as possible so perhaps take the system offline as the issue could spread or otherwise worsen. So by all means let CHECKDB do all the scans it can but for now (until you've ruled out physical storage problems) avoid having it try repair the DB in-place.

Possible next steps:

Perhaps stop SQL Server and use the operating system's tools to scan for and attempt to recover (or at least reallocate around) the bad blocks. This may allow the backup to complete, though likely with duff data in the affected pages as the bad block reallocation probably couldn't pull the original data to copy info the newly allocated blocks, so you can restore it to another server and use DBCC ... to fix-up affected structures there (don't try to repair the DB on a damaged drive, this could make things worse). Keep the original DB where it is, read-only, then if you find this process has left you with large chunks of broken/lost data instead of just one or two pages (perhaps because the damage was on a key structure not just a couple of data pages so the repair couldn't do a great job) then you may be able to extract some if it from the old location with other techniques (and info from DBCC ... about where it found corruption.

One further significant note: If this is a high value database I would consider hiring the services of a professional data recovery expert, rather than trying to fix it yourself with my guesswork and maybe making things worse!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.