Blog ala Vidar

I’ve had a couple of issues regarding the maintenance plan-tool in management studio (at least before 2008), and therefore I’ve never really put my trust in this tool. Instead of this, I’ve created a SP which now is available as an s-object in AppFrame. Will be available with the next build, but if you need it NOW, here it is:

This will take backups, (full, diff or transactional) using default settings and saving files with the following syntax:
DatabaseName_backup_201006231500.bak

CREATE PROCEDURE sstp_Database_Maintenance_Backup
(
	@BackupType AS CHAR(4) = 'FULL',
	@BackupPath AS NVARCHAR(300)
)
AS

IF @BackupType NOT IN ('FULL', 'DIFF', 'TRAN')
BEGIN
	RAISERROR('BackupType must be set to FULL, DIFF or TRAN!', 18, 1)
	RETURN
END
ELSE IF ISNULL(@BackupPath, '') = ''
BEGIN
	RAISERROR('BackupPath does not have any value!', 18, 1)
	RETURN
END
ELSE IF RIGHT(@BackupPath, 1)  '\'
BEGIN
	SET @BackupPath = @BackupPath + '\'
END


DECLARE
  @DBNAME AS NVARCHAR(128),
  @Filename AS NVARCHAR(128),
  @SQL AS NVARCHAR(MAX)


SET @DBNAME = (SELECT DB_NAME())
SET @Filename =
	@DBNAME +
	'_backup_' +
	CONVERT(VARCHAR(8), GETDATE(), 112) +
	REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108), ':', '')

IF @BackupType = 'FULL'
BEGIN
	SET @Filename = @Filename + '.bak'
	SET @SQL = 'BACKUP DATABASE [' + @DBNAME + '] '
END
ELSE IF @BackupType = 'DIFF'
BEGIN
	SET @Filename = @Filename + '.diff'
	SET @SQL = 'BACKUP DATABASE [' + @DBNAME + '] '
END
ELSE IF @BackupType = 'TRAN'
BEGIN
	SET @Filename = @Filename + '.trn'
	SET @SQL = 'BACKUP LOG [' + @DBNAME + '] '
END

SET @SQL = @SQL + 'TO DISK = N''' + @BackupPath + '' + @Filename + ''' WITH '

IF @BackupType = 'DIFF'
BEGIN
	SET @SQL = @SQL + 'DIFFERENTIAL, '
END

SET @SQL = @SQL + 'NOFORMAT, INIT, NAME = N''' + @DBNAME + ' ' + @BackupType
SET @SQL = @SQL + ' Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

EXEC(@SQL)

You can download the .sql file from skydrive.