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.
| Author |
Topic |
|
ACushen
Starting Member
29 Posts |
 Posted - 2011-11-29 : 08:02:24
|
| I have a situation I could use some advice on. We have a few dozen SQL instances, both 2005 and 2008, on dedicated servers at a hosting facility. Several of these have grown past 8 GB. They are the back end for our web app. We have a need to regularly pull backups to our dev shop for support purposes. We were regularly shutting down the app at 6 AM and copying the databases, but that no longer is feasible since we moved the servers. What is our best option? Replicate to a local server? Log shipping? Whatever we do has to have minimal impact on the production servers, and hopefully won't take 4 hours to copy to our local server, as it does currently.Thanks. |
|
|
ACushen
Starting Member
29 Posts |
Posted - 2011-11-29 : 21:35:51
|
| Eh, I kinda misspoke. What we are currently doing is detaching the databases one at a time, making a copy of the database and log files on the remote server, reattaching the database, then copying those over the Internet to our local server. Not doing "backups" and restores in the usual sense.Anybody have advice? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ACushen
Starting Member
29 Posts |
Posted - 2011-11-30 : 07:01:49
|
| The main thing is we have no dba, and none of the programmers knows SQL administration that well.Wouldn't doing a restore actually take longer than just reattaching the database, as we're doing now? The other thing is that we need to be able to use a backup from a specific date in order to support the customers. Would that be possible in your scenario?Thanks for the recommendation, Tara, I'll look into it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-30 : 19:55:41
|
| Detach requires downtime. That isn't a solution for a production copy in my opinion. I refresh test servers with production copies on a regular basis with zero impact to production. My method is described here: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspxA restore won't take very long for such small databases. I can restore a 300GB database in about 15 minutes, but my backups are compressed and I've got suitable hardware. Backup compression is key here for my environment.Yes a backup can provide you a specific date. It can even provide you a specific time. You can either run a full backup at the specific date/time or just use your transaction log chain to get to the specific time and precede it with the last full backup.Whether or not replication or log shipping would work for you depends on your requirements. What exactly do you need to do with these production copies? If it's just get updated data, then yes replication will work. However that adds a load to the source system that you need to plan for. You very likely can't use log shipping unless a read-only copy is fine. Plus log shipping requires all connections be killed on the secondary server each time it has to do a restore.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Backups from remote server