2

I recently was asked to ship a .bak file of our database to a client, and through reindexing all tables with a 100% fill factor (to make sure there were no empty slots), and resizing the transaction log, and 'shrinking' the file was still large (before 7zipping).

I can't find the information about what happens to index data during a .bak anywhere. So, the question is, does a .bak file exclude the index data already? or is there a way of stripping out all index data before taking a .bak and just rebuilding it on restore?

I did try looking for similar posts before posting, apologies if this has already been asked/answered.

Are there a known set of steps to make the smallest restorable .bak file when you

a) don't care about transcations (you're in single user mode, all transactions are committed).

b) don't care about indexes/statistics which can be rebuilt

c) don't care how long it takes to restore.

GilesDMiddleton
  • 385
  • 3
  • 9
  • Yes, a backup will include ALL allocated pages for whatever you're backing up (database, filegroup, etc). So that will include all supporting objects like indexes, statistics... The only log data that will be included are log entries made after the backup starts, and before it finishes the data reading phase. – db2 Apr 21 '11 at 15:03

1 Answers1

2

Try scripting out all your indexes and drop your indexes using one of the scripts out there then take a backup, restore your database then rebuild your indexes using your scripts.

DaniSQL
  • 1,107
  • 7
  • 12