September 24, 2008 at 3:19 pm
Hi Everyone,
I hope you guys can give me a better idea. I am always comfused with those steps in Database Backup. Which order I should follow before I run the full backup or after backup.
Database Integrity Check
Index rebuild/defreg
Truncate logs
Full Backup
What is the right order?
Thanks.
Leo
September 24, 2008 at 3:31 pm
The right order really depends upon your business requirements, the disaster recovery requirements and system restore requirements. My preferences is to perform the backups last, that way I don't have to rebuild indexes or update statistics after a restore (shortens the restore window by at least 3 hours on some of my larger systems).
However - one step you have in their should NEVER be performed. Do not truncate the log files. If your business requirement is to be able to recover to a point in time - then you need to schedule regular transaction log backups (I back up the transaction logs on our critical systems every 15 minutes).
If your business can afford to lose up to 24 hours of data - you can always put the database in simple recovery model and forget about backing up the transaction log. However, for production systems that is not recommended.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2008 at 3:48 pm
Hello,
This is for Production System. Shall I rebuild the index every once a week and run the full backup every day? I set the datatabase at Full Recovery Mode so that when I run the full backup at night, that include the transaction log as well?
Why I shouldn't truncate the log files? example..backup log with truncate_only
What is the main purpose of backup the transaction log only? If I set the backup for every 2 hours just for transaction log, can I get the data back when I restore the transaction log if someone delete the table or records before I run the backup?
Sorry to ask again, I want to be clear this.
Thanks.
Leo
September 24, 2008 at 4:01 pm
You should rebuild your indexes as often as needed. I don't know your system or how often your indexes are becoming fragmented - so, I really couldn't say whether once a week or daily would be better. I utilize a 'smart' indexing scheme that checks index fragmentation and only rebuilds indexes that need to be rebuilt. Lookup the topic in Books Online on the DMV sys.dm_db_index_physical_stats. At the bottom there is an example of a script to perform a 'smart' reindex.
Issuing the command BACKUP LOG db WITH TRUNCATE_ONLY wipes out all of the transactions that have been committed from the transaction log. Since you did not back them up, you cannot restore them - they are gone. Once you have done that, SQL Server will not allow a backup of the transaction log until you have performed another full backup.
You backup the transaction log so you can restore them later if needed. The process is:
1) Perform a full backup (1:00am for example)
2) Backup transaction log every hour
Now, if you have an issue and your system crashes at 5:15am - how would you recover the system to as close as possible to the crash? If you have the transaction log backups, it is simple - restore the full backup from 1:00am (with norecovery), then start restoring the transaction log backups in order (again, with norecovery) until the last one has been restored. Once done, issue a restore with recovery and your database has been restored to 5am since that was the last transaction log backup you had.
There is also the possibility of performing what is called a tail-log backup prior to restoring. If you can do that, you can restore your system back to the point of failure with no data loss.
Read up on transaction logs in Books Online for further information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2008 at 4:13 pm
Hi Jeff,
What a good one, thanks for explaining this. If you don't mind, where can I find the 'Smart' Index?
'Lookup the topic in Books Online on the DMV sys.dm_db_index_physical_stats. At the bottom there is an example of a script to perform a 'smart' reindex.'
Can you provide the link for this?
Leo
September 24, 2008 at 5:17 pm
Unfortunately, I only have SQL Server 2008 installed on this workstation - so the link I have would not work for you.
If you open Books Online (or, from SSMS go to Help | Index) you can find the help by typing in: sys.dm_db_index_physical_stats
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2008 at 2:29 pm
Thank You.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply