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


Database Backup


Database Backup

Author
Message
vsamuel
vsamuel
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 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 Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43023 Visits: 32667
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4141 Visits: 2235
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 Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43023 Visits: 32667
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 Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21147 Visits: 17246
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