--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"