SQL Server Forums - backup before or after Index defrag

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

backup before or after Index defrag

New Topic

Reply to Topic

Printer Friendly

Author

Topic

heliang
Starting Member

18 Posts

Posted - 06/07/2012 : 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

Edited by - heliang on 06/07/2012 16:53:57

tkizer
Almighty SQL Goddess

USA
35007 Posts

Posted - 06/07/2012 : 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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 06/07/2012 : 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

18 Posts

Posted - 06/08/2012 : 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!

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.05 seconds.