1

I have a backup which in file explorer is roughly 160GB.

If I run

select sum((size*8)/1024) From sys.database_files where type=0

I get a result of 169493, fine.

When I try to restore this database to another server (the old DBA set up a job which restores the database automatically each morning from the latest backup, then runs DBCC CHECKDB on it) it fails. If i try RESTORE VERIFYONLY it turns out I need 288GB in order for this to work. Why? The backup is not compressed.

Thanks

  • 4
    SQL Server need to create the database files with same size as they has when the backup was produced. I'm not clear on where that SELECT were ran. Also, I assume you mean RESTORE FILELISTONLY and note RESTORE VERIFYONLY? Just trying to understand... In short, are you saying that the size of the files do not match, between sys.database_files on the originating server and RESTORE FILELISTONLY? – Tibor Karaszi Oct 16 at 14:35
  • Are you sure it isn't compressed? Can you check. And see if it's set globally. Here's another way. The query you ran is at the DB level for rows only... there's no way for us to know for certain this is correct. – scsimon Oct 16 at 14:40
  • 2
    I think @TiborKaraszi is on the correct track, check the the source database on the server where it is backed up at and see what the size of the data and log files is. There is probably a bunch of unused space allocated. – James Jenkins Oct 16 at 14:44
  • 1
    Adding to @TiborKaraszi See this answer – scsimon Oct 16 at 14:55
2

From your question, it sounds like the restore has been working daily, for some time. As of today it suddenly stopped working.

Yesterday it restored fine with a restored size of around 160GB today it needs 288GB to restore.

One of two things probably happened (or a combination of both)

  1. The data files (mdf.ndf.) grew a bunch either from lots of new data coming in, or from someone increasing the file size.

  2. The log files grew a bunch, probably because of an auto grow when someone ran a big ugly query.

You will need to look at the source database to find out what is bigger than it was yesterday. Then you will need to figure out why it grew. Then you will need to make a decision with the business on if the source should be shrunk back down, or if the secondary server needs to have more space added to it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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