Restore/Log Growth Question:

[mobius]

Restore/Log Growth Question:

We have an SQL2K SP3 Decision Support Server which will be receiving a nightly data dump from the offsite production system. We’ve decided on a backup/restore process to move the data. We’re currently testing the process and have come across a problem. The test backup file is only 500MB, the real DB will be approx. 20GB, and when it is restore the DB is fine but the log file has ballooned to 8 GB.

When the DB is restored the properties of the production DB are forced over our settings. These include no truncation of log on checkpoint and a full recovery model (the destination DB will have simple recovery). We’ve been through the books online and search online forums, but have not found information on this particular problem. We can, of course, shrink the log file after restoring but are concerned about the amount of dead space needed when the real DB is delivered.

Any help would be greatly appreciated.

[skhanal]

To restore a database, the destination has to be as big as the source.

If you are concerned with the disk space, then you should do frequent transaction log backup and restore rather than full backup restore.

[mobius]

Thank you for the reply,

Agreed, we’ve allocated space for the destination DB to be as large as the source. The disk size for the database isn’t the problem; the growth of the log file due to the restore is of concern. We do not understand why the log is growing to size of 16X that of the database. We’re looking for a way to keep this from happening so that we don’t have to allocate a great deal of space for the log file during restoration.

[MAK]

before backing up the database do the following.
0. use database
1. checkpoint
2. backup log with truncate
3. dbcc shrinkfile (logfile)
4. backup the database
so that you will have minimum size for log file
5. copy backup file to destination server
6. restore database in the destination server

[rmiao]

It depends on recovery model on source db, and you can shrink it after restoring.

[mobius]

The backup file is from a full backup, no log files supplied. The source is an outside firm, we’re in an ASP environment, and using a Full Recovery Model. Thus we have little to no control of their operations, and it seems to us that the problem is on the destination side. The flow is as follows:

Source DB: 500 MB

Supplied backup file: 500 MB

Pre – restoration destination DB: 1 GB

Pre – restoration destination Log: 1 MB

Post – restoration destination DB: 500 MB

Post – restoration destination Log: 8 GB

[MAK]

Ask the source to do the following before sending the backup file to you.

0. use database
1. checkpoint
2. backup log with truncate
3. dbcc shrinkfile (logfile)
4. backup the database
so that you will have minimum size for log file

or

ask them to send .mdf file only so that you can use sp_attachdb instead of restoring it

[MAK]

refer "Partial Restore Operations" in books online on handling filegroups. ask the outside firm to make changes to their filegroup, so that you can restored only one filegroup.


I havent tested it.

[mobius]

Mak,

We’ve tested your instructions 0-4 on the test db, it worked well. Thanks for the help, wish us luck getting our source firm to do something so simple.

Thanks again!