Microsoft SQL and SQL Express both provide a T-SQL (Transact SQL) command-line interpreter that you can use to automate backup and restore processes. It enables you to utilize publisher/publishee configurations for real-time or near real-time replication.

Sometimes, though, all you may want to do is run a daily backup of the database on one SQL Server and restore it to a second SQL Server to keep a ‘warm standby’ available. This can be done easily with a pair of simple batch files. Here’s how you do it:

MS-SQL 2003 and above come with a T-SQL command line interpreter called sqlcmd.exe

MS-SQL 2000 provides osql.exe, which accepts the same parameters

Backup: To backup the database to disk, the command line takes this form*:

SQLCMD -S <serverinstance> -U SQLUser -P SQLPassword -Q ‘BACKUP DATABASE <yourdb> TO DISK='<path>’ WITH INIT;’

Example:

SQLCMD -S .SQLEXPRESS -U DBuser -P somepassword -Q ‘BACKUP DATABASE MyDB TO DISK=’c:replication’mydb.bak’ WITH INIT;’

*Note that the SQL User must have sufficient privileges to perform the backup.

Restore: To restore the database from disk, the command line takes this form*:

SQLCMD -S <serverinstance> -U SQLUser -P SQLPassword -Q ‘RESTORE DATABASE <yourdb> FROM DISK='<path>’ WITH REPLACE;’

Example:

SQLCMD -S .SQLEXPRESS -U DBuser -P somepassword -Q ‘RESTORE DATABASE MyDB FROM DISK=’c:replicationmydb.bak’ WITH REPLACE;’

*Note that a database (even if blank) must first exist on the server you are restoring to prior to running this command.

Tying it all together:

Now you can create a simple batch file on the primary SQL Server that:

• Backs up your database, and
• Copies it to a central share or file store

On the target system, your batch file would:

• Copy the file from the share to a local folder
• Restore it to the local database instance

Source Server:

@echo off

SQLCMD -S .SQLEXPRESS -U DBuser -P somepassword -Q ‘BACKUP DATABASE MyDB TO DISK=’c:replicationmydb.bak’ WITH INIT;’

copy /y c:replicationmydb.bak \fileservershare

Backup Server:

@echo off

copy /y \’fileserversharemydb.bak c:replication

SQLCMD -S .SQLEXPRESS -U DBuser -P somepassword -Q ‘RESTORE DATABASE MyDB FROM DISK=’c:replicationmydb.bak’ WITH REPLACE;’

Use the Task Scheduler on both servers to automate the processes. But – make sure that the restoration on the backup server has a sufficient time offset to give the primary server enough time to complete the backup in the first place.