Backing up SQL Express Edition

  • Article

Using Microsoft SQL Express could be painful, if you don't care about backup and recovery. Using the following script, you can export backup files and transfer them to remote servers to ensure data availabilty.

/*  SQL Server Backup Script  Copyright: isiCore GmbH, 2011 (http://www.isicore.de)  License: GPL 3 (http://www.gnu.org/licenses/gpl-3.0.html)  
Limited liability: isicore does not take responsibility for  loss of  data or any damage to your systems, which might be caused by the original or adapted versions of the script. The execution of the script should be tested - especially after alteration - within a test environment. A backup  prior  execution is  recommended. */ 
 
USE master 
GO 
DECLARE @isicore_backup_dir nvarchar(150) = 'C:\temp\sqlbackup\'
DECLARE isicore_cur CURSOR FOR 
    SELECT name FROM sys.databases WHERE name NOT IN ('tempdb') 
DECLARE @isicore_db_name nvarchar(100); 
DECLARE @isicore_backup_name nvarchar(250); 
OPEN isicore_cur; 
FETCH FROM isicore_cur 
    INTO @isicore_db_name; 
WHILE @@FETCH_STATUS = 0 
   BEGIN 
      SELECT @isicore_backup_name = @isicore_backup_dir + @isicore_db_name + '.bak'; 
      BACKUP DATABASE  @isicore_db_name TO  DISK = @isicore_backup_name 
          WITH FORMAT, MEDIANAME = @isicore_db_name, NAME = @isicore_db_name; 
      FETCH NEXT  FROM isicore_cur into @isicore_db_name; 
   END
CLOSE isicore_cur 
DEALLOCATE isicore_cur 
GO