LiveZilla Live Chat Software

Quick and dirty database replication with MS-SQL

Written by Yves Lacombe on . Posted in Best Practices

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.

Trackback from your site.

Yves Lacombe

Yves Lacombe

Yves Lacombe has been working on Internet Infrastructure products for over 15 years. He is an Internet Security expert and one of his company's gurus. He has forgotten more things about Email Security than most people will ever know. He runs numerous heavily secured email servers and is constantly getting in trouble while trying to hack into his company's products. Yves has two mottos that he lives by: “The buck stops here” and “Lets just get the job done”.

Leave a comment

Whitepaper Downloads

  • Spam Industry terms
  • Antispam checklists
  • Tips and tricks
  • In depth research analysis

Download

Customer Quote

I previously tested three other products, and modusGate was by far the best.

    Jennifer Mocherman
    Windows ITPro