Options
Restored database with focus on log size
colby
Posts: 30
Moving a database to another server can be more easily be done with Red Gate than attach detach methods when network is slow because the compression is so large and my copy time to the other server is so long.
My question is the restoring of the database and the associated log. Sometimes the logs are larger than the database and sometimes the logs are small. How can you minimze the size of the restored log?
For example,
Before the backup, should I truncate the log and then RedGate backup and now the restored log will be as small as possible. After restored, now start transactional backing up of log.
Or After backup and restore, truncate the log and then start hourly backing up of the log.
Thanks
Colby
My question is the restoring of the database and the associated log. Sometimes the logs are larger than the database and sometimes the logs are small. How can you minimze the size of the restored log?
For example,
Before the backup, should I truncate the log and then RedGate backup and now the restored log will be as small as possible. After restored, now start transactional backing up of log.
Or After backup and restore, truncate the log and then start hourly backing up of the log.
Thanks
Colby
Comments
I am little confused by your post but I believe I understand what you are seeking to achieve.
Performing a Transaction Log backup will truncate the transaction log providing you have not selected the NO_TRUNCATE option. This action alone does not reduce the phyiscal size of the Transaction Log file.
After truncating the transaction log, you need to perform a Shrink Database or Shrink File command to reduce the phyiscal size of transaction log, using DBCC SHRINK DATABASE or SHRINK FILE commands. The following SQL Server Books On-Line contain further advice on the DBCC SHRINK commands:
SHRINK DATABASE
SHRINK FILE
If you wish to use SQL Backup to backup the database and restore to a different server, follow these steps.
1. Use SQL Backup to backup the Transaction Log of the database.
2. Use DBCC SHRINK FILE to reduce the phyiscal size of the Transation Log.
3. Perform a Full Backup of the database using SQL Backup.
4. Perform a Log backup of the database using the NO_TRUNCATE option.
On the new Server
5. Restore the Full backup taken in step 3, using the WITH NORECOVERY argument.
6. Restore the log backup taken in step 4, using the WITH RECOVERY argument.
Otherwise perform steps 1 and 2 in the above procedure and use the detach/attach method to move database or use the the copy database wizard via SSMS.
Hope this answers your question.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com