MS-SQL - Create a Backup and Restore the database using Stored Procedure

T-Sql Script

   1:  USE MASTER; 

   2:  CREATE PROCEDURE Dbo.Sp_BackUpRestore

   3:  (

   4:   

   5:  @Process VARCHAR(10), -- specify the process name (example - restore or backup)

   6:   

   7:  @DatabaseName VARCHAR(50), -- specify database name

   8:   

   9:  @Path VARCHAR(MAX) -- specify full path

  10:   

  11:  )

  12:   

  13:  AS 

  14:   

  15:   

  16:  BEGIN

  17:  DECLARE @DQuery varchar(MAX)

  18:   

  19:  DECLARE @ErrorMessage NVARCHAR(MAX)

  20:   

  21:  IF @Process<>'' AND @DatabaseName<>'' AND @Path<>''

  22:   

  23:  BEGIN

  24:   

  25:  IF @Process=LOWER('backup')

  26:   

  27:  BEGIN

  28:   

  29:  BEGIN TRY

  30:   

  31:  IF EXISTS(SELECT [Name] FROM sys.databases WHERE [Name]=@DatabaseName )

  32:   

  33:  BEGIN

  34:   

  35:  SET @DQuery='BACKUP DATABASE '+@DatabaseName+' TO DISK =N'''+@Path+'\'+@DatabaseName+'.bak''' PRINT(@DQuery)

  36:   

  37:  EXECUTE(@DQuery)

  38:   

  39:  END

  40:   

  41:  ELSE

  42:   

  43:  BEGIN

  44:   

  45:  PRINT('Database Dose not Exists in Sql Server')

  46:   

  47:  END 

  48:   

  49:  END TRY

  50:   

  51:  BEGIN CATCH

  52:   

  53:  SET @ErrorMessage=ERROR_MESSAGE()

  54:   

  55:  RAISERROR(@ErrorMessage,16,1)

  56:   

  57:  END CATCH 

  58:   

  59:  END

  60:   

  61:  IF @Process=LOWER('restore')

  62:   

  63:  BEGIN

  64:   

  65:  BEGIN TRY

  66:  IF NOT EXISTS(SELECT [Name] FROM Sys.Databases WHERE [Name]=@DatabaseName)

  67:   

  68:  BEGIN

  69:   

  70:  SET @DQuery='RESTORE DATABASE '+@DatabaseName+' FROM DISK =N'''+@Path+'\'+@DatabaseName+'.bak'''

  71:   

  72:  PRINT(@DQuery)

  73:   

  74:  EXECUTE(@DQuery)

  75:   

  76:  END

  77:   

  78:  ELSE

  79:   

  80:  BEGIN

  81:   

  82:  PRINT('Database Exists in Sql Server')

  83:   

  84:  PRINT('It can not Override')

  85:   

  86:  END 

  87:   

  88:  END TRY

  89:   

  90:  BEGIN CATCH

  91:   

  92:  SET @ErrorMessage=ERROR_MESSAGE()

  93:   

  94:  RAISERROR(@ErrorMessage,16,1)

  95:   

  96:  END CATCH

  97:   

  98:  END

  99:   

 100:  END 

 101:   

 102:  ELSE

 103:   

 104:  BEGIN

 105:   

 106:  PRINT('Please Enter The Parameter')

 107:   

 108:  END

 109:   

 110:  END

 111:   

 112:  GO 

Calling Stored Procedure

   1:  -- For Backup the Database

   2:  EXEC Sp_BackUpRestore 'backup','HSM','C:\Database'

   3:   

   4:  -- For Restore the Database

   5:  EXEC Sp_BackUpRestore 'restore','HSM','C:\Database' 

Note

Execute above Stored Procedure in Master Database

Download
Download Script