Ms SQL Express server backup script

Who said there are no backups in SQL Express Server?
Prepare SQL script like below:
  1. 2BACKUP DATABASE [database_name] TO DISK = N’C:\Backup_baz\database_name’ WITH NOFORMAT, INIT, NAME = N’database_name-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  2. 4BACKUP DATABASE [database2_name] TO DISK = N’C:\Backup_baz\database2_name’ WITH NOFORMAT, INIT, NAME = N’database2_name-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  3. 6GO
where database_name and database2_name are should be names of databases you want to backup,
DISK – path for backups,
NAME – name of the backup.
Full description of options is there.

Then save script somewhere with sql extension, let’s say in:c:\scripts\db_backup.sql.

Check if it’s OK running in command window (use right path to SQLCMD.EXE):

"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -S server_name\instance_name -i "C:\scripts\db_backup.sql"

OK, now let’s create task in Scheduler that each day will run ourdb_backup.sqlscript.
Opencmd.exewindow and run following command:

schtasks /create /tn "SQL backup" /tr " ’C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE’ -S server_name\sql_instance_name -i ‘C:\scripts\db_backup.sql’ " /sc DAILY /st 22:00:00

  • /tn – task name,
  • /tr – path to SQLCMD.EXE interpreter,
  • /S – name of server and SQL Server instance name,
  • /i – path to our sql script,
  • /sc – how often task is run, (each day),
  • /st – what time task is run, (10p.m.).
Look carefuly at quotes in /tr parameter! Full command is in “ (double quotes) but particular paths are enclosed with ‘ (single quote).

Put the right path to SQLCMD.EXE, it can differ between SQL Server versions.

How to make SQL script for many databases?

Create another SQL script like this:

  1. 2USE <server_name>\<instance_name>;
  2. 4GO
  3. 6SELECT name FROM sys.databases;
  4. 8GO
and save it, ex:C:\scripts\list_db.sql.
Then run it in command window redirecting output to file:

" ’C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE’ -S server_name\sql_instance_name -i ‘C:\scripts\list_db.sql’ " > db_list.sql

It will print all names of databases to db_list.sql.
Content of this file will look like this:
  1. 2foo
  2. 4bar
  3. 6baz
  4. 8qux
OK, time to do magic with VIM… and YES, there’s Windows versionof VIM.
Open db_list.sql in VIM press : (colon) (pressing colon will put VIM in command mode) and put following command:
  1. 2%s/ .*$//g
press ENTER and it will remove spaces that exist at the and of some lines.
Then press : (colon) again and make another command:
  1. 2%s/\(^.*$\)/BACKUP DATABASE [\1] TO DISK = N’C:\\Backup_baz\\\1′ WITH NOFORMAT, INIT, NAME = N’\1 Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10/g
press ENTER and the magic will happen.
In this example:
  • \(^.*$\) – will copy content of particular line,
  • ‘Backup_baz’ – is folder for backup,
  • \\ – will create one backslash,
  • \1 – will put that content that was matched by \(^.*$\) which is database name
Press G (Shift + g), press ENTER and write: GO. Press ESCAPE twice.
Save file and exit VIM pressing Z (Shift + z) twice.
File is ready. It should look like this (see example above):
  1. 2BACKUP DATABASE [foo] TO DISK = N’C:\Backup_baz\foo’ WITH NOFORMAT, INIT, NAME = N’foo-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  2. 4BACKUP DATABASE [bar] TO DISK = N’C:\Backup_baz\bar’ WITH NOFORMAT, INIT, NAME = N’bar-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  3. 6BACKUP DATABASE [baz] TO DISK = N’C:\Backup_baz\baz’ WITH NOFORMAT, INIT, NAME = N’baz-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  4. 8BACKUP DATABASE [qux] TO DISK = N’C:\Backup_baz\qux’ WITH NOFORMAT, INIT, NAME = N’qux-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
  5. 10GO