Predeploy Backup Script

USE master;
SET NOCOUNT ON;
DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @cutoff DATETIME;
DECLARE @BackupCopy VARCHAR(2000);
DECLARE @PredeployDir VARCHAR(2000);
DECLARE @name VARCHAR(50); -- database name
DECLARE @fileName VARCHAR(256); -- filename for backup
DECLARE @fileDate VARCHAR(20); -- used for file name
DECLARE @DirTree TABLE
  (
  subdirectory NVARCHAR(255),
  depth        INT
  );
SET @cutoff = DATEADD(dd, -2, GETDATE());
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @PredeployDir = @DefaultBackupDirectory+'\PreDeploy';

EXECUTE master..xp_delete_file 0, @PredeployDir, N'bak', @cutoff, 1;
EXECUTE master..xp_delete_file 0, @PredeployDir, N'trn', @cutoff, 1;

INSERT INTO @DirTree
(subdirectory
   , depth
)
EXEC master.sys.xp_dirtree @DefaultBackupDirectory;
IF NOT EXISTS
  (
   SELECT 1
   FROM @DirTree
   WHERE subdirectory = 'Predeploy'
AND depth = 1
  )
    EXEC master.dbo.xp_create_subdir @PredeployDir;
SELECT @fileDate = REPLACE(CONVERT( VARCHAR(20), GETDATE(), 112)+CONVERT(VARCHAR(5), GETDATE(), 108), ':', '');
DECLARE db_cursor CURSOR
FOR SELECT name
    FROM sys.databases
    WHERE database_id <> 2
AND name IN
 ('DBName'  -- Change Database name
 )
AND is_read_only = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
    BEGIN
  SET @fileName = @PredeployDir+'\'+@name+'_'+CONVERT(VARCHAR(128), REPLACE(@@servername, '\', '_'))+'_'+@fileDate+'.bak';
  PRINT 'Backup started for database: '+@NAME+' located @ '+@filename;
  BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 20;
  PRINT 'Backup completed for database: '+@NAME
  SELECT TOP 1 @BackupCopy = physical_device_name
  FROM msdb..backupmediafamily
  ORDER BY media_set_id DESC
  SET @BackupCopy = 'copy '+'\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@BackupCopy, ':', '$')+' \\Share\DB-PreDeployBackup\';
  PRINT @BackupCopy
  FETCH NEXT FROM db_cursor INTO @name;
    END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO