SQL Server 2008 Express - "Best" backup solution?

Hi!

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 x-copy backups of that server.

But unfortanetly replication is not available in the express edition.

The site is heavily accessed, so there has to be no delays och 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 don't know if that helps me since i'm using the express version.

Thanks

From serverfault Alexander Nyquist

• 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:

  1. Use Microsoft SQL Server Management Studio Express which has the Backup option on the right click menu for each database under "tasks."
  2. 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 -i C:\SqlJobs\backup.sql -o C:\Logs\output.txt
  

  Martín Marconcini : The BACKUP syntax looks something like this: BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak';

  Alexander Nyquist : Thanks for this information guys, i'm going to try this out.

  redknight : when using the T-sql script, make sure your db names do not contain spaces.

  Tomalak : +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.

  Ian Boyd : 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.
  From splattne

• 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.

  Ian Boyd : You should also mention *how* a batch file backs up a database.
  From no_one

• I use expressmaint, 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 application event log on failure.

  From UndertheFold

• I use SQLBackupAndFTP - fantastic and simple product.

  From $pageUsers[$post.author].name

• 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 ''
  

  From Ngân Phạm

• Just wanted to support Ruslan's answer above - SQLBackupAndFTP is an awesome, free, simple program! Was pulling my hair out until I found it! (SQL Server N00b)

  From

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

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

  I then used on 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 history if there is corruption. The logs (-RU) are kept for 1 week.

  I've only been using it for a few weeks, but been very happy with it, as its an 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 clound storage.

  From Guppy

• You can use DBSave, it's a great freeware tool to backup and restore ms sql server. It's verry simple to setup and to use.

  From MP1963