Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Scheduled backups on SQLExpress

From Wiki

Jump to: navigation, search

SQL Server 2008 (R2) Express Edition will not allow you to utilize the SQL Server Agent Services. Due to this, when you use this edition on production systems, creating scheduled backups can be more complicated.

Below is a quick solution you can push in using Windows Scheduled Tasks and the SQLCMD.exe that ships with SQL Server.

What you will need to know: Based on the SQL Server version, the Binn folder location will change from 80,90 or 100. This will match the compatibility mode in which the database server is. So SQL Server 2000 = 80, SQL Server 2005 = 90 and SQL Server 2008 = 100. This will match the compatibility level of the databases if the databases were created with the same version. It is however possible to have older versions while not upgrading to the current level of the database server exist.

To find out what they are you can

  1. select cmptlevel,[name] from sysdatabases

In the case of this example, SQL Server 2008 was used to the path to the SQLCMD.exe is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe

The complete call would appear as follows in either a bat or cmd file C:\Program Files\Microsoft SQL Server\100\Tools\Binn>sqlcmd -S SERVERNAME\SQLEXPRESS -i C:\BackupAllDatabasesButTempDB_Model.sql -o C:\BackupLog.txt

Note: The script name in our case is BackupAllDatabasesButTempDB_Model.sql and the logging file is C:\BackupLog.txt. This is specified with the output switch of -o

In the script below you will also need to adjust the variable that holds the backup location. This is where the .bak files will be written to. Set @backuploc = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'

The script file contains the following

  1. Declare @tbl Table
  2. (id int identity(1,1), dbname varchar(255),dbid int)
  3. Declare @int int
  4. Declare @cmd varchar(max)
  5. Declare @dbname varchar(255)
  6. Declare @backuploc varchar(1000)
  7. Declare @timestamp varchar(20)
  9. Set @backuploc = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'
  10. SELECT @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','')
  11. Set @int = 1
  12. Set @cmd = ''
  13. Insert Into @tbl
  14. Select [name],dbid from sysdatabases where [name] not in ('tempdb','model')
  16. While @int <= (Select count(*) from @tbl)
  17.  Begin
  18.    Set @dbname = (select dbname from @tbl where id = @int)
  19.    Set @cmd = 'Backup Database ' + @dbname + ' To Disk = ' +
  20.                '''' + @backuploc + @dbname + '_' + @timestamp + '.bak' + ''''
  21.         --Print @cmd
  22.         Exec(@cmd)
  23.    Set @int = @int + 1
  24.  End

This will create a backup of each database (not TempDB and Model) and add a time stamp to the file name. In order to preserve space and if one days retention for a backup is enough, you can use the WITH INIT option by changing the @cmd variable to the below string

  1. Set @cmd = 'Backup Database ' + @dbname + ' To Disk = ' +
  2.                '''' + @backuploc + @dbname + '.bak' + '''' + ' WITH INIT'

--onpnt 18:13, 7 July 2010 (GMT)

783 Rating: 2.5/5 (44 votes cast)