SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup scripts vs. Maint Plans


Backup scripts vs. Maint Plans

Author
Message
SQL Guy 1
SQL Guy  1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 2601
Hi all,

What are advantages and disadvantages of Maintenence Plans for backups vs backup scripts ?
Our version is 2008.

Thanks
SQL_Bob
SQL_Bob
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 408
To me, the advantage of Maintenence Plans is that they're easy - especially for people who need to get maintenance tasks done but don't know how to write T-SQL (think accidental DBAs, etc.). They're a good tool and I'm glad MS provides them.

That being said, I prefer scripting out my backups in plain old T-SQL. Long ago when MPs first came out I remember them being very limited, and you couldn't do things like split a backup across multiple files. These features were added over time, but I still feel that I get more flexibility out of just scripting things out. It's also a great way to learn and understand the backup syntax!

Another great option is Ola Hallengren's SQL Server Maintenance Solution, which is totally free and will create stored procedures that perform backups, integrity checks and index maintenance. They do a great job and are very easy to configure through parameters. If you're not really wanting to write your own T-SQL but aren't thrilled with MPs, this is an excellent way to go.

Hope this helps!

Bob

_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008

Blog: The Outer Join
Twitter: @SQLBob
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2755 Visits: 3671
I have several databases that contain scratch data that either don't ever need backing up or only need the occasional full backup, without the usual differential and log backups. With a custom script, I can ignore these databases by name or by adding them to a table of DB names to skip.

YMMV,

Rich
SQL Show
SQL Show
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1078
I would recommend scripts rather than MP. I had some maintenance plans in Dev env - virtual machines which threw some memory related errors(those were simple packages for sql backup). I cant remember those errors now and just a rerun solved them.

Scripts will give us more flexibility than maintenance plan. Also when you upgrade/migrate your database, scripts will not give you that much problem as MP do.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57693 Visits: 9730
Maintenance plans are great for those who can't customize something better.

I prefer scripts, because they can be customized to your specific DR needs. For example, I've set up scripts that would check the current LSNs vs the last ones backed up, and customize which databases to back up based on that. Easy enough to do, and quite useful on databases that get only very infrequent data changes. Also easy enough to check available disk space vs database size before attempting a backup, and thus avoid crashing an overloaded disk. That kind of thing.

Since scripts can run CLR (in SQL 2005 and later), which has very robust disk interaction and system interaction capabilities, there are very few limits on what scripts can do.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24594 Visits: 13698
there's no shame in using maintenance plans for backups if they suit your needs.

A weakness with them is they do not failover at all well in HA\DR solutions that involve a server name change. In that case scripts are better unless you are prepared to maintain two versions of the maintenance plan, one on each server in the DR pair.

---------------------------------------------------------------------
prettsons
prettsons
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1865 Visits: 1410
Check this video where Brent explains advantages and disadvantages of Maintenence Plans for backups vs backup scripts: http://sqlserverpedia.com/blog/sql-server-management/maintenance-plans-vs-t-sql-scripts/

SQL Database Recovery Expert :-)
Judy Why
Judy Why
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 828
GSquared (9/26/2012)
For example, I've set up scripts that would check the current LSNs vs the last ones backed up, and customize which databases to back up based on that.


Hi,

Could you shed a light on how to compare current LSN with the LSN of last backup?

I have one instance hosting around 150 databases. Daily all the backups need to be copied to DR site and restored.

Most of database are not very active. I am thinking if the database is not changed, then I only need do a local backup and don't need to copy backup to DR site.

I tried to compare Current LSN with the LSN of last backup, but no luck.

------------------------
For example, for Database TestLSN

to get current LSN
select * from fn_dblog(null,null)

the result:
000000e2:000000f1:0022 LOP_BEGIN_CKPT
000000e2:000000ff:0001 LOP_END_CKPT

To get Backup LSN
select backup_start_date,first_lsn,last_lsn
from msdb..backupset where database_name='testlsn'
and backup_start_date>'2012/09/28'
order by backup_set_ID

The result:
2012-09-28 09:43:45.000 226000000019400040 226000000021100001
2012-09-28 14:15:55.000 226000000021900037 226000000023500001


select
convert(bigint, 0x000000e2) * 1000000000000000 +
convert(bigint, 0x000000f1) * 100000 +
convert(int, 0x0022)

The result: 226000000024100034


select
convert(bigint, 0x000000e2) * 1000000000000000 +
convert(bigint, 0x000000ff) * 100000 +
convert(int, 0x0001)


The result: 226000000025500001

They are not equal.

----------------------

What did I miss?

Thank you very much.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search