Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this procedure below to backup database(procedure is in backuped database). Procedure is called by external program. Procedure runs fine, backups are created. But when I want to find out last backup date by this script:

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 104),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name =
GROUP BY sdb.Name

it returns date of last manual backup.

There are no rows in msdb.dbo.backupset table. Restore of backup works fine.

Where is the problem please?


CREATE PROCEDURE [dbo].[dbBackup]

    EXEC sp_configure 'show advanced options', 1
    EXEC sp_configure 'xp_cmdshell', 1

    If OBJECT_ID('tempdb..#DBName','u') IS NULL
        Create Table #DBName
            ID              int identity (1,1) ,
            Name            varchar(128) not null ,
            RetentionPeriod int null,
            BackupPath      varchar(255) default(''),
            DBSize          float default(0)

    If OBJECT_ID('tempdb..#ExistingBackups', 'u') IS NULL
        Create Table #ExistingBackups
            Name varchar(128) ,
            ID int identity (1,1)

    Declare @Path varchar(255)
    Declare @sql varchar(1000)
    Declare @Name varchar(128)
    Declare @RetentionPeriod int
    Declare @LastBackupToKeep varchar(8)
    Declare @ID int
    Declare @MaxID int
    Declare @eName varchar(255)
    Declare @eMaxID int
    Declare @eID int
    Declare @eTimeStamp varchar(20)
    Declare @errMsg nvarchar(2048)
    Declare @errCount int; set @errCount = 0;
    Declare @freeSpace bigint
    Declare @pageSize float
    Declare @dbSize bigint
    Declare @procDate datetime
    Declare @Sklad char(3)

    Select @pageSize = v.low / 1024 From master..spt_values v (noLock) Where v.number = 1 And v.[type] = 'E'
    Select Top 1 @sklad = sklad_id From dbo.pohyb (noLock) Where Convert(int, sklad_id) > 500
    Set @procDate = GETDATE()

    Truncate Table #DBName

    Insert Into #DBName (Name, RetentionPeriod, BackupPath)
    Select  DBName, BackupsToStore, BackupPath
    From    dbo.databaseBackup (noLock)
    Where   runBackup = 1

    Select @MaxID = max(ID), @ID = 0 From #DBName

    While @ID < @MaxID
        Select @ID = min(ID) From #DBName Where ID > @ID

        Select @Name = Name, @RetentionPeriod = RetentionPeriod, @Path = BackupPath
        From #DBName
        Where ID = @ID

        If SUBSTRING(@Path, Len(@Path), 1) <> '\' Set @Path = @Path + '\'

        Set @sql = 'Update #DBName Set DBSize= (Select Round(Sum(size) *' + CONVERT(varchar, @pageSize) + '/1024, 0) From ' + @Name + '.dbo.sysfiles (noLock)) Where Name = ''' + @Name + ''''

        Exec (@sql)

        Select @dbSize = DBSize From #DBName

        Exec @freeSpace = dbo.getDiskFreeSpace @drive = @Path

        If @freeSpace > @dbSize
                Set @eTimeStamp = REPLACE(REPLACE(CONVERT(varchar, @procDate, 113), ' ', '_'), ':', '-')
                Set @sql = @Path + @Name + '_' + @eTimeStamp + '.bak'
                Set @errMsg = 'OK'
                Begin Try

                    Backup Database @Name To Disk = @sql

                    Truncate Table #ExistingBackups
                    Set @sql = 'dir /B /OD ' + @Path + @Name + '_*.bak'

                    Insert #ExistingBackups Exec master..xp_cmdshell @sql

                    If Exists (Select 1 From #ExistingBackups Where PATINDEX('%File Not Found%', Name) > 0)
                        Truncate Table #ExistingBackups

                    Delete From #ExistingBackups Where Name IS NULL

                    Select @eID = 0

                    Select @eMaxID = Max(ID) - @RetentionPeriod From #ExistingBackups

                    While @eID < @eMaxID
                        Select @eID = Min(ID) From #ExistingBackups Where ID > @eID
                        Select @eName = Name From #ExistingBackups Where ID = @eID

                        Set @sql = 'del ' + @Path + @eName
                        Exec master..xp_cmdshell @sql

                    Truncate Table #ExistingBackups

                End Try
                Begin Catch
                    Set @errMsg = @errMsg + '||' + ERROR_MESSAGE()
                    Set @errCount = @errCount + 1;
                End Catch
            Set @errMsg = 'Error'

        Insert Into [dbo].[databaseBackup_log] ([Sklad_id], [DBName], [BackupDate], [Status]) Values (@Sklad, @Name, @procDate, Ltrim(Rtrim(@errMsg)))

    Drop Table #DBName
    Drop Table #ExistingBackups

    IF @errCount > 0 BEGIN
        RAISERROR (@errMsg, 16, 2) WITH SETERROR

    RETURN @errMsg;

share|improve this question
If you look in the msdb.dbo.backupset table, do you see the backups listed that were created by your procedure? Or are they missing from that table, implying that perhaps your backups are not completing successfully? – SQLRockstar Dec 18 '13 at 14:53
"Procedure runs fine, backups are created." -> are you certain the backup is created properly? Have you tried restoring one of the backups? – Max Vernon Dec 18 '13 at 14:59
no record in msdb.dbo.backupset, but restore works fine – Cicik Dec 18 '13 at 17:41
real problem was outside of SP. Soved by… – Cicik Jan 9 '14 at 11:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.