ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Backup using sqlcmd on SQLExpress

Categories: SQL Server
--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"