create Procedure [dbo].[Backup_BNWINS]
AS

DECLARE @name VARCHAR(50) -- Datenbankname  
DECLARE @path VARCHAR(256) -- Backup-Pfad  
DECLARE @fileName VARCHAR(256) -- Backupname  
DECLARE @fileDate VARCHAR(20) -- Datum
DECLARE @DeleteDate DATETIME = DATEADD(DAY,-3,GETDATE())--Backup älter als 3 Tage löschen


 
-- Pfad zum Backup
SET @path = 'C:\Backup\'  

 
-- Datum des Backups wird angehängt
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('BNWINS')  -- Zu sichernde Datenbanken

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName   
       FETCH NEXT FROM db_cursor INTO @name   
END  

-- löscht alte Backups
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;

 
CLOSE db_cursor   
DEALLOCATE db_cursor
GO


USE Master
GO

--Falls Prozedur schon vorhanden, wird diese gelöscht
IF  EXISTS( SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[Backup_BNWINS]')
            AND TYPE in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Backup_BNWINS]
GO

CREATE PROCEDURE [Backup_BNWINS]
AS
BEGIN
    SET NOCOUNT ON;

    -- Uhrzeit der Datensicherung
    DECLARE @timeToRun NVARCHAR(50)
    SET @timeToRun = '10:00:00'

    WHILE 1 = 1
    BEGIN
        WAITFOR TIME @timeToRun
        BEGIN
            EXECUTE [BNWINS].[dbo].[Backup_BNWINS];--Datenbank mit Stored Procedure
        END
    END
END
GO

-- Wird beim Start der Instanz geladen
sp_procoption    @ProcName = 'Backup_BNWINS',
                @OptionName = 'startup',
                @OptionValue = 'on'
GO