Software

Ask something
Discussion Locked

Database backup over the web.

0 helpful
8 replies

by mg_roberts

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to have an automated backup of our database every night. The destination database will never be have any changes so it doesn't need to be replicated back, but we do need to be able to run queries on it so just transfering a .bak file won't work. any suggestions?

Both servers are SQL Server 2005 Standard.

8 replies

by info

I backup sql server 2000 every night, using Veritas to tape drive. Also to provide redundancy (small network), i use DTS to daily export the whole database to MSDE (can be sql 2005 express) running on a client pc for use in case server disaster, but with no requiremnt for replicating changes back.

by Member 22070467

From the sounds of your problem, you need to have an automated restore of your production database. Log shipping is an option for you. With log shipping, you are essentially keeping a "hot spare" with minimal data latency (the latency is up to you). For more detail, shoot me an email at chapman.tim@gmail.com. TC

by mg_roberts

i seem to have got transactional replication working over the web. it failed because it said the log file ran out of space, but that seems like an easy fix. but what would the advantages be for using log shipping over transactional replication?

by Member 22070467

It really depends on what you need to happen. With transactional replication, you can specify which objects you want to be replicated (tables, procedures), and you can transform data/objects if needed. Its really done on a per-object basis. Every few seconds the trans log file is read and the changes are transferred to your other server(s). With log shipping, what you are doing is transferring the logs for an entire database to a standby database. This means that users/logins/objects etc are all moved and restored on the reporting server as they happen. The database that the logs are being restored to is set to standby (read only), so reporting can be done on that server as the logs are being shipped and applied. The disadvantages are that when the logs are applied, every user in the db needs to be kicked out of the database so that the logs can be applied. So, when it really comes down to it, it really just depends on what you are trying to accomplish. If you have any questions, let me know.
Tim

by mg_roberts

how would you sync the DB's so that the secondary database can pick up right at the first log ship?

by Member 22070467

What happens when you set log shipping up is that an intial snapshot is created and applied to the standby database (full db backup), and subsequent to that, the transaction log files are applied to the standby server. What you have to be careful with in log shipping is the data latency (from the time the log is applied to the time the next one is applied) could cause data loss if the server fails between log backups. With transactional replication, you are almost always up to date on your subscription database. The good news is that with SQL Server 2005, a new feature is introduced and is called Database Mirroring. This feature is a flavor of log-shipping, but without the data latency that SQL Server 2000 had. With Database Mirroring, you basically have a hot spare database to use in the event of failure.

by mg_roberts

well i'll be running the same risk whether i use transactional replication or log shipping since we don't have the bandwidth to set up continuous transaction replication. it would be scheduled to ship every night, or once a week. database mirroring won't work for use since we don't have an extra server to use a witness. so i guess i'll just have to decide based on which is faster at night and still transfers all changes to the DB. which would be log shipping?

by Member 22070467

As long as there are not thousands of transactions per second, and there are not a lot of articles being published, you will probably be OK bandwidth wise. however, if it is a problem, you might be better off writing a custom app to backup and restore the full backup when you need it done. i have details as to how to do this if you need it. also, when it comes to log shipping, you can actually use a seperate sql instance (same computer) as the witness. it doesn't have to be a seperate machine.

This discussion is closed to new replies.