Click here to monitor SSC
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
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 2450
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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
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: 1087 Visits: 3661
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
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
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
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1381
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
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 823
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