locked
Missing chuncks of data from backup files RRS feed

  • Question

  • Hello all,

    We recently restored a production database from a day before to our development environment using the full backup files and an Incremental backup. Our developers then states that they are missing chunks of data from the restored database which currently exists in production. After a bit of snooping around, it was true; the data was missing.

    RESTORE DATABASE [ProdData]
    FROM  DISK = N'G:\ProdData_backup_2016_04_05_004501_8871617.bak' WITH  FILE = 1, 
    MOVE N'ProdData' TO N'G:\MSSQL\Data\ProdData.mdf', 
    MOVE N'ProdData_log' TO N'H:\MSSQL\Logs\ProdData_log.ldf',   NOUNLOAD,  REPLACE,  STATS = 10
    GO

    The above script was what I used. As you can see I am using the REPLACES option which should overwrite the existing database files. There is nothing fancy that I am doing. So, I retried restoring just the full backup, looked for the same chunk of data. And again, they seemed to be missing. I then went back to a backup file that was taken a week back and did another restore. And again the same data chunks were missing. I ran the DBCC CHECKDB for any corruption in the files and there were none. So, now I am worried. We were doing full backups using the built in SQL compression. So, I tried making a new full backup without the compression and tried the restore. This time though the missing data were there. So, I thought could it be the SQL compression that is causing this? Wanting to validate, I did another backup using the SQL compression and did another restore. The missing data was there as well. So, now I can't explain why the data went missing from those backup files that were running on a schedule.

    Any explanations that you guys can give me would help. Has this happened to anyone?

    Thanks.


    NM


    • Edited by NMan47 Wednesday, April 20, 2016 2:05 PM
    Wednesday, April 20, 2016 2:03 PM

Answers

  • The only reasonable explanation is that the missing chunks had been written to the database after taking the backup you used for the restore.

    Wednesday, April 20, 2016 9:49 PM
  • I've been working with SQL Server since version 1.0 (back in the eighties) and i have never heard about a backup missing data. So id you are asking me, that data wasn't in the database (or was, but not committed - an open transaction) when that backup was produced...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, April 25, 2016 5:58 PM

All replies

  • The only reasonable explanation is that the missing chunks had been written to the database after taking the backup you used for the restore.

    Wednesday, April 20, 2016 9:49 PM
  • If the database is compressed, compressing backups might not reduce their size by much, if at all. see here: https://technet.microsoft.com/en-us/library/bb964719(v=sql.105).aspx
    Thursday, April 21, 2016 5:38 AM
  • A backup operation either work or not. Nothing in between. Same with restore. Compression will not cause data to not be there. So, I agree with Erland, you restored an earlier backup than what you think you did (which which was taken when the data wasn't there). Note that a backup file can contain several backups, and restore without FILE will restore the first one. See RESTORE HEADERONLY.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, April 21, 2016 11:11 AM
  • The change was done two weeks prior to the backup file used for the restore.

    NM

    Monday, April 25, 2016 3:44 PM
  • Hi Tibor,

    I understand what you're saying and that was the first question I asked myself and the developers too. But, I was then told that they had made the stated changes two weeks before and the records did show that it was last modified two weeks ago. And, I can't have picked an earlier file because we only retain the backup files on disk for 24 hours. Anything prior to that gets moved to tape. I had to put in a request for the Storage team to recover a specific file from a week back for me to use it which also didn't have the data we were looking for.

    Also, we create a separate file for each of our backups and I did verify the result brought by the RESTORE HEADERONLY option. It only showed one set of backup.

    Any other suggestions would help.

    Thanks,


    NM

    Monday, April 25, 2016 4:04 PM
  • I've been working with SQL Server since version 1.0 (back in the eighties) and i have never heard about a backup missing data. So id you are asking me, that data wasn't in the database (or was, but not committed - an open transaction) when that backup was produced...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, April 25, 2016 5:58 PM