How to Automate SQL SERVER EXPRESS Backup


If your using this and you are accepting the license terms and conditions given on this link: http://opensource.org/licenses/ms-pl.html

Microsoft SQL Server Express edition  doesn’t have the SQL Server Agent that enables you to schedule jobs.I guess that’s a paid for extra !!

So to back up your databases, you need to create a T-SQL script and run it through Task Scheduler.

You can use this to Automate SQL Express Database Backup on
SQL Server Express 2005,
SQL Server Express 2008,
SQL Server Express 2012.

Information and step to follow :

Step 1. Create a stored procedure in your master database:

a. Open SQL Server Management Studio connect to database engine.
b. Click New Query
c. Copy all the text from here and paste it into New Query Window then click Execute. it should show [Command(s) completed successfully.]

automate backup 1

The following script :

———————————————————————————————————————————————————————-

–Copyright © Microsoft Corporation.  All Rights Reserved.

–This code released under the terms of the

— Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: Microsoft

— Create date: 2010-02-06

— 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’,’master’,’model’,’msdb’ )

END

ELSE IF @backupType=’D’

BEGIN

DELETE @DBs where DBNAME IN

(‘tempdb’,’master’,’model’,’msdb’ )

END

ELSE IF @backupType=’L’

BEGIN

DELETE @DBs where DBNAME IN

(‘tempdb’,’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

———————————————————————————————————————————————————————-

d. Close SQL Server Management Studio.

Note: if you want to edit the stored procedure once it is created:
Expand Database -> System Database -> master -> Programmability -> Stored Procedure -> Right click sp_BackupDatabases -> Modify.

You can also modify the script above me if there are some databases that do not want to backed up.

like this :

automate backup 3

Step 2. Create .bat file to call the procedure

a. Run the following script to automate backups (file. bat) :

———————————————————————————————————————————————————————-

echo off
cls

REM The sqlcmd utility lets you enter Transact-SQL statements.

REM [-S] Specifies the instance of SQL Server to which to connect.

REM [.\instance name of the SQL Server] Specifies the instance
REM of SQL Server to which to connect.

REM [-E] Uses a trusted connection instead of using a user name
REM and password to log on to SQL Server.

REM [-Q] Executes a query when sqlcmd starts and then immediately exits sqlcmd.

REM Quotation marks around the query which is going to executed [” “]

REM The path of the backup folder [‘D:\SQLBackups\Automate\’]

REM Type of backup you want to take [F=full, D=differential, L=log]

REM ==============================

REM ——————————

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\Automate\’, @backupType=’F'”
REM ——————————

———————————————————————————————————————————————————————-

automate backup 2

following script to remove the results automate backups (file .bat)

“that the results of the backup files that accumulate cannot spend the hard drive size”

———————————————————————————————————————————————————————-

@ECHO OFF
REM Del is used to delete files.

REM Put the path of the file between Double quotation marks
REM [“Path of file”] “D:\SQLBackups\Automate\*.bak”

REM Star [*] is a wildcard to delete all the files in the
REM folders where [.bak] is the file extraction

REM ———————————

del “D:\SQLBackups\Automate\*.bak”

REM ———————————

———————————————————————————————————————————————————————-

Test the command

“Test that your command works by saving the .bat file, then double clicking it to run it. A Windows Command Line window will open whilst the script is executing. When completed you should be able to see a new .bak file in the folder you specified.”

 

Step 3. Scheduled Task for a batch file to take backup and delete backup

  1. Open task scheduler from Start >> Administrative Tools >> Task Scheduler
  2. Click “Create Basic Task” and enter a relevant name, e.g. Daily SQL Backup
  3. Under “Triggers” select ‘Daily’ and then choose a time to run the backup.
    Try to choose a time when your database traffic is low, typically in the early hours of the morning. Also make sure that it doesn’t overlap with other scheduled tasks like Windows Updates that may reboot the server.
  4. Under “Action” select ‘Start a program’ and browse to the script you created earlier

Your database(s) will now be being backed up on a daily basis. This is always a good thing!

Note: You can edit the scheduled task anytime you want by double-clicking task -> target -> edit

Source: http://support.microsoft.com/kb/2019698

3 thoughts on “How to Automate SQL SERVER EXPRESS Backup

  1. My programmer is trying to convince me to move to .
    net from PHP. I have always disliked the idea because of the costs.
    But he’s tryiong none the less. I’ve been using Movable-type on
    various websites for about a year and am concerned about switching to another platform.
    I have heard very good things about blogengine.net. Is
    there a way I can transfer all my wordpress content into it?
    Any help would be greatly appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s