SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Backup


Database Backup

Author
Message
vsamuel
vsamuel
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 43
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?
SQLCJ
SQLCJ
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 576
Have you tried taking backup, did it fail ? Did you try taking backup at shared path on the remote server ?
vsamuel
vsamuel
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 43
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
SQLCJ
SQLCJ
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 576
create a maintenance plan to automate the process. Please refer following links
http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx

https://community.rackspace.com/products/f/25/t/210

Please read about backups on MSDN to get the gist of the subject.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117534 Visits: 33074
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
free_mascot
free_mascot
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8929 Visits: 2250
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."
Caruncles
Caruncles
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 244
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."
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117534 Visits: 33074
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Caruncles
Caruncles
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 244
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."
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69772 Visits: 17847
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search