Restoring backup of a Database with SIZE=MAXSIZE

• abdul.samad.121

  Ten Centuries

  Points: 1049

  More actions

  October 8, 2010 at 3:42 am

  #142968

  Hi,

  Ihave created a database named TransDB1 with SIZE=300GB and MAXSIZE=300GB.

  CREATE DATABASE [TransDB1] ON PRIMARY

  ( NAME = N'TransDB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TestDB1\MSSQL\DATA\TransDB1.mdf' , SIZE = 200GB , MAXSIZE = 200GB)

  LOG ON

  ( NAME = N'TransDB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TestDB1\MSSQL\DATA\TransDB1_log.ldf' , SIZE = 300GB , MAXSIZE = 300GB )

  GO

  I have taken a backup of the above created database which has 10 MB of data; when I am trying to restore the .bak file (of size 3MB) on a machine which has 100GB of disk size , restore is throwing error: not enough disk space; even though I have 10 MB of data in the .bak file and the free space on the disk (on which I want to restore) is 70GB.

  Is that issue occuring because of (SIZE=MAXSIZE) ?.

  Is there any way by which I can restore the .bak file or

  any THIRD PARTY TOOLS which lets me do so overcoming the

  above error ?.

  ** I dont want to change the SIZE=MAXSIZE parameter.

  Please reply to this post as early as possible.

  Thanks in advance.

  Abdul Samad

• Oliiii

  SSCertifiable

  Points: 5328

  More actions

  October 8, 2010 at 4:22 am

  #1234131

  The problem is the SIZE=200GB, the backup command only backup the used pages and not the free space (that's why your backup is only 10MB).

  When you restore, the first thing it tries to do is to recreate the 200GB file, which it can't if you only have 70GB free.

  You need to retore the DB on a drive that has enough space or shrink it a bit before doing the backup.