backup before or after Index defrag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

backup before or after Index defrag

Author

Topic

heliang
Starting Member

38 Posts

Posted - 2012-06-07 : 16:42:48

I have a nightly maintenance plan to defrag indexes and also full DB backup. I am wondering should I do the backup before defrag or after or maybe I should do one before then one after. Please advice.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-07 : 17:11:57

I backup before because we have a limited window where we can do maintenance activities, and the backup fits into the schedule before alter index.

But if you backup after you may get some space savings.

Either way is fine though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2012-06-07 : 23:54:10

Did you analysed whether you really need to do a defrag each night.cause a defrag drops and recreates the index which bloats the log size which in turn increases the backup size.

After Monday and Tuesday even the calendar says W T F ....

heliang
Starting Member

38 Posts

Posted - 2012-06-08 : 10:39:57

Yes, this database get insert update a lot veryday. I am setting only defrag 50%. so it does not increate log too much.

Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources