May 11, 2010 at 5:54 pm
I am using the below sccript for full backup of all database in a server using LiteSpeed but most of the time it will not backup all databases in a server and the job ends successfully with out any error mesg any where.What ever may be the reasons but i want to make sure all db's , here is my thought..
Before it ends the job i want to check if all databases are backed up, if there are any missing i would like to re run the same script for those databases, how would i add that step to my current script ?
Here is the script i am using...
declare @db as varchar(50)
declare @bakfile as varchar(50)
declare Bkp_cursor CURSOR for select name from master.sys.databases
where state_desc = 'ONLINE' and is_read_only = 0 and name not like 'test_%'
and name>'Dev'and name <> 'tempdb' order by name
open Bkp_cursor
Fetch next from Bkp_cursor into @db
WHILE @@FETCH_STATUS = 0
BEGIN
select @bakfile='\\OBBEAVER\' + @db + '.bak'
BEGIN TRY
exec master.dbo.xp_backup_database
@database = @db,
@filename = @bakfile,
@compressionlevel = 2,
@init = 1,
@logging = 2,
@maxtransfersize= 512000,
@threads = 3
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
FETCH NEXT FROM Bkp_cursor INTO @db
END
CLOSE Bkp_cursor
deallocate Bkp_cursor
GO
May 12, 2010 at 2:57 am
running the query
SELECT database_name, MAX(backup_finish_date) backup_date
FROM msdb.dbo.backupset
GROUP BY database_name
you can get the most recent backup date of the databases, you can rerun the script for those with backup date older than the time you want (older than one day, if you schedule your script once a day for example).
May 12, 2010 at 3:10 am
I've just found this script in the 'Scripts' section, it can be very useful!
SELECTt.name as [DB Name],
t.user_access_desc as [Access State],
t.state_desc as [Online/Offline],
((SELECT (CASE t.is_in_standby WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Other' END))) as [In Standby],
(COALESCE(Convert(datetime, MAX(u.backup_finish_date), 101),'Not Yet Taken')) as [Last BackUp Taken],
(((COALESCE(Convert(real(256), MAX(u.backup_size), 101),'NA'))/1024)/1024) as [Backup Size in MB],
(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup],
(COALESCE(Convert(varchar(12), MAX(u.user_name), 101),'NA')) as [User Name]
FROM SYS.DATABASES t
INNER JOIN msdb.dbo.BACKUPSET u
ON t.name = u.database_name
GROUP BY t.Name,t.is_in_standby, t.user_access_desc, t.state_desc
ORDER BY t.Name
nice day,
sb
May 12, 2010 at 7:08 am
SELECT database_name, MAX(backup_finish_date) backup_date
FROM msdb.dbo.backupset
GROUP BY database_name
from this script i still some databases which are old and not existing on the server ?
may be it has to be like this..
SELECT database_name, MAX(backup_finish_date) backup_date
FROM msdb.dbo.backupset b inner join master.sys.databases m
on b.database_name=m.name
GROUP BY database_name
May 12, 2010 at 10:36 am
Say if there are 10 databases that was not backed up, how would i modofy my script so that it backs up only those databases in the re run.
May 13, 2010 at 1:44 am
After the first backup script, you can define a cursor on msdb.dbo.backupset
and rerun your script for dbs having last backup date
older than you need.
If you see that at the second try backups succeed, this is enough, else you could schedule a job with the cursor on msdb.dbo.backupset that reruns the backup at different times, but in this case maybe it would be convenient to understand the reason for the failures.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply