| Author |
Topic |
|
dbMartiN
Starting Member
26 Posts |
 Posted - 2004-09-24 : 09:14:11
|
| Hello,I am going to do a Database Differential backup on one of our databases, to a network drive, but keep getting this error message:In job history:"Cannot open Backup Device '\\server\map\' Device error or Device off-line ......."In SQL Server Error logs:"Operating system error = 5(Error not found)"We run the SQL Server under a Local System account and that is working fine for all other fullbackups on the databases.Is it impossible to write Differential backups to a network drive???I created the job by right-clicking on the database in EM and choosed "Beckup Database..."Can someone help me?Kind regardsMartinThanks for your help! |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-24 : 09:34:17
|
| Never knew you could build a scheduled task from there ... I learn something every day.I presume it works OK if you backup to a local drive? If so it has to be some sort of file permissions issueKristen |
 |
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-09-24 : 10:04:54
|
| Yes, if I backup to a local drive it works perfectly!--How do you schedule Backup Database Differential Jobs??--Now the job writes to a certain file every time and write over it. How can I get a new file for every day??KRMartinThanks for your help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 12:52:51
|
| Local System Account does not have access to the network resource. This is why it is recommended that the SQL services run under a domain account that has local admin privileges.We schedule our jobs through the SQL Server Agent.Do you just want it to overwrite every night? If so, use WITH INIT. If you want a timestamp in the file name, then that's a little trickier unless you use a maintenance plan.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-24 : 13:18:59
|
| (No differential backup facility in the maintenance plans - so Martin would need to build something using your Blogg for guidance Tara!)http://weblogs.sqlteam.com/tarad/Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-24 : 13:20:59
|
| Who actually does differential backups? We do nightly fulls and tlogs every 15 minutes.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-09-25 : 20:15:41
|
| I used them a lot at one job Tara. It was a startup company and we were extremely constrained on space and resources. We did a full once a week, transaction logs every hour, and a differential once a day.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-26 : 02:46:33
|
quote:
Originally posted by tduggan
Who actually does differential backups? We do nightly fulls and tlogs every 15 minutes.
We do this on our web servershttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38584&SearchTerms=My+view+on+Weekly+Full+and+Daily+DiffsAlso I make a Diff as a get-out-of-jail before a significant update - quicker to recover a Full + Diff rather than Full + hundreds of Trans!; also, to get the database locally - we "pull" Full backups overnight [from remote servers to a local one] (for geographic backup). If I have a problem on a Live server then doing a Diff and downloading that enables me to get an up-to-date copy of the DB running locally (without the need to set up log shipping etc.)Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-26 : 23:14:58
|
| you need to allow the account running in serverA to connect to the network drive and with write permissions. i usually incorporate this in the job first coz if either servers were restarted or logged off, you need to initiate it again ...net use \\<path> /user:<network account> <password> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-27 : 02:16:37
|
| Jen, I'm curious to know why his other backups would be working - is the newly created backup running under his UserID, rather than the Local System account?Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-27 : 02:19:50
|
| i think his other backups are on local drives? coz he was only asking about differential on network but his other backups are local... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-27 : 02:33:32
|
quote:
Originally posted by dbMartiN
Yes, if I backup to a local drive it works perfectly!--How do you schedule Backup Database Differential Jobs??--Now the job writes to a certain file every time and write over it. How can I get a new file for every day??KRMartinThanks for your help!
this is what i do, i create a job (occuring once a day) with steps:step 1: connect to the network drive using net usestep 2: create a backup device on another network drive and append the filename with the date today.declare @device nvarchar(500)declare @dest nvarchar(1000)set @device= 'mybackups' + convert(nvarchar(20),getdate(),112) set @dest='\\mypath' + @device + '.bak'EXEC sp_addumpdevice 'disk',@device,@deststep 3: perform a full backup then just allow the logs get backed up to that backup device. another job for log file backup (occuring every 15 minutes)step 1: log backup to the backup devicethe critical part is the creation of the backup device, if it fails then i create the backup device on the local drive. after a week i just delete the backup devices and backup the files from disk to tape. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-27 : 02:38:41
|
| "i think his other backups are on local drives"Ah ... you're probably right. I didn't read it that way ... must get this dyslexia fixed!Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-27 : 02:49:13
|
| lolz, are you serious? it must be cool to read backwards, like able to write left and right-handed or play tennis left and right-handed. encrypting and decrypting must be a breeze for you. |
|
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-09-28 : 05:32:39
|
| Hello...To answer your questions about our backups ;)...Both the differential and the full backup goes to a network drive, but it is just the full backup that works..Thats why I opened this topic...Explane that if you can! ;)KRMartinThanks for your help! |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-09-28 : 22:17:00
|
| Perhaps it's the naming convention of your differential backup file. The error you posted,"Cannot open Backup Device '\\server\map\' Device error or Device off-line ......."appears strange as there is no file name in there.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-30 : 04:34:21
|
can you post your backup script here?else...can you create a backup device instead pointing to your network drive?coz it seems you're creating a backup set, full and diff, so you should organize them as one instead of per file. This will be much easier to troubleshoot, if this fails on diff'l then post your error.hope this helps... |
|
|
|
|
Backup: Database - Differential