Backup Job, Replication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Backup Job, Replication

Author

Topic

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 14:36:30

I have two server. The first is in a remote Data Center which hosts my site and the second server is in my office. I need to hava the same data in both. What I mean is that the same data that's is stored in the first sql server must be copied to the second. But ot should happen automatically. Is it possible? How can i do it?
Later I want do schedule a job to back up the data everyday. I tried to do this but the task did not work correctly.
Anyone could help me?
Thanks a lot.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-17 : 14:41:19

Depending on ur need u may use a suitable type of Replication.
(I mean how soon u want the replication to be done)
If u want it to happen immediately, u can use transaction replication.
But its a big overhead to the Server.

or if u want u can use a DTS to move ur data and it can be scheduled.

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 14:50:06

If this is for disaster recovery purposes, you should use log shipping instead of replication. If it is not for disaster recovery purposes, then you should look into transactional replication. Both topics are discussed in SQL Server Books Online.

For backups, check out my backup stored procedure:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
aka tduggan

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 14:53:51

I don't need it immediately. So the best way is the DTS ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 14:56:10

I would not use DTS for this. So is this for disastery recovery purposes or not?

Tara Kizer
aka tduggan

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 15:02:01

Yes, it' for disaster.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 15:07:02

Do you have Enterprise Edition? If so, then you can use log shipping. If not, then you'll need to develop custom log shipping scripts. Check this out:

http://www.nigelrivett.net/SQLAdmin/LogShipping.html

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-17 : 15:09:10

quote:
Originally posted by deco.oliveira

I don't need it immediately. So the best way is the DTS ?

By "Immediate", I meant to ask how soon the Data in One server to be present in the other.

Not whether u want this to do quickly --

What is ur whole idea behind this?
U need to take reports without disrupting the transactions?
U need to Recover, in case of a data corruption in ur First DB ?
U need data for ur testing purposes ?
Any other ?

Srinika

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 15:17:34

I have the Enterprise Manager.
I did'nt mean "quickly" I wrote in worng way. Sorry !
What I want is to recover in case of a data corruption.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 15:20:53

Enterprise Manager is a tool. It is not the edition. What edition of SQL Server do you have? SELECT @@VERSION

Tara Kizer
aka tduggan

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 15:23:31

I know that is a tool. I just answered your other question. The version is: Microsoft SQL Server 2000 - 8.00.2039 (Build 2195: Service Pack 4).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 15:29:29

We need to know the edition of SQL Server. Do you have Enterprise Edition, Standard Edition, Personal Edition, MSDE, Developer Edition?

Tara Kizer
aka tduggan

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 15:31:07

It's the Standard Edition

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-17 : 15:31:28

deco.oliveira,

Run the following in Query Analyzer to get the Edition

Select SERVERPROPERTY ( 'Edition' )

Srinika

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 15:34:01

Thanks,
It's the Standard Edition.

Deco Oliveira
Oriunda IT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-17 : 15:38:53

You'll need to check out the link that I posted then as the built-in log shipping is only available in Enterprise and Developer editions of SQL Server 2000. Here's the link again:
http://www.nigelrivett.net/SQLAdmin/LogShipping.html

Tara Kizer
aka tduggan

deco.oliveira
Starting Member

8 Posts

Posted - 2006-04-17 : 16:12:09

Thank you Tara and Srinika. I'll try do to what you taught me.

Deco Oliveira
Oriunda IT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources