November 22, 2002 at 4:10 am
We have a virtual shared account on a host. I used Database=>All Tasks=>Backup Database and backed up our database from our database on our host to my hdd. After that I used Restore Database in my database and restored the database to my local SQL Server.
The database size on the host is 124 mb.
The backup file is 50 mb.
The restored database on my SQL Server is 227 mb.
How come the sizes are so different. I guess the backup uses some compression but why the restored database is so big? I'm asking this because I need to upload this database to a new server and we only have 200 mb SQL Server space on this new host. What will happen if I upload this 227 mb database the the new server, will it be 227 mb too?
November 22, 2002 at 5:25 am
How big is the transaction log on your machine compared to the remote one and how did you compare the two, total size or mdf size? Also, when the backup was taken did you check the DB size beforehand or after?
Finally, what OS version are you running and what HD format FAT or NTFS, differences in this can cause size differences but I have not seen anything of that big a difference.
Edited by - antares686 on 11/22/2002 05:26:30 AM
November 22, 2002 at 9:18 am
WHen you backup the databse the size of the backup file is the size of your used .mdf file.For exapmle if you have afile of size 100 MB and only 50 MB is used then your backup size is going to be 50 MB only.
But wehn you restore the database the database size is the size of the .mdf file +transaction log and whaterver free space you have in the databse .
The best this you can do is shrink your Mdf and transaction log files if they have too much of free space and then take the backup and restore it.
November 22, 2002 at 3:38 pm
Antares686:
The sizes I mentioned here are the "Size" info on the Properties page of the databases.
124mb is the total size of the remote server.
227mb is only the mdf file of the local database (440etc in total size!)
The remote host is Windows 2000. Mine is Windows XP Pro. I don't know the remote file system but I use Fat32.
I thought of something else. What if I try to export the database directly from the old server to the new one, using DTS Export Wizard, without me downloading/uploading it? Do you think the size of the database on the new server will be the same as the old one?
Edited by - kensai on 11/22/2002 3:41:09 PM
November 23, 2002 at 4:01 pm
No because various things will cause it to grow during the transaction and building of indexes. Doing a shrink and and update on usage and stats can correct.Same thing sometimes is the case with backup restores but this seems not always.
I usually on a restore run
DBCC DBREINDEX
DBCC UPDATE USAGE
sp_updatestats
DBCC SHRINKDATABASE
then see what the size is like.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply