Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup scripts vs. Maint Plans Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 8:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:21 PM
Points: 439, Visits: 1,388
Hi all,

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

Thanks
Post #1363757
Posted Tuesday, September 25, 2012 6:13 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:00 AM
Points: 41, Visits: 394
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
Post #1363967
Posted Wednesday, September 26, 2012 6:40 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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
Post #1364622
Posted Wednesday, September 26, 2012 7:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1364696
Posted Wednesday, September 26, 2012 8:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1364706
Posted Wednesday, September 26, 2012 10:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


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

Post #1364818
Posted Friday, September 28, 2012 3:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 112, Visits: 1,210
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
Post #1365706
Posted Friday, September 28, 2012 12:43 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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.
Post #1366066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse