Take the 2-minute tour ×
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.

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 = sdb.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?

Thanks

CREATE PROCEDURE [dbo].[dbBackup]
AS
BEGIN
    SET NOCOUNT ON;

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

    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
    Begin
        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
            Begin
                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
                    Begin
                        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
                    End

                    Truncate Table #ExistingBackups


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

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

    Drop Table #DBName
    Drop Table #ExistingBackups

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

    RETURN @errMsg;

END
share|improve this question
1  
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
1  
"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 stackoverflow.com/questions/20990318/… –  Cicik Jan 9 at 11:21
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.