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 <server\instance> -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 <server\instance> -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:\replication\mydb.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:\replication\mydb.bak’ WITH INIT;’
copy /y c:\replication\mydb.bak \\fileserver\share
Backup Server:
@echo off
copy /y \\’fileserver\share\mydb.bak c:\replication
SQLCMD -S .\SQLEXPRESS -U DBuser -P somepassword -Q ‘RESTORE DATABASE MyDB FROM DISK=’c:\replication\mydb.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.
Leave a Comment