I’m running sql server 2005 and I’ve made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has only backed up around half of my databases. I’ve tried it on multiple servers and it does the same thing. I’ve checked the application log for the backups in the eventvwr, however, it tells me to check the application log for more details so I can never find the detailed information I need.
I saw this similar question and tried the solution that was recommended to try and catch errors. However, there were no errors thrown when I run the script.
DECLARE @db_names NVARCHAR(1000) DECLARE db_cursor CURSOR FOR select name from sys.databases where name not in ('tempdb') order by name OPEN db_cursor FETCH NEXT FROM db_cursor into @db_names WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXECUTE [Full_Backup] @DB_name = @db_names END TRY BEGIN CATCH EXEC spErrorHandling END CATCH FETCH NEXT FROM db_cursor INTO @db_names END CLOSE db_cursor DEALLOCATE db_cursor
I used this sites example for error handling.
-- Declaration statements DECLARE @Error_Number int DECLARE @Error_Message varchar(4000) DECLARE @Error_Severity int DECLARE @Error_State int DECLARE @Error_Procedure varchar(200) DECLARE @Error_Line int DECLARE @UserName varchar(200) DECLARE @HostName varchar(200) DECLARE @Time_Stamp datetime -- Initialize variables SELECT @Error_Number = isnull(error_number(),0), @Error_Message = isnull(error_message(),'NULL Message'), @Error_Severity = isnull(error_severity(),0), @Error_State = isnull(error_state(),1), @Error_Line = isnull(error_line(), 0), @Error_Procedure = isnull(error_procedure(),''), @UserName = SUSER_SNAME(), @HostName = HOST_NAME(), @Time_Stamp = GETDATE(); -- Insert into the dbo.ErrorHandling table INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line, Error_Procedure, UserName, HostName, Time_Stamp) SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line, @Error_Procedure, @UserName, @HostName, @Time_Stamp
Is there a reason why backups fail silently? Is there better error handling I can be doing?
declare @path varchar(200) set @path = 'P:Backups' + CONVERT(char(10), GetDate(),126) + '_' + @DB_name + '.bak' declare @backupStr nvarchar(max) set @backupStr = 'BACKUP DATABASE [' + @DB_name + '] TO DISK = N''' + @path+ ''' WITH NOFORMAT, NOINIT, NAME = N'''+@DB_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'''+@DB_name+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DB_name+''' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DB_name+''''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''' + @path + ''' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
Edited to add the [Full_Backup] sp
The problem may actually be that in using your cursor, you’re not encountering errors, but databases are simply getting skipped. I have had situations in the past where a default cursor will skip records. I’ve never been able to determine the cause, but I have found some ways to handle it better in the code.
One is to put some better definition around your cursor declaration. Since this kind of cursor will always iterate forward, you should declare it as such:
DECLARE db_cursor CURSOR STATIC FAST_FORWARD FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name != 'tempdb' ORDER name
I have found using the
FAST_FORWARD option is much more reliable for iterating each item in a data set. Also, it can have other benefits.
Another approach is to build a
WHILE... loop structure to handle this. This does not rely on the cursor engine in SQL Server to handle your logic and can give you better reliability for getting all your databases(this is incomplete code, you will need to add backup/error handling logic to make it work):
DECLARE @work table (dbid int, backupdate datetime, errormsg varchar(max)); DECLARE @currdb int INSERT INTO @work (dbid) SELECT database_id FROM sys.databases a WHERE state_desc = 'ONLINE' AND name != 'tempdb' WHILE EXISTS (SELECT 1 FROM @work WHERE backupdate IS NULL AND errormsg IS NULL) BEGIN SELECT top 1 @currdb = dbid FROM @work WHERE backupdate IS NULL AND errormsg IS NULL; BEGIN TRY --Execute Backup Code, then update @work table UPDATE @work SET backupdate = GETDATE() WHERE dbid = @currdb END TRY BEGIN CATCH --Catch error, put message in @work table update @work set errormsg=ERROR_MESSAGE() where dbid = @currdb; END CATCH END
These are two approaches if you decide to move forward with writing your own scripts for backups. Overall, I would strongly recommend you check out Ola Hallengren’s backup scripts. They are highly recommended by members of the community, well documented, and thoroughly tested.
I have an alternative suggestion, applicable if you are running an edition of SQL Server rather than Express.
Use a Maintenance Plan to do the backups on a schedule. SQL Agent will log the scheduled job’ s execution results.
i wouldn’t use maintenance plans – i don’t trust them. 😉
However, it looks like you could possibly be getting either an empty string or just a partial result with:
select @backupSetId = position from msdb..backupset where database_name=N”’+@DB_name+”’ and backup_set_id
But, yeah – I’d use a print statement and see what is actually being generated.