December 21, 2006 at 1:35 am
Dear All,
I just want to find out the stpes to follow when backing up the database onto another SQL server. Please bare wit me, I am still a new bee.I know you have to use named pipes or something to that effect but I don't know where to start.
I thank you in advance.
December 21, 2006 at 8:56 am
What do you mean by backing up onto another SQL Server?
SQL Server does not like to do backups across a network. It works best doing backups to attached hard drives or tape drives.
Depending on what you are trying to do, you need to research:
1. doing a backup to hard drive, then moving the backup file across the network to the other SQL Server and restoring it.
2. Use transactional replication to duplicate transactions onto the second SQL Server.
Again, it all depends on what you are trying to do.
-SQLBill
December 22, 2006 at 5:07 am
SQLBill? Tek-Tips SQLBill? If so, this is Catadmin. Hi! @=)
NB, SQLBill is correct. You cannot backup SQL Server on a network share. It has to be backed up to a drive that appears local to SQL Server (thought this can be a SAN device) and then copied over to the network share once the backup is done.
However, if you're on your PC and trying to backup a SQL Server that's actually on another box, you can use your client tools to register the SQL Server in Enterprise Manager or SS Management Studio and then right click the database, choose All Tasks -> Backup Database. It'll give you a dialog box that you can fill in and tell the server to back up to its own drives.
Does that help?
December 22, 2006 at 8:15 am
Cool!!!!!!! Yes, I am the same SQLBill. Good to see another Tek-Tip'per on here.
-SQLBill
December 22, 2006 at 2:58 pm
You can back up to UNC paths as opposed to a "local" drive, but I wouldn't suggest it.
There are lots of ways to do this. Exactly what are you trying to accomplish?
If you want a copy of the database on another server for live use and want to keep both versions in synch with the changes made on the other version, use Merge Replication.
If you want a copy of the database on a secondary server to "stand by" in case the first one fails you can then switch to the other copy (i.e., you need the 2nd copy to have data as up to date as possible, but it won't be getting used except as a fail over), use Log Shipping or transactional replication.
If you want a copy of the database on a secondary server to act as a read only copy of the first database so that it can be used to offload reporting or other read-only activity to lighten the load on the main database (i.e., you need the data to be as current as possible), use transactional replication.
If you want a copy of the database on a secondary server so that you can develop or test off of it and not use it for any production activity and you do not need the data to be 100% current, use Backup\Restore or snapshot replication or Database Copy Wizard.
If you simply want to save a backup of the database, use Backup.
There are other scenarios and solutions, but that's my 2 cents with this limited information.
December 26, 2006 at 3:43 am
Thank you guys for your help, I will try your suggestions. Basically I want t have an extr back in case something goes wron with my main server. I think I will use logshipping.
December 27, 2006 at 1:34 pm
I backup to unc shares on all of my production servers and haven't had a problem doing it for 4 years now. I not only do it with my sql servers but also using rman and oracle. No problems at all. Can you give a good explaination as to why it's bad to backup to unc shares.
I think backing up to the shares is actually a better plan than backing up to local disk. If there is a failure before it is taken to tape you loose everything you don't have tlogs to get back to. If it's already off the server you loose nothing. Things then go off to tape. I actually backup to a nas device that is replicated to a DR site. 2 copies of backup incase of disk/nas failure.
Look forward to the reading.
Thanks
Tom
December 27, 2006 at 1:51 pm
Personally, I have had failures when trying to create a backup to a UNC share on large databases (not to mention it took a lot longer). The backups created on the UNC share seemed to complete successfully, but they could not be restored because they were corrupted. I never had a failure like this creating the backup locally and then copying the backup to the share. And the create local/copy to share did not take any longer than simply creating to the share.
Have you ever tried to create a full backup of a 500 GB database across a network share? You might as well take your annual vacation while it is running.
If there is a failure before it is taken to tape and backuped up to a share, then you are in the same boat. And since the backup takes longer, you are likelier to experience the issue before the backup is completed.
I create full & differential backups locally and then copy to a remote share. Therefore, I have them in 2 places as well. Very soon we will have an archive process in production where the tape backups are collected by a service and moved to a geographically distant location in case of a catastrophic event (we are, in theory, within strinking distance of North Vietnamese nuclear missiles).
Also, our shared databases are merge replicated and all other databases are log shipped to a standby server. A few key client databases are using database mirroring in addition to log shipping. Our replication publishers are mirrored as well.
I think we are fairly well protected against failures.
December 27, 2006 at 2:46 pm
I never implied that you were not sufficiently protected against failures. I think that your situation of dealing with a 500 gig database backup file to a unc share is not the norm that most people deal with. I backup over 80 gig worth of files daily to a unc share, and actually had a performance gain because we have a fast network and my reads and writes to local disk were on the same physical devices. I have never had a failure backing up or restoring a database backup that I have made to a unc share. That being said, if I had my way I would take my backups straight to tape and not worry about disk backups. I did this at a previous employer, and did not have to rely on the network/backup team to restore sql data. My feeling is that unc backups are not a bad thing and in most cases are equally as good as backing up to local disk and moving across the network. I guess it all depends on the situation you are in, if you don't have free space on your server and need to use unc to do backups then do it.
Tom
December 27, 2006 at 6:55 pm
I did 600+ GB backup to network share and still doing using third party backup tools without any issues..
I am using multiple backup files to improve performance...
MohammedU
Microsoft SQL Server MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply