• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

MSQL - How can I keep a backup database up to date.

Situation.  I have two servers.  A production and a backup.  I want to make sure the production server is backing its MSQL database to the backup.  Is there a way to update the backup database rather then a full backup?  The idea is all I would have to do is change the IP of the backup server.
0
Bgauth
Asked:
Bgauth
1 Solution
 
MARTiNCommented:
Maybe this is something you could find useful?
http://msdn.microsoft.com/en-us/library/ms189047.aspx
0
 
sarabhaiCommented:
It's need to be down time to do this task.
or you can use the reflection or database mirroring.
 
0
 
gwickertCommented:
Hi Bgauth,

If I understand you correctly, you have 'DatabaseA' on 'ServerA' and 'Database B' on 'ServerB'.  You want 'DatabaseB' on 'ServerB' to always be the same as 'DatabaseA' on 'ServerA'?

I believe this can be done with Database mirroring in SQL, however if you want a simple, yet effective way to get much the same result, you could do the following:

1. Setup an SQL Database backup on Server A to backup Database A once a day or twice a day, depends how up to date you want your backup to be in the case you need to restore it.
2. Set the location of the backup to a share on the 'backup' server - eg \\ServerB\sqlbackup
3. In the case the primary goes down, you will always have the most recent backup stored on your backup server.
4. When the primary goes down, simply open SQL manager and restore the database backup file on ServerB.
5. redirect your SQL frontend applications to point to 'sql:\\ServerB\databasename' rather than sql:\\ServerA\databasename'

This is more of a small business solution, whereby implementing proper SQL mirroring would be over complicating things.  Below are some articles on proper SQL mirroring if you like to read those and compare the 2 methods.

http://technet.microsoft.com/en-us/library/cc917680.aspx
http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx
http://searchsqlserver.techtarget.com/tip/Database-mirroring-setup-in-SQL-Server-2005#works

The third link provides a good overview and guide, in a quick manner.

Let me know what you decide to do and I'll anymore insight I can.

Glen
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now