Keeping 2 servers synchronized

[dr1256dr]

I am a developer working from home 2 or 3 days a week. I need to keep my home SQL server in synch with my server at work, give or take a few hours. I am running SQL Server 2000.

Currently, I run a backup at night, zip up and download the file in the morning, then do a restore. This works fine, except the download, although only 150 KB or so, takes a very long time. (The initial data backup file is around 1.6 GB before zipping)

Is there a way I can set up a backup procedure that creates a file with ONLY the incremental changes from the past day? I realize this would have to be in addition to the regular backup.

I know businesses keep remote instances of their servers in synch all the time. Since I am developing applications that I need to test in an environment that matches the office as close as possible, I have my home server set up exactly as the work location, (server name, paths, etc).
I imagine this restricts me from using more conventional synch procedures?

I am the DB Admin, and have Terminal Services admin access, along with pcAnywhere full privileges.

I am open to any suggestions.

Thanks in advance

Dennis

 

[DrSql]

What you can perfom is daily you can schdeule a full back up and restore from your prod to dev, else you can schdule a trasnaction logfile bak and restore.

Dr.Sql
Good Luck.

 

[dr1256dr]

Thanks for the quick response, Dr.Sql

I am still a little confused, however. I am already doing a full backup from production, then restoring to development. The problem is that the resultant full backup bak file takes an incredibly long time to download to my home office.

It looks as if the transaction log backup and restore might be my answer, thanks for that tip. The Books Online help on that subject is rather hard to understand for me, and is also filled with various conditions and warnings! But I'll give it a good read.

In case you didn't figure it out, I am pretty new to SQL server management.

Still open to other ideas!

Thanks,

Dennis

 

[Catadmin]

Sounds like what you need is to do differential backups to this database.

Check in Books Online under Backup. Then under Restore. Differential only copies over the data changed since the last backup. I wouldn't rely only on differential backups/restores if the db you are restoring is your disaster recovery resource, but if it's just a development server, this should suit your needs perfectly.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[DrSql]

But if your DB ever got out of sync you still have to do a full restore.

Dr.Sql
Good Luck.

 

[dr1256dr]

Catadmin:

Maybe I have not explained myself very well. (happens frequently!). I want to avoid having to download the large full backup bak file, as it takes a long time. Even if I do an incremental backup/restore, the file size remains the same. What I am trying to accomplish is a means of downloading JUST the data that has changed.

I thought about using DTS to create the tables from the database, then append that to my existing data. But there are 500 some tables, and I'm not sure how to go about that. Plus, I imagine there would be issues with the log file?

It looks as if the transaction log backup and restore might be my answer? The Books Online help on that subject is rather hard to understand for me, and is also filled with various conditions and warnings! But I'll give it a good read.

In case you didn't figure it out, I am pretty new to SQL server management.

Still open to other ideas!

Thanks,

Dennis

 

[k108]

Why not use replication - this will automatically update the changes from your server at work to your home computer. However, this is assuming you have a network connection available. Just an idea.

 

[dr1256dr]

k108,

I guess I can check into that, (replication). It just seemed a little bit extravagant as a starting point.

I was hoping there would be a way to just create a small bak file with ONLY new data, then restore that bak using the append setting.

In effect, I am looking at it like this:

An incremental backup has to "figure out the starting point" when it begins. I would like to be able to write the results from that point on to a bak I that could append to my existing data. The subsequent resultant file would be small and easy to download via pcAnywhere on a daily basis. My db is just for development purpose, and not "mission critical", so I'm not afraid of a burp once and a while. I can always just restore it from a full backup.

Anyway, I guess it's just my inexperience that has me thinking that this, or something similar, should not be so hard to accomplish! Evidently, the backup can only write to the SAME file it is incrementally backing up. So, I guess I'll just keep researching.

I have terminal services access to the db, but in administrative mode. Would replication require a VPN?

Thanks for the response.

Dennis

 

[k108]

The problem with doing a daily incremental backup (called a DIFFERENTIAL BACKUP on SQL Server), is that the incremental contains all the changes from the database from the LAST FULL BACKUP.

So let's say you do a full backup on Monday at midnight.

Then, on Tuesday, you do a DIFF backup. This will contain all the changes since Monday at midnight.

Then, on Wednesday, you do another DIFF backup. This too, will contain all the changes since Monday at midnight.

And so on.

So, each day the DIFF backup gets bigger and bigger (and of course, will take longer to restore), provided you have not done a full backup since Monday at midnight. But it will still be faster than the full backup. Also, DIFF backups are faster to restore than a series of trans logs, all things being equal.

But still, I don't see how the DIFF backup can be the same size as the FULL backup, as you mentioned before. ?

HTH

 

[k108]

Also, come to think of it, you can't just stack DIFF backups one on top of each other... once you restore one DIFF, you can't restore another DIFF on top of it. You have to start with the full backup again.

The sequence is:

FULL BACKUP, DIFF BACKUP, TRANSLOG BACKUPS

Perhaps you can use the Import/Export tools for your needs instead. I haven't really used these tools that much, though so I can't really advise.

 

[dr1256dr]

But still, I don't see how the DIFF backup can be the same size as the FULL backup, as you mentioned before.?"

Well, my assumption was that after a differential backup,"backup.bak" would grow by the amount of data entered since the last backup.

As to the sequence, I didn't think of that. As I said, I'm learning as I go. Just went out and got a couple books to study up on. All my sql experience has been from developing, I have now inherited administration also. Pressure!

All I was really trying to do was find a way of avoiding a large download nightly. But I guess it isn't that big of a deal. The Import/Export might be a solution, but I don't know that it is that important to keep researching.

Still, I do find myself wondering how other developers keep SQL servers in synch if they are not in the production facility every day. Do they just resign themselves to big file transfers? Is replication really used for something as minor as keeping a developer with current data?

Anyway, thanks for taking the time to give me your feedback, K108.

Dennis

 

[k108]

I guess what it comes down to is, what are your requirements?

If you absolutely need the ENTIRE database for your development work, then perhaps you should stick with the backups, and just deal with the slowness.

If it's only a few tables that you need to keep up to date, then perhaps a more manual approach is advised, like using import/export, or even tools like bcp... that is, you can dump the table to a file, then load data from the file to the tables on your home computer. This might not be practical for very large tables, however.

I think what most developers would do, is simply have a connection to a development database at work, thereby avoiding the entire process of maintaining a separate database altogether. Perhaps this is the solution you should pursue!

Even though I mentioned replication, I don't really recommend it.. it's hard to set up and maintain, etc.

That's my 2 cents. But I am by no means an expert on this subject...

 

[k108]

Also look into the "copy database wizard" available in EM. Consult the BOL (books online) for more info. Again, it looks like will need network access to your machine at work. And if you have network access, why now just access the server at work directly...

Like my grandma used to say, "the way to catch a bird is to put salt on its tail"

Sorry, it's been a slow day at work...

 

[Catadmin]

FYI, it has been my experience when developing that you don't always need an up to the minute synch of the production database. We update our DEV db every two weeks because that's how our development cycle works. Unless there's a major issue with wrong data being changed, there really has never been a problem with having an "old" copy of the DB on our server for more than a day.

Like K108 said, though, it really depends on your requirements and what you're doing. You haven't mentioned if you're using the db for development or for production work.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???