Ms SQL Express server backup script

Who said there are no backups in SQL Express Server? Prepare SQL script like below:

#. 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
#. 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
#. 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.).

CAUTION!

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.

*** ANNEX

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:

#. 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:

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