Q
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 fi...

les.

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.

This was last published in March 2005

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close