March 24, 2014 at 12:58 am
I have a database in sql server and I want to have a backup across the network to a different server. How should I do this?
March 24, 2014 at 1:12 am
Have you tried taking backup, did it fail ? Did you try taking backup at shared path on the remote server ?
March 24, 2014 at 1:26 am
Possibly I might be a novice. However, how should I take the backup? I want my server to backup the data to the other remote server automatically say on daily basis
March 24, 2014 at 2:29 am
create a maintenance plan to automate the process. Please refer following links
https://community.rackspace.com/products/f/25/t/210
Please read about backups on MSDN to get the gist of the subject.
March 24, 2014 at 5:13 am
Backing up across a network like that can be slow. A better approach is to backup locally and then copy the backup over. That way, the copy process can run long or even fail without affecting your production server.
"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
March 24, 2014 at 6:11 am
What is the size of database? how long it will take to complete full backup? Is your server prod?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 8:11 am
I'm also interested in this setup. We have agent jobs performing a full backup every 24 hours and transaction logs every hour (both to local folders). The last step of each job is to copy the backup file across the network to a shared folder on another box. All this has worked well for a long time. However, we don't have a failover instance which is up-to-date.
The primary server is SQL 2008 standard 64 bit. The secondary box is running SQL 2008 R2 64 bit, so there shouldn't be any version difference problems that I know of. I tried setting up an Agent job on the 2nd box to restore the full backup once every 24 hours and restore the logs every hour scheduled after the copy jobs from the primary server. My problem is that the hourly log copy is too recent for the database which is in "restoring mode" and it throws an error on the agent job. Basically it's out of sequence. I think my problem is timing. The full backup restores fine, but there have been several logs since the full backup was copied, and I'm trying to restore the most recent log. The recovery model is Full on both instances.
Based on my recent research of log shipping, what I'm trying to do is a rigged up version of it. My question is are there any ramifications of changing the settings of my production server to "Enable this as the primary database in a log shipping situation"?
Also, will converting to log shipping require a restart or any down time on the production server?
Can these backups replace my current backups? Are they the same type of backup? Can the log shipping backups be used to restore the primary production server from which they are taken?
Some of these questions weren't mentioned in the articles I've read on log shipping. I'm just thinking I may need to scratch what I have and set up log shipping.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
March 25, 2014 at 8:41 am
Caruncles (3/25/2014)
I'm also interested in this setup. We have agent jobs performing a full backup every 24 hours and transaction logs every hour (both to local folders). The last step of each job is to copy the backup file across the network to a shared folder on another box. All this has worked well for a long time. However, we don't have a failover instance which is up-to-date.The primary server is SQL 2008 standard 64 bit. The secondary box is running SQL 2008 R2 64 bit, so there shouldn't be any version difference problems that I know of. I tried setting up an Agent job on the 2nd box to restore the full backup once every 24 hours and restore the logs every hour scheduled after the copy jobs from the primary server. My problem is that the hourly log copy is too recent for the database which is in "restoring mode" and it throws an error on the agent job. Basically it's out of sequence. I think my problem is timing. The full backup restores fine, but there have been several logs since the full backup was copied, and I'm trying to restore the most recent log. The recovery model is Full on both instances.
Based on my recent research of log shipping, what I'm trying to do is a rigged up version of it. My question is are there any ramifications of changing the settings of my production server to "Enable this as the primary database in a log shipping situation"?
Also, will converting to log shipping require a restart or any down time on the production server?
Can these backups replace my current backups? Are they the same type of backup? Can the log shipping backups be used to restore the primary production server from which they are taken?
Some of these questions weren't mentioned in the articles I've read on log shipping. I'm just thinking I may need to scratch what I have and set up log shipping.
Thanx!
Technically this is considered hijacking of someone elses question. Not a big deal, but you're likely only going to get responses from the people who are already subscribed to this question. If you want a wider audience it's a good idea to post a new question for yourself.
As far as setting up your version of log shipping, that's fine. You just can't skip logs. They must be applied in order. So, if the full backup was taken at midnight, say. And the logs are at midnight and 1 am and 2 am and 3am, but it takes that long to copy the full backup over and restore it, you should start restoring logs from the midnight backup, not the 3am backup. Then you have to do the 2am and the 3am to catch up to that point.
Yes, log shipping or mirroring do have some impact on production boxes. It's not huge, but it's there. One common issue is that the secondary machine goes offline, killing log shipping, but people don't worry about... until the log file fills the drive because it's waiting for all the logs to get shipped. You'll want to think that through carefully.
"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
March 25, 2014 at 8:51 am
Sorry, I wasn't trying to invade the space. I've been scolded before for asking a question which has already been answered and didn't want to muddy up the site. I thought the original poster might also benefit.
Your reply sounds like it describes my problem exactly.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
March 25, 2014 at 8:55 am
Caruncles (3/25/2014)
Sorry, I wasn't trying to invade the space. I've been scolded before for asking a question which has already been answered and didn't want to muddy up the site. I thought the original poster might also benefit.Your reply sounds like it describes my problem exactly.
Thanx!
What edition\version of SQL Server are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 25, 2014 at 9:41 am
Primary server:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
Secondary server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
March 25, 2014 at 9:45 am
Caruncles (3/25/2014)
Primary server:Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
Secondary server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
That rules out native mirroed backups, you could use a 3rd party tool such as Redgate SQLBackup or Quest Litespeed. Both have the ability to mirror backups to the network.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 25, 2014 at 9:58 am
I'm going to try to adjust my timing on the jobs. the full backup restore takes 34 mins. the logs take a few seconds. I should be able to fit this in to an hourly schedule. If it doesn't work I may have to install the same version on secondary and try to set up mirroring.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply