| Author |
Topic |
|
jocampo
Starting Member
48 Posts |
 Posted - 2006-07-13 : 09:50:33
|
| Hi forum friends.I'm having a weird error after being backed up 2 big databases (using maintenance plan) The email i recieved says that the verify phase found errors checking the back up. I checked backup and it ran ok. I also made a restore and a checkdb .. everything ok.So, here's my question. Its really the "verify the integrity of teh backup upon completion" option a good one? It is adding overhead to our servers and if it is not 100% realiable i prefer to uncheck and let the backup run alone, with no option.Any ideas? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-13 : 13:10:04
|
| I don't use the verify option as it doesn't prove that the backup is good. The only way to know if a backup is good is to do a restore. So every good backup strategy must have restoring of the backups included. I also don't use the maintenance plans except for log shipping. Check out my stored procedures for alternatives to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara Kizeraka tduggan |
 |
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-07-13 : 13:26:46
|
Thanks for reply.And yes, we do restores from time to time to check backup's health. But we have A LOT of databases here ... and i mean, A LOT ... some of them of 50 or 60 GB ... so, this process is made daily but in a round robin way. We're currently using Maintenance Plans because we have a share drive with folders within for each database. Not sure... but i think your store procedures make a full backup of all databases and just drop 'em in a single folder/location; i need a specific folder for each one. Besides, we drop backups using network... we do not backup locally (obviously) Anyway, i don't know too much TSQL and you got a point ... doin' things manually is much better.I will check your link and change that option from my "Plans" ...Best regards,JCquote:
Originally posted by tkizer
I don't use the verify option as it doesn't prove that the backup is good. The only way to know if a backup is good is to do a restore. So every good backup strategy must have restoring of the backups included. I also don't use the maintenance plans except for log shipping. Check out my stored procedures for alternatives to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara Kizeraka tduggan
|
|
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-07-13 : 15:57:58
|
Tara:The "Backup databases stored procedure" you wrote, does not work if i do not specify MASTER as database in the job itself. But your advice is to create an database named "admins" and run sp from this one; i tried ... it does not work. I assume it is because it can not "read" databases ids from "admin" database ... Any comments?quote:
Originally posted by tkizer
I don't use the verify option as it doesn't prove that the backup is good. The only way to know if a backup is good is to do a restore. So every good backup strategy must have restoring of the backups included. I also don't use the maintenance plans except for log shipping. Check out my stored procedures for alternatives to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara Kizeraka tduggan
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-13 : 16:05:44
|
| It works in any database. What error are you getting?Tara Kizer |
|
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-07-13 : 16:54:15
|
Thanks for reply ...well... the SQL job simply does not run under "admin" database ... i was trying to backup Norhwind. But ... if i put the store procedure under Northwind instead, not under "admin, it works ... any ideas?quote:
Originally posted by tkizer
It works in any database. What error are you getting?Tara Kizer
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-13 : 17:00:53
|
| I don't understand what the problem is.Put the stored procedure in the Admin database. Then inside Query Analyzer, run the stored procedure. Post any errors here.The version that I posted of isp_Backup does not support backing up a single database. Please see the comment header block in the stored procedure for what it supports, specifically @dbType variable.I do have a newer version that supports backing up a single database, but I haven't posted it yet. It supports both 2000 and 2005 as well.Tara Kizer |
|
|
|
jocampo
Starting Member
48 Posts |
Posted - 2006-07-18 : 11:43:11
|
A new version? well ... that will be cool ...Tara? i have not recieved the error again ... this is weird ... anyway, i just want to let you know ...quote:
Originally posted by tkizer
I don't understand what the problem is.Put the stored procedure in the Admin database. Then inside Query Analyzer, run the stored procedure. Post any errors here.The version that I posted of isp_Backup does not support backing up a single database. Please see the comment header block in the stored procedure for what it supports, specifically @dbType variable.I do have a newer version that supports backing up a single database, but I haven't posted it yet. It supports both 2000 and 2005 as well.Tara Kizer
|
|
|
|
|
Verifiy Backups