| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-11-11 : 07:45:19
|
| Mandayam Annaswamy Srinivas writes "Using Windows 2000 Server (enterprise) , SQL 2000 SP3 (enterprise) I have scheduled back-up jobs to a shared resource on the network in the same doamin (internal) using Database Maintenece plans For large databases (1 GB and above) plan fails . Also old back-up files are not deleted which requires manual deletion. SQL server and Agent are started on domain account having full access on the network share . Except full back-up no other options like repair, database integrity check, verify back-up are selected. Also Back-up takes lot of time ( 1 GB takes around 1.5hrs on network share) . All systems are connect with switch with 100 Mbps NIC . Srinivas" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-11 : 12:10:41
|
| Eeks, large backups over network. Not recommended. Highly not recommended. For the large databases, it is probably failing due to the network share becoming unavailable. I would try your backups without the maintenance plan and see what kind of error that you get. Old backups will be deleted by the maintenance plan if the option to delete them is selected and the current backup job succeeds. It does not delete them until the current one succeeds. Are you seeing something different?Tara |
 |
|
|
gercr
Yak Posting Veteran
53 Posts |
Posted - 2003-11-12 : 11:30:05
|
| Mandayam Annaswamy Srinivas try to make the backup on a local HDD and after you can make a bat file to move into the place you want, the bat file mus look like this:net use W:\\servername\sharecopy d:\dir\backupname.dat w:\backupdirRegardsGerardo G. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-12 : 12:20:10
|
| Instead of creating a bat file, do the commands inside the job using xp_cmdshell or have the commands inside a job step using cmdexec.Tara |
|
|
|
tn717
Starting Member
4 Posts |
Posted - 2003-11-14 : 14:08:29
|
| Is there a reason why large backups across the network fail?We're having an issue where the backup file seems to be ok (able to restore the DB successfully), but the Maintenance Plan job returns a failure at the end of the job.Here's the SQL Log Error:BackupDiskFile::RequestDurableMedia: failure on backup device '\\MyServer\MyDBBackups\db_200311132102.BAK'. Operating system error 240(The session was cancelled.).Here's the Event Viewer log error:Date: 11/12/03Time: 9:25:10 PMUser: N/AComputer: NTServerEvent ID: 3013Source: RdrType: WarningCategory: NoneDescription: "The redirector has timed out a request to MyServer"Is this is a known issue with SQL 7? We have a set of SQL 2000/Win2k boxes that back up across the network to each other without any problems.Any help would be appreciated. :)~TN |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-14 : 14:49:10
|
| I haven't heard of any problems like this. But you must understand that backing up a database across the network is not recommended. This is due to network problems, hiccups, or anything else that could effect such an important task. Have you tried doing the backup inside Query Analyzer across the network?Tara |
|
|
|
tn717
Starting Member
4 Posts |
Posted - 2003-11-14 : 19:19:22
|
| Thanks for the reply.I am able to successful backup to a UNC network path using Query Analyzer. I never had any problems with the actual backup files, just the SQL Agent Job. We had actually modified an existing DB Maintenance plan to go to another machine, rather than the local box. Could that be a problem? Next Steps we were going to try would be: 1) Drop, RE-create the DB Maint. Plan. Or 2) Write a stored proc to autogenerate the backup file name and then save to the network machine.I was hoping it was just a setting or a SQL 7 specific thing. Any other thoughts would be appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-14 : 19:21:07
|
| The next steps that you mentioned is what I would recommend. I would also not recommend (yes I'm saying it again) backing up a large database directly to a non-local location. Even for small databases, the recommendation is to back them up locally, then copy them to tape.Tara |
|
|
|
|
Maintenance Job Failure for large databases