Quick and dirty database replication with MS-SQL

posted by Yves Lacombe in Best Practices on Oct 18, 2010


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.


Share this article


Share

Comments