giammin

Backup database to backup device

Jun 15th, 2015
439
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.19 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[BackupDatabase]
  2.         @dbname VARCHAR(50)
  3. AS
  4. BEGIN
  5.         SET NOCOUNT ON;
  6.         IF (ISNULL(@dbname, '') = '')
  7.         BEGIN
  8.             RAISERROR(N'Invalid parameter: @dbname cannot be NULL or empty string', 18, 0)
  9.             RETURN
  10.         END
  11.         if   NOT (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
  12.         BEGIN
  13.             RAISERROR(N'Invalid parameter: database %D not found', 18, 0, @dbname)
  14.             RETURN
  15.         END
  16.         BEGIN
  17.                 DECLARE @BackupDevice VARCHAR(256);
  18.                 Declare @backupsetName varchar(256);
  19.                 Declare @retaindays int=30;
  20.                 SET @BackupDevice = 'BACKUP DEVICE NAME';
  21.                 SET @backupsetName = lower(@dbname) + '_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '');
  22.                 Print 'Executing Backup database';
  23.                 Print '     Backup Device: ' + @BackupDevice;
  24.                 Print '     Backup set name: ' + @backupsetName;
  25.                 Print '     retain days: ' + cast( @retaindays as nvarchar(2));
  26.                 BACKUP DATABASE @dbname TO @BackupDevice with  RETAINDAYS=@retaindays, NAME=@backupsetName ;
  27.         END
  28. END
  29. GO
Advertisement
Add Comment
Please, Sign In to add comment