5

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.

4 Answers 4

10

Since you are using SQL Server Express, you will have to get creative. You don't have any SQL Server native scheduling tool, you can utilize Task Scheduler, and schedule a daily task to run SQLCMD, with query text to backup your database.

I'd recommend creating a script that does some string manipulation to generate a day-unique filename for the BACKUP DATABASE T-SQL.

Your script could resemble something like this:

-- declare the backup filename (with path) without the file extension
declare @DestFile varchar(128) = 'C:\BackupDir\BackupFileName_';

-- get the current date as a nice and sortable string
declare @CurrentDate varchar(64) = '';
set @CurrentDate = replace(convert(varchar(10), getdate(), 111), '/', '');

set @DestFile += @CurrentDate + '.bak';

backup database YourDatabaseName
to disk = @DestFile;
go

Save that T-SQL to a script file (for example's sake, C:\YourScriptDir\BackupDatabase.sql). To run this script using SQLCMD, you could do:

sqlcmd -S YourServerName\YourInstanceName -i C:\YourScriptDir\BackupDatabase.sql

Then just schedule that with Task Scheduler to run daily. It's one giant spaghetti workaround, but that's what you get with a free version.

(the above process would be tailored to a database in Simple Recovery mode. If you are in Full, then you need to consider transaction log backups as well)

2

Try our SQLBackupAndFTP tool. You can run scheduled backups of SQL Server databases (full, differential or transaction log), run file/folder backup, zip and encrypt the backups, store them on a network, FTP server, Dropbox, Box, Amazon S3, Google Drive or SkyDrive, send yourself an e-mail confirmation on job success of failure.

1

https://support.microsoft.com/en-us/kb/2019698 How to schedule and automate backups of SQL Server databases in SQL Server Express

this link above is what you need for SQL Server Express.

add this stored procedure to master db:

// 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','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  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

run the script (or add it in task scheduler):

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"  

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server. 




Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"





Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.
1
  • juFo, can you add some more detail to this answer? Link only answers aren't always useful since the link or its content can change. Commented Sep 21, 2016 at 12:52
0

I have prepared a utility to automate server backups using a simple UI. This lets you create backup schedules for one or more databases (MSSQL and MySQL) and also lets you backup folders by creating a zip file. You can then configure the utility to automatically move your backup files to Amazon S3 bucket and set e-mail alerts for failed/success backup jobs. This utility is an open source project hosted at GitHub. You can download the installer and use directly. You are also welcome to contribute or request new features.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.