Database Backup via TSQL
-
I have a stored procedure that I'll be executing to backup a database. How can I be sure that the backup was successffull?
Thanks
-
This really depends on what your definition of "successful" is.
If it is "Stored Proc Worked." then you already get that by the error handling. You'll get an error if it doesn't at runtime.
If it is "created the backup file" then you can simply run a fileexists check for it afterwards.
If it is "created a verified good backup" (which is what I would do) then run another proc to restore it to a test server, and select a row of data from the restored db, checking for success.
What exactly do you need?
-
I want to ensure the backup database command completed successfully. Will that backup database command set @@ERROR if it fails?
Here is what i've come up with.... would you agree with this or suggest something else?
I won't be confirming the the backup by doing a restore... that will be happening already on a daily basis. We just want the database to be automatically backed up when a user runs a certain program.
-- Backup the database
BACKUP DATABASE APWUPrototype
TO DISK = @Backup
WITH
INIT,
NOUNLOAD,
NAME = @BackupName,
NOSKIP,
STATS = 10,
NOFORMAT
SET @Err = @@ERROR
IF @Err <> 0
BEGIN
PRINT 'The database backup failed.'
RAISERROR('The database backup failed.', 16, -1)
RETURN @Err
END
--Verify the backup set exists and is valid
RESTORE VERIFYONLY
FROM DISK = @Backup
SET @Err = @@ERROR
IF @Err <> 0
BEGIN
PRINT 'The database backup could not be verified.'
RAISERROR('The database backup could not be verified.', 16, -1)
RETURN @Err
END
-- One more check to ensure that the backup set was made today - not sure if this is necessary
IF NOT EXISTS
(
SELECT
name, backup_start_date, backup_finish_date, *
FROM
msdb.dbo.backupset
WHERE
name = @BackupName
AND backup_finish_date >= @BackupDateTime
 
BEGIN
PRINT 'The time_stamp of the database backup could not be verified.'
RAISERROR('The time_stamp of the database backup could not be verified.', 16, -1)
RETURN @Err
END
RETURN
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply