September 24, 2012 at 8:39 pm
Hi all,
What are advantages and disadvantages of Maintenence Plans for backups vs backup scripts ?
Our version is 2008.
Thanks
September 25, 2012 at 6:13 am
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[/url], 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[/url]
Twitter: @SQLBob
September 26, 2012 at 6:40 am
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
September 26, 2012 at 7:56 am
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.
September 26, 2012 at 8:02 am
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
September 26, 2012 at 10:19 am
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.
---------------------------------------------------------------------
September 28, 2012 at 3:21 am
This was removed by the editor as SPAM
September 28, 2012 at 12:43 pm
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:0022LOP_BEGIN_CKPT
000000e2:000000ff:0001LOP_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.000226000000019400040226000000021100001
2012-09-28 14:15:55.000226000000021900037226000000023500001
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply