Automate database backup of SQL Server Express Edition

How Automate database backup of SQL Server Express Edition

 As we all know that SQL Server Agent will not be available in Express editions of SQL Server.

That makes the DBA Task quite tricky for automating the backups.

Here is the way to go

Create the script for backup

Backup database databasename
to disk='Path\backupfilename.bak',description='backuptaken by dba team'
Go

Save the above script in the disk

say : backupscript.sql

Connect to SQL Server using SQLCMD

SQLCMD -S servername -E -i Path\backupscript.sql

Above code will take the backup when executed from Command prompt

Schedule the batch file in windows scheduler.

One more Query:

 CREATE PROCEDURE [dbo].[dbbkup_proc]
@databaseName sysname, @backupType CHAR(1)

AS BEGIN SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
IF @backupType = 'F'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''
IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
EXECUTE sp_executesql @sqlCommand
END
Now create a file and save it on disk as backupscript.sql

exec dbbkup_proc 'dbname','F'
Go

Now create batch file as

SQLCMD -S servername -E -i path\backupscript.sql

Schedule the above batch file

Hope the above helps

Thanks