Backing up your Microsoft SQL Server Database

If you are using the full version of Microsoft SQL Server Database you will have the option of adding a scheduled task to backup your database and will be able to specify the location. For some customers the full version of SQL server is not required but the option to schedule the backup is useful.For backing up your Microsoft SQL Server Database you have a number of Options..

SQL Server Backup logo

 

 

SQL Server Backup and FTP

– its a free backup program which will backup and zip the database , store the zip in a location of your choice and optionally ftp the file to Dropbox or Google drive (this option is not free) and you can set an email to be sent each night to confirm the backup has executed. We ftp the zip to a remote server . We have used this on a number of sites with somplete sucess. –  http://sqlbackupandftp.com/

Blurb from there site
SQLBackupFTP is ideal for any SQL Server database from which backups have to be sent daily to a remote FTP server. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, since they don’t have built-in in tools for backup, but it can be used in any SQL Server Version. Local Instances when SQLBackupAndFTP is installed on the server (Backups are done using BACKUP DATABASE command). If you use shared hosting and can connect to your SQL server using SQL Management Studio (SQLBackupAndFTP generates script (T-SQL) files to restore database)

 

microsoft SQL Server Backup

MS Stored Procedure

– Using a stored procedure on the server to backup the files and a scheduled task in your scheduler to execute the stored procedure each night – http://support.microsoft.com/kb/2019698

// 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

Now you will need to create a batch file to fire off the stored procedure – you may need to add username and password if you are running this from a location other than the server.

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.

Finally, schedule a job by using Windows Task Scheduler to execute the batch file that you created in step B. To do this, follow these steps:

  1. On the computer that is running SQL Server Express, click Start, point to All Programs, point to Accessories, point toSystem Tools, and then click Scheduled Tasks.
  2. Double-click Add Scheduled Task.
  3. In the Scheduled Task Wizard, click Next.
  4. Click Browse, click the batch file that you created in step B, and then click Open.
  5. Type SQLBACKUP for the name of the task, click Daily, and then click Next.
  6. Specify information for a schedule to run the task. (We recommend that you run this task at least one time every day.) Then, click Next.
  7. In the Enter the user name field, type a user name, and then type a password in the Enter the password field.Note This user should at least be assigned the BackupOperator role at SQL Server level if you are using one of the batch files in example 1, 3, or 4.
  8. Click Next, and then click Finish.
  9. Execute the scheduled task at least one time to make sure that the backup is created successfully.

Contact Information

Drop us a line or call and we'll discuss how we can help with your projects.

Handheld.ie
Waterford City Enterprise Centre
Waterford Business Park
Waterford

  • 051 391338
  • info@handheld.ie

Posts Categories