Schedule and Automate backups of SQL Server databases in SQL Server Express
Dynamics AX Retail POS mostly uses SQL Server express per store database. SQL Express version is attractive because it provides a lot of value and free. This is especially true for retailers that have many stores as the cost of database software licensing would quickly become cost prohibitive. The use of SQL Express requires careful planning and consideration for ongoing maintenance and an understanding of how the database will grow.
SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.
This article describes how to use a Transact-SQL script together with Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis
Follow these 3 steps to back up your SQL Server databases by using Windows Task Scheduler:
Step 1:
Use SQL Server Management Studio Express to create the following stored procedure in master database:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
Create PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name
-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB','master', 'model','msdb')
END
ELSE IF @backupType='D'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB', 'master', 'model','msdb')
END
ELSE IF @backupType='L'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB', 'master', 'model','msdb')
END
ELSE
BEGIN
RETURN
END
-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
-- Execute the generated SQL command
EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END
Step 2:
In a notepad, write the batch file code depending upon your scenario. The SQL ID used for backup should have at least the Backup Operator role in SQL Server.
Example: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication
//File Name: Sqlbackup.bat
//@backupType = 'F' : F means Full Backup
//If your instance is SQLEXPRESS use \SQLEXPRESS. If it is defaulut mention \ only
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’YOURDB’, @backupType='F'"
Similarly, you can make a differential backup of YOURDB by pasting in 'D' for the @backupType parameter and a log backup of YOURDB by pasting in 'L' for the @backupType parameter.
Step 3:
Schedule a job by using Windows Task Scheduler to Run the batch file that we created earlier in step 2. To do this, follow these steps:
1. On the computer that is running SQL Server Express, click Start, Serach for Task Scheduler, and then click Task Scheduler.
In the Task Scheduler click Create Task.
2. In General Tab, Type SQLBACKUP for the name of the task, Run whether user is logged in or not option.
3. In Trigger Tab then Click New, click Daily and Specify information for a schedule to run the task. (As recommend that you run this task at least one time every day.) Then slect enabled option, and then click Ok.
4. In Action Tab then click New, Click Browse, click the batch file that you created in step 2, and then click Ok.
5. Run the scheduled task at least one time to make sure that the backup is created successfully.
That's It!!!!
Make sure that there should be space on the drive to which the backups are being taken.
Source: Microsoft
