1

I have a large (90 GB) database. About half of the space is taken up by two tables that are no longer used. I intend to truncate these two tables and as I understand things this won't free up the space (on the disk) assigned to this database until I shrink it. I don't want to shrink it at the moment.

There is an overnight backup process. Will it be possible for me restore this database to a server that only has 50GB space free or will the restore create a 90GB database that needs a shrink?

  • 1
    What is the version of sql server where you have this 90 GB database – KASQLDBA Feb 2 '15 at 10:00
  • 2008, sorry woulda included that in the question. I assumed it was the same for all versions. – Mikey Mouse Feb 2 '15 at 10:01
  • Ok, so have you tried backing up the 90 GB database using backup compression feature first? if not enable compression and try to see what's the size of backed up db now after compression feature? – KASQLDBA Feb 2 '15 at 10:05
  • The backup is compressed. It comes out as 15gb. But the restore wants the full 90gb before it'll even start unpacking. I haven't done the truncate yet, I just wanna get an impression of what will happen – Mikey Mouse Feb 2 '15 at 10:09
  • If you are sure those tables are not in use, backup the tables, truncate, shrink and do the fragmentation once shrinked. Else get some space available on that other server. – KASQLDBA Feb 2 '15 at 10:17
1

I believe you are short of space on that other server where you want to restore:

So, yes you can restore if you have valid amount of space added on that server:

Else, you can go ahead with truncate process as yo are sure those 2 tables in DB are no more in use:

Before that make sure you backup those 2 tables, Various methods to backup can be referred from below:

http://www.sqlserver-training.com/how-to-backup-sql-table/-

Once you have the backup, run the shrink process once to get the free space available on DB to release the same from Disk as well.

Once done with shrinking, do a fragmentation and check the size of backup.

Do a full backup with compression after running FRAG job:

Now once size has been limited as required, you are good to restore the DB:

1

Are you using the compression function in SQL 2k8? If so then it should work.

I would personally restore this database onto a Dev/Test server and just carry out the scenario you have stated and see what size a full backup is.

  • Yeah, the backup file is compressed down to 15gb. I'm just wondering before I start anything if the total size will be restored with it – Mikey Mouse Feb 2 '15 at 10:11
  • You will have to shrink it down prior to backing it up otherwise it will just restore to 90gb in size. – Parvinder Nijjar Feb 2 '15 at 10:13
  • ok, that's what I needed to know – Mikey Mouse Feb 2 '15 at 10:13

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.