SQL Command to Backup Databases

Create a SQL stored procedure called usp_backup_database

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[usp_backup_database]    Script Date: 4/1/2013 12:59:00 PM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[usp_backup_database](

                @iv_backup_folder VARCHAR(50),

                @iv_db_name VARCHAR(50),

                @iv_tag VARCHAR(50) = NULL

)

as

 

/*==============================================================================

*    PROCEDURE:               usp_backup_database

* 

*  DESCRIPTION: Backups a database to a .bak file given the folder and database

*               name as input parameters. Also adds a timestamp to the .bak

*               name.

*

*      OUTPUTS: None, executes 'BACKUP DATABASE' command

* 

* DEPENDANCIES:           None

*============================================================================*/

begin

   set nocount on

 

                declare @lv_backup_full_path varchar(500)

 

                set @lv_backup_full_path = @iv_backup_folder + '\' + @iv_db_name + '_' +

                REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), CURRENT_TIMESTAMP, 20), '-', ''), ' ', '_'), ':', '') 

 

                if(@iv_tag is not null)

                                set @lv_backup_full_path = @lv_backup_full_path + '_' + @iv_tag

 

                set @lv_backup_full_path = @lv_backup_full_path + '.bak'

 

                backup database @iv_db_name to disk = @lv_backup_full_path

 

                set nocount off

end

grant execute on dbo.usp_backup_database to public

Then execute the stored procedure:

exec usp_backup_database 'e:\', 'MyDatabase1', 'Prod'
exec usp_backup_database 'e:\', 'MyDatabase2', 'Prod'
exec usp_backup_database 'e:\', 'MyDatabase3', 'Prod''