ict.ken.be

--create in master
CREATE PROCEDURE [dbo].[backup_db]
@db            varchar(50),
@backup_path   varchar(254) = 'C:\Databases.Backups\'
AS
DECLARE @backup_device nvarchar(1024);
DECLARE @backup_name nvarchar(127);
DECLARE @device_type nvarchar(50);
SET @backup_device = @backup_path + @db + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';
SET @backup_name = @db +'-Full Database Backup';
SET @device_type = 'disk';
 
EXEC master.dbo.sp_addumpdevice
@devtype = @device_type,
@logicalname = @db,
@physicalname = @backup_device;
 
BACKUP DATABASE @db TO  @db WITH NOFORMAT, INIT,
NAME = @backup_name , SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
 
EXEC sp_dropdevice @db;
GO

--create in master
CREATE PROCEDURE [dbo].[backup_db_all]
AS
	exec backup_db 'Ken_Databasename1'
	exec backup_db 'Ken_Databasename1'
GO
 
--exec backup_db_all

Call stored procedure from batch file:

• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db 'databaseName','C:\TEMP\'"
• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db_all"