Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server primary database backup options

Copy MDF and LDF files from primary SQL Server databases to backup databases with these best practices offered by SQL Server expert Andrew Novick.

I am not an expert when it comes to SQL databases. I have two active SQL servers (two separate machines with their own static and unique IPs) on the same network. What I want to do is the following:

1.) Create a link between the two machines so that it is no longer necessary for me to copy the LDF and MDF files.

2.) If one database gets an update it should automatically update the other one as well.

How do I do this?

From your question it sounds like you're copying the MDF and LDF files from your primary database to your backup database. I'd guess that you're doing this on a nightly basis. You can achieve similar or better functionality using these approaches:

Approach Latency
Restore a nightly backup Overnight
Log shipping 5 to 60 minutes
Transactional replication Very quick. A couple minutes

By latency, I mean the time difference between when an update is posted to the primary database until it is posted to the backup database. The less latency the more similar the two databases are.

The first approach, restore a nightly backup, is similar to the database file copy that you use now. By switching to restoring a backup you can avoid taking SQL Server down.

The second approach, log shipping, depends on making transaction log backup files from your primary database at frequent intervals and then applying them to the backup database. Log shipping is part of the SQL Server 2000 Enterprise Edition. However, it can be achieved in Standard edition through some script writing.

Transactional replication pumps updates from the primary database to the backup database at a very high frequency. It can keep the backup database almost in synchronization with the primary database. However, it uses the most resources.

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.