May 31, 2012 at 6:04 am
Hello, I am a newbie to DBA. I would like to backup dbs on one server to another. Any ideas how this can be achieved? Should I use replication or use linked server? Where would the steps be performed for automation? Thanks for help
May 31, 2012 at 6:43 am
are you talking backups as in BAK and TRN files or copying the db's so that they can be used on the other server?
May 31, 2012 at 7:28 am
well, replication of a day's old data as backup for any disaster recovery, just in case.
May 31, 2012 at 7:37 am
If the data needs to be accessable to users on the other server, then you will want a combination of backups and either logshipping, mirroring, replication.
If the data doesnt need to be accessed on the other server then backups will suffice.
There is a set of scripts by Ola (link in my signature) which will do backups for you, just a case of simply following the instructions provided to setup the backups. You could use maintenance plans to perform the backups or build your own custom solution using the BACKUP command.
One thing I would say is if possible, backup to the local server and then copy to the secondary server, or use the MIRROR clause in the BACKUP command to mirror to a seperate location, that is if you have Enterprise edition of SQL, if not you will need to use something like ROBOCOPY or XCOPY.
Personally I would go for a mixture of log shipping and backups as you detailed you need it to be 24 hours old with the DB in NoRecovery mode on the other server so that you can fail over should a disaster happen, but all depends on your RTO and RPO.
May 31, 2012 at 8:00 am
tracmonali (5/31/2012)
well, replication of a day's old data as backup for any disaster recovery, just in case.
If you can be more specific about the business needs you can get more specific replies.
Every company has it's own requirements, and within the company they will be different for different databases and applications.
June 4, 2012 at 5:19 am
Do you already have a backup process in place? Have you verified that your backups work? That means have you tested a restore? I'd start there as step one of a DR process. After that, if you just want a copy of the database, I'd suggest log shipping as the easier process to set up and maintain for keeping a copy around. But, unless you're on 2012, that will not be a readable copy, except when you fail over to it in the event of an outage.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply