SQL Server 2008 Express - "Best" backup solution?

Question

What backup solutions would you recommend when using SQL Server 2008 Express? I'm pretty new to SQL Server, but as I'm coming from an MySQL background I thought of setting up replication on another computer and just take Xcopy backups of that server.

But unfortunately replication is not available in the Express Edition.

The site is heavily accessed, so there has to be no delays and downtime. I'm also thinking of doing a backup twice a day or something.

What would you recommend? I have multiple computers I can use, but I don't know if that helps me since I'm using the Express version.

Does anybody know if there are issues with just using Windows Server Backup to do this? — James, Dec 22 '11 at 19:57

score 30 · Accepted Answer · edited Apr 08 '12 at 03:37

30

SQL Server Express 2008 supports database backups. It's missing SQL Agent, which allows to schedule backups, and the maintenance plan wizard for creating a backup tasks.

You can backup databases in two different ways:

Use Microsoft SQL Server Management Studio Express which has the Backup option on the right click menu for each database under "tasks."
Use T-SQL to manually write your backup script. Read the MSDN documentation for the T-SQL BACKUP command.
Syntax something like: BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak';

If you want to schedule your backup jobs, you have to write a T-SQL script and then use the Windows Task Schedule to call SQLCmd to run the script on what every schedule you're interested in:

 sqlcmd -s server_name\sqlexpress -i C:\SqlJobs\backup.sql -o C:\Logs\output.txt

edited Apr 08 '12 at 03:37

Community

1

answered May 05 '09 at 07:59

splattne

28,348
19
97
147

1

The BACKUP syntax looks something like this: BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak'; – Martin Marconcini May 05 '09 at 11:18
Thanks for this information guys, i'm going to try this out. – alexn May 05 '09 at 11:49
when using the T-sql script, make sure your db names do not contain spaces. – redknight Jun 19 '10 at 08:09
+1 One more bit of info: Don't forget to mention the instance name via the `-S ".\SomeInstance"` if you get login errors from sqlcmd. – Tomalak Jul 28 '10 at 15:18
It would be helpful, for the rest of us coming to this question looking for the same answer, if you included the SQL you use to run a backup. – Ian Boyd Aug 30 '10 at 03:49

score 6 · Answer 2 · answered Jun 19 '09 at 20:09

6

I use SQLBackupAndFTP - fantastic and simple product.

answered Jun 19 '09 at 20:09

sqlbackupandftp is best.. if u want back + compress + auto delete (after specified days) – suraj jain Sep 15 '12 at 14:16
Yes I also use this. Works well. – Alan B Mar 26 '13 at 16:49

score 5 · Answer 3 · answered Dec 18 '09 at 06:46

I was wrote backup script for use myself, install as metioned in post by splattne:

----- Version: 2.0 - 2009-12-12 - SQL 2000 Compatible
----- Pham Kim Ngan (jbngan@gmail.com)
----- Usage:
-- Copy 7za.exe (http://www.7-zip.org/download.html - Command Line Version) to @CFG_BACKUP_PATH
-- Modify @CFG_BACKUP_PATH = <Backup Store Path> - no long filename/directory please
-- Modify @CFG_DAYS_DELETE = Days to keep backups
-- Enable 'xp_cmdshell' (SQL 2005/EXPRESS or higher)

----- Configuration Variables
DECLARE @CFG_BACKUP_PATH NVARCHAR(256)
DECLARE @CFG_DAYS_DELETE INT

SET @CFG_BACKUP_PATH = 'C:\DatabaseBackup'
SET @CFG_DAYS_DELETE = 30

DECLARE @Today DATETIME
DECLARE @TodayName CHAR(8)
SET @Today = GETDATE()
SET @TodayName = CONVERT(CHAR(8), @Today, 112)

DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @cmd VARCHAR(256)

----- Create Temporarity Directory
DECLARE @TempDir VARCHAR(256)
SET @TempDir = @CFG_BACKUP_PATH + '\' + CONVERT(VARCHAR(256), NEWID())
SET @cmd = 'md ' + @TempDir
EXEC xp_cmdshell @cmd, no_output

----- List of current databases, only 'ONLINE' databases to be backup
DECLARE @dbList TABLE
    (
      dbno INT IDENTITY,
      dbname NVARCHAR(256)
    )

INSERT  INTO @dbList ( dbname )
        SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   ( name NOT IN ( 'tempdb' ) )
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'


------ Starting backup, one by one
SELECT  @id = dbno,
        @name = dbname
FROM    @dbList
WHERE   dbno = 1
WHILE @@ROWCOUNT = 1
    BEGIN
        PRINT N'++ Backup: ' + @name
        SET @path = @TempDir + '\' + @name + '.bak'

        BACKUP DATABASE @name TO DISK = @path

        SELECT  @id = dbno,
                @name = dbname
        FROM    @dbList
        WHERE   dbno = @id + 1
    END

PRINT N'++ Compressing: ' + @TempDir

----- Delete output file if existed
SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP'
EXEC xp_cmdshell @cmd, no_output

DECLARE @Count INT
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
----- Compress, -mx1 = Set Compression Ratio to 1 (very low)
SET @cmd = @CFG_BACKUP_PATH + '\7za.exe a -bd -y -tzip -mx1 '
SET @cmd = @cmd + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP ' + @TempDir + '\*.bak"'
EXEC xp_cmdshell @cmd, no_output

SET @Count = DATEDIFF(second, @StartTime, GETDATE())
PRINT N'++ Compression Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'
SET @Count = DATEDIFF(second, @Today, GETDATE())
PRINT N'++ Total Execution Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'

---- Delete temporarity directory
SET @cmd = 'rd /s /q ' + @TempDir
EXEC xp_cmdshell @cmd, no_output

---- Delete previous backup versions
DECLARE @OlderDateName CHAR(8)
SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112)

----- List all .ZIP files
CREATE TABLE #delList
    (
      subdirectory VARCHAR(256),
      depth INT,
      [file] BIT
    )
INSERT  INTO #delList
        EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1
DELETE  #delList
WHERE   RIGHT(subdirectory, 4) <> '.ZIP'

SELECT  @Count = COUNT(1)
FROM    #delList
PRINT N'++ Number of Backups: ' + CONVERT(NVARCHAR, @Count)

SELECT TOP 1
        @name = subdirectory
FROM    #delList
WHERE   LEN(subdirectory) = 12
        AND RIGHT(subdirectory, 4) = '.ZIP'
        AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName

WHILE ( @@ROWCOUNT = 1 ) 
    BEGIN
        PRINT N'++ Delete Older Backup: ' + @name
        SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @name
        EXEC xp_cmdshell @cmd, no_output

        DELETE  #delList
        WHERE   subdirectory = @name

        SELECT TOP 1
                @name = subdirectory
        FROM    #delList
        WHERE   LEN(subdirectory) = 12
                AND RIGHT(subdirectory, 4) = '.ZIP'
                AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName
    END

DROP TABLE #delList

PRINT N'++ Done.'
PRINT ''
PRINT ''
PRINT ''

score 2 · Answer 4 · edited Jun 23 '11 at 17:43

2

I use ExpressMaint, and it works great as a scheduled task. Just pass the appropriate parameters for the type of job you are doing.

The source code is also out there. We changed it slightly to add an entry in the application event log on failure.

edited Jun 23 '11 at 17:43

Peter Mortensen

2,319
5
23
24

answered May 06 '09 at 04:19

CPU_BUSY

2,322
17
17

score 1 · Answer 5 · edited Apr 13 '17 at 12:14

Based on UndertheFold's post I did some googling and found the details of ExpressMaint. I'd not seen this before, so I am very pleased to have found it.

FYI, the web page is, http://expressmaint.codeplex.com/

I then used one of the examples I found to create this batch file that I have scheduled to run overnight every day.

c:\expressmaint\expressmaint -S (local)\SQLExpress -D ALL_USER -T DB -R E:\backups\sqlexpress\backupreports -RU WEEKS -RV 1 -B E:\backups\sqlexpress -BU DAYS -BV 4 -V -C

This takes a backup, keeps each backup (-BU) for four days, so you get a history if there is corruption. The logs (-RU) are kept for one week.

I've only been using it for a few weeks, but I have been very happy with it, as it's a hands-off approach. Locally I put the backups on a second disk, I then use JungleDisk to do an offsite backup to the Amazon EC2 cloud storage.

The problem with expressmaint is it does not support SQL Server 2008 R2. It will work with 2008 and it will work if you install 2008 and then upgrade to 2008 R2, but it will not find the databases on a fresh install of 2008 R2. It's a known bug with a patch submitted but the executable has not been updated with the patch. — James, Dec 22 '11 at 18:30

score 1 · Answer 6 · edited Jun 23 '11 at 18:01

1

You can use DBSave. It's a great freeware tool to backup and restore MS SQL Server. It's very simple to setup and to use.

edited Jun 23 '11 at 18:01

Peter Mortensen

2,319
5
23
24

answered Jun 19 '10 at 08:05

score 0 · Answer 7 · answered May 05 '09 at 08:35

0

I am using a windows scheduler job to backup the SQL Server express database every few hours by using a batch file . Seems to work fine.

answered May 05 '09 at 08:35

no_one

111
1

You should also mention *how* a batch file backs up a database. – Ian Boyd Aug 30 '10 at 03:47

score 0 · Answer 8 · answered Apr 09 '13 at 16:12

0

I'd check out SQLServerBooster: http://www.sqlserverbooster.com.

It's looks like it has a nice feature set and is free. The support for azure/S3/glacier is nice as well.

answered Apr 09 '13 at 16:12

Ben Lachman

101
1

SQL Server 2008 Express - "Best" backup solution?

8 Answers8