SQLCMD uses the following script
USE BackupDatabase
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Date SMALLDATETIME
SET @Date = GETDATE()
SET @Hour = DATEPART (HOUR , @Date)
SET @Minute = DATEPART (MINUTE , @Date)
-- We need to either do FULL or DIFFERENTIAL Backups
IF (@Hour % 4) = 0 AND @Minute = 0
BEGIN
-- IF 12:00 AM, then we do full backup
IF @Hour = 0
BEGIN
-- FULL BACKUP
EXEC dbo.usp_BackupDatabase 'test', 1
EXEC dbo.usp_BackupDatabase 'test2', 1
EXEC dbo.usp_BackupDatabase 'master', 1
EXEC dbo.usp_BackupDatabase 'BackupDatabase', 1
END
ELSE
BEGIN
-- DIFFERENTIAL BACKUP
EXEC dbo.usp_BackupDatabase 'test', 2
EXEC dbo.usp_BackupDatabase 'test2', 2
END
END
ELSE
BEGIN
-- LOG BACKUP
EXEC dbo.usp_BackupDatabase 'test, 3
EXEC dbo.usp_BackupDatabase 'test2', 3
END
and the stored procedures
CREATE PROCEDURE [dbo].[usp_BackupDatabase]
(
@DatabaseName VARCHAR(50),
@BackupMode TINYINT -- 1 FULL, 2 Differential 3 Log
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BackupCommand AS NVARCHAR(1000)
DECLARE @FileName AS NVARCHAR(100)
IF @BackupMode = 1
BEGIN
SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Full'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH INIT, NOSKIP'
END
IF @BackupMode = 2
BEGIN
SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Differential'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
SET @BackupCommand = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH DIFFERENTIAL, INIT, NOSKIP'
END
IF @BackupMode = 3
BEGIN
SET @FileName = '''C:\SQL2005Backups\'+ @DatabaseName + '\Log'+
LEFT(REPLACE(CONVERT(VARCHAR(25), GETDATE(),108), ':', ''), 4) + '.BAK'''
SET @BackupCommand = 'BACKUP LOG ' + @DatabaseName + ' TO DISK = ' +
@FileName + ' WITH INIT, NOSKIP'
END
--PRINT @BackupCommand
EXECUTE sp_executesql @BackupCommand
END