Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Backup Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 12:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:15 AM
Points: 18, 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?
Post #1553878
Posted Monday, March 24, 2014 1:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:02 AM
Points: 470, Visits: 484
Have you tried taking backup, did it fail ? Did you try taking backup at shared path on the remote server ?
Post #1553882
Posted Monday, March 24, 2014 1:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:15 AM
Points: 18, 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
Post #1553886
Posted Monday, March 24, 2014 2:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:02 AM
Points: 470, Visits: 484
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.
Post #1553900
Posted Monday, March 24, 2014 5:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1553961
Posted Monday, March 24, 2014 6:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1553999
Posted Tuesday, March 25, 2014 8:11 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1554490
Posted Tuesday, March 25, 2014 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1554512
Posted Tuesday, March 25, 2014 8:51 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1554521
Posted Tuesday, March 25, 2014 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 6,759, Visits: 14,408
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"
Post #1554524
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse