Comments
1 comment
-
Thank you for your post into the forum.
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
Add comment
Please sign in to leave a comment.
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