Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Georgia
    Posts
    3

    Unanswered: SQL 6.5 database device size

    Greetings!

    I have some 6.5 backups that I need to restore to one machine with lots of hard disk space and then I want to data transfer them to another machine where there is less disk space.

    Let's say the size of the .dat file I want to restore is 5G. If I create a 5G database device and database, the restore tells me that the database must be 30G to hold the 5G backup. SO, the database on the machine where the backup was made must be 30G.

    So, I recreate the database and device to be 30G and then restore the backup.

    Now I want to data transfer this database to another machine where disk space is limited. I don't have 30G on this system to make the device so I want to know how big to make the database device to hold the transfer.

    If I look at the database information on the other machine (by editing it), it tells me that the database only has 2G of data space available, so I figure I need to create the new database device to be 28G (even though the backup file was only 5G).

    I don't believe that the 5G backup that I restored into the 30G database really took up 28G leaving me only 2G like SQL is telling me.

    I want to know how to tell how much space the database is really taking up so I will know how big to create the device on the target system where disk space is a premium.

    Do I just look at the 5G backup file and guess how big to create the device on the target machine? Someone suggested looking at the size of the backup file and adding 10%. This seems to work okay for me.

    Is there some query I can run that will tell me how much space the database is really using since the edit database information doesn't seem to be correct (and why is it incorrect?)

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    To restore a backup, the minimum size of the target database must be the size of the source database, even if thesize of the backup is smaller.

    The backup only save the allocated pages, but the address of the pages must fit in the new db... The backup checks first of all this constraint of size !

  3. #3
    Join Date
    Jul 2003
    Location
    Georgia
    Posts
    3

    SQL 6.5 database size

    Yeah - I know that I must create the device and database the same size as it is on the source machine to restore the backup and that all works fine.

    When I want to do the data transfer to another machine though, I don't need to create that device and database to be as big.

    To figure out how big to make it, I just looked at the size of the .dat file, added 10% to it and used that number. Then I just transfer the data from the big database to the smaller one and it fits.

    I was just wondering if there is some other way to know how big to make the device on the other machine besides just adding 10% to the size of the backup .dat file...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •