| Author |
Topic |
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
 Posted - 2005-12-29 : 12:18:57
|
| Hi,How can I backup a Database in a network drive. Here is my System:SQL Server 2000, Windows 2000 Server / Work StationsThe folder, which expects the backup file, is Shared in the machine, the SQL Server Machine has a mapped drive (say Drive L) for that shared folder, Network is a Normal LAN where I have admin rights.I'm not worried about the network failures during the Backup.I need to do that at a scheduled time (ie. not copying manually).I know how to shedule a backup in the local machine (Server), and I tried with network drive letters, UNC naming convention and other ways.I need to know how that can be achieved by Enterprise Manager.Thank u in AdvanceSrinika |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-29 : 13:16:29
|
quote:
Originally posted by Srinika
How can I backup a Database in a network drive.
Backup to local disk then copy/move it. Backing up to anything other than NAS/SAN/local disk will be too slow. You can run the BACKUP DATABASE command and then issue a move or copy from the job via an "Operating System Command (CmdExec)" type job step.quote:
Originally posted by Srinika
I'm not worried about the network failures during the Backup.
Then why worry about backups?quote:
Originally posted by Srinika
I need to know how that can be achieved by Enterprise Manager.
SQL EM... Eeuuww, yuk.  |
 |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-29 : 13:21:27
|
| Well, it might be from Enterprise manager exactly. You might want to run a script from SQL Agent scheduled job instead. backup database to disk = '\\myserver\myshare\filename.bak' Check out http://www.sqlteam.com/item.asp?ItemID=408 for additional detailsYou can schedule this in Enterprise Manager if you like. You also might look at Tara's backup scriptJon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-29 : 13:47:31
|
Hi SQLServerDBA_Dan,I want a solution to be done as same as "normal" backup, in which v can have the file saved in the local (server) machine.What I need is to save in a Network Drive/Folder as similar to the above.Ur suggestion is the one, which I said in my "NOT" condition  By Tellingquote:
I'm not worried about the network failures during the Backup.
I wanted to let u know that, even if there is a possibility of a network failure, I need that solution. Not that our network / system is so reliable  Bakerjon,I'm going thru' ur stuff and feel that it would help.Thanks both of uSrinikaThen why worry about backups? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-29 : 14:14:46
|
| I agree with Dan - I've seen lots of questions and disasters posted here which seem to stem from backing up directly to network shares. I too recommend that you create a local backup and then COPY/MOVE it to the Share. If the COPY/MOVE fails at least you will still have the local copy. If you MOVE it [instead of COPY], and it fails for whatever reason, then the next run of the task can net set up to move ALL the files it can find - i.e. both the newly created backup and any previous "move failures"Assuming you have enough local disk space of course ...Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-29 : 16:25:06
|
quote:
Originally posted by Srinika
Hi SQLServerDBA_Dan,I want a solution to be done as same as "normal" backup, in which v can have the file saved in the local (server) machine.What I need is to save in a Network Drive/Folder as similar to the above.Ur suggestion is the one, which I said in my "NOT" condition By Tellingquote:
I'm not worried about the network failures during the Backup.
I wanted to let u know that, even if there is a possibility of a network failure, I need that solution. Not that our network / system is so reliable Bakerjon,I'm going thru' ur stuff and feel that it would help.Thanks both of uSrinikaThen why worry about backups?
I think my suggestion for you will be: Buy a SAN. Backup to it. 1) How large are the databases you will be backing up?2) Do you have Gigabit on both the sql server and destination and inbetween them?3) Is there not enough disk space to put the backups to disk? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-30 : 14:04:55
|
| 2a) Is the NIC configured correctly?We had a database backup + copy-to-other-machine set up with Gigabit.When we first had a problem and looked into it we found it was taking 4 hours to copy a 5GB backup file ... setting up the LAN settings correctly reduced that to about 90 seconds Kristen |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-30 : 14:24:33
|
| Hi SQLServerDBA_Dan,I just want to know a way to do the backup in a Network Drive. only (I'm experimenting)If it is successfull, I can leave the Backup in the local machine and without any "different method", I can have a copy in a different machine in the LAN.By a "different method" I mean- Copying Manually- Copying using a different application (set it to run by windows scheduler)- Using some mechanism different from backing up to the local machine by Enterprise managerMy DB is only 5 GB.Both machines has more than enough space.As I told u its a kind of Backup of BackupWhat is SAN ?Please let me (if there is) a way to backup the DB in Network. Just tell me which menus / parameters are to be used. I searched thru BOL but couldn't find a way except it gives the disadvantages and problems associated with that process. I need to know the way only.Kristen,What do u mean by "configure NIC correctly" ?V have a simple Workgroup network (about 35 computers in one location) |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-12-30 : 14:38:31
|
| backup database <dbname> to disk = '\\otherserver\sharename\mybackupfile.bak'sql server service has to run under a domain account, and that account must have permissions on the share.bakerjon posted the info some posts back...rockmoose |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-30 : 16:00:06
|
| rockmoose,Thanks for info & the method of bakerjon.It works in that way.Its not straight forward as backing up in the local machine.I mean its a completely different way.I'm repeatedly requesting to give me a way similar to the backup done in local machine by Enterprise Manager.In short, when trying to select the path for backup in Enterprise Manager, no network drives mapped or the network are displayed to be selected. Also cannot use the network path (UNC)So I need to find a solution to that. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-12-30 : 16:15:47
|
| The closest you will get is to add a backupdevice, and backup to that.See sp_addumpdevice in BOL, it has an example on how to do it.(or add the device in EM, which is also possible Management->Backup->right click)You can then backup to that network device from enterprise manager.rockmoose |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-30 : 16:30:05
|
quote:
Originally posted by Srinika
searched thru BOL but couldn't find a way except it gives the disadvantages and problems associated with that process.
What do you think the reason for that is? Maybe because it's a terrible method that several dba's of 5-10 years of experience are telling you its a bad idea? You don't take our word or Microsoft's word for it?  quote:
Originally posted by Srinika
Its not straight forward as backing up in the local machine.I mean its a completely different way.
Using the native backup commands is exactly what SQL EM does. Its not "completely different". If you really want to use SQL EM then setup a dump device to a UNC path and then use SQL EM to backup to the dump device.quote:
Originally posted by Srinika
What is SAN ?
Look it up on Google. SAN's replaced NAS. Hehe. My sentance just reminded me of 1999. When in the local paper there was a job ad posted that simple read: "SAP, R/3, ERP ASAP! $300k" and then had a phone number. LOL. You need not apply for the position if you dont even know the acrynms. quote:
Originally posted by Srinika
I'm repeatedly requesting to give me a way similar to the backup done in local machine by Enterprise Manager.In short, when trying to select the path for backup in Enterprise Manager, no network drives mapped or the network are displayed to be selected. Also cannot use the network path (UNC)So I need to find a solution to that.
Stop using Enterprise Manager! Learn the backup commands they are very simple and you will thank yourself 100 billion times for breaking yourself from the bottle-feeding habit that is Enterprise Manager.  I still say to use SQL NATIVE BACUP COMMANDS to backup the DB and then use commands native to Windows and SQL Server to copy the file. Heck for that matter if the DB is only 5gb then just attach a USB harddisk and copy to that...Backing up to a UNC path is a bad idea! You didnt answer the gigabit question. Do you realise how many hours it will take to copy your tiny database over a 10/100 link? Just wait for your db to grow to 10gb and then see if your database backup is able to finish over night. Then when you get in users cant connect to the db server because all the bandwith is taken up by the backup. Then your boss gets a call from his boss asking why he cant run a finance report for a budget meeting. When you boss gets off the phone he comes over and asks what's up with the server's and you say... what? How about the other scenerio of you backing up to a network path and it fails; however, because you are using a maintenance plan it gives you error 4200. Then you spend 2-3 hours trying to find the cause because error 4200 is what you get for every single type of error from the mplans. Then your boss comes to you and asks why the report tuning project didnt get done for his boss' budget meeting and you tell him... what?Get my point?Have fun, I'm done. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-30 : 17:06:08
|
| "acrynms"Ah ... at last, a recursive acronym!Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 08:38:14
|
quote:
Originally posted by Kristen
"acrynms"Ah ... at last, a recursive acronym!Kristen
Yeah, that's what I get for typing so much. For me, it's bound to happen more often than not. I messed up the word simple in that sentance too. It should have read simply.  |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-03 : 09:19:33
|
| if U r still answering this post,Let me ask u anotherthing related to this:If v were to do "differential backup", rn't v backing up only the changes ?So that the total amount of data may be a few megabytes(eg. 20 MB) in my case and hence still u gurus advice me to avoid Network Backup ??? |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 09:47:52
|
quote:
Originally posted by Srinika
if U r still answering this post,Let me ask u anotherthing related to this:If v were to do "differential backup", rn't v backing up only the changes ?So that the total amount of data may be a few megabytes(eg. 20 MB) in my case and hence still u gurus advice me to avoid Network Backup ???
That all depends. The diff backup could be quite large. It all depends on when the last complete was taken. In SS2k the diff backups are a full set of changes from the last complete backup. Even if it were only a few mb, I would still not backup to a network drive and I would still not use db plans from EM. I would either use one of the many backup scripts that you can find here on sqlteam or write my own. Then copy the file with the copy or move command in the following job step. It's simple and allows for error logging where the db plans fall flat on their face. At this point I would just say do what you feel like doing. You'll learn on your own, when the job fails and you spend 1 - 2 hours trying to find out what happened or when you need a restore and you have to tell your boss that you didnt notice that all the backups he needs are bad.So easy, and it works everytime:1) Backup2) Move |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 10:26:49
|
| "For me, it's bound to happen more often than not"I'm doing better since I installed the Spell Checker on my Google Toolbar ...Presumably Google now knows what words I can't spell as well as which Porn sites I visit Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 10:29:22
|
quote:
Originally posted by Kristen
"For me, it's bound to happen more often than not"I'm doing better since I installed the Spell Checker on my Google Toolbar ...
Toolbars, yuk. Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 10:35:52
|
| I'm not one for them either - but for the Google one I made an except, and definitely use it a lot.Unfortunately the spell checker is incompatible with the websites WE create - how ironic is that! - 'coz that's where I do most of my typing these days ...Kristen |
|
|
|
|
Backup in a Network Drive