July 7, 2010 at 9:00 pm
Hi All,
Curretly we are running a job which take database backup. If backup failed then it will re-run it again after ten minutes. Now the problem is that If it will start to take backup from first database in the list.Which seems like two backup files for the same database untill it reach to the database which failed at first attempt.(Say for low disk space or any other reason like network failure)
Is there anyway(Script) we can stop taking backup of database which was backed up in last half an hour and start to take backup from where it failed last time.
Your help will be greatly appreciated.
Thanks In advance.
July 7, 2010 at 9:17 pm
Yes there would be a way to do it. Some of that answer will depend on your precise method for performing the backup.
An example of something that would work is to use a table that controls the schedule and records last successful backup date of the database. If your job fails at the fourth database, then the first three databases would have a date stamp or even a bit field set that indicates that the database was backed up successfully. If the job has to restart in ten minutes then it will skip any databases meeting certain criteria.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2010 at 9:59 pm
Thanks For quick reply Jason.
We are running this job through third party scheduler tool. Which run a normal sql script with backup database commannd.
July 7, 2010 at 10:06 pm
You could use the backup history tables for reference. The example below will list down all database which have not had a full backup taken in the last 24 hours:
SELECT a.database_name
FROM msdb..backupset a
INNER JOIN sys.databases b ON a.database_name = b.name
WHERE a.type = 'D'
AND b.state_desc = 'ONLINE'
GROUP BY database_name
HAVING MAX(backup_start_date) < GETDATE() - 1
ORDER BY database_name
(edited to check that the list contains only online databases)
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 8, 2010 at 2:09 am
Thanks Ray.
This will help me a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply