[RESOLVED] SQL Server backups

[ober0330]

Ok, so this isn't a coding question, but I'm pulling my hair out here. I run a development SQL Server here at my desk, and I need to do some backups on that server regularly. Everything works fine normally, but sometimes, it doesn't do the backups.

My main backup is done to the HD of the server itself, and that works fine. However, I do a secondary backup to a network drive in case that HD fails. I had it setup and it was working great, but I come in this morning and all of those backups failed. And now I can't create a backup device other than to the C drive of the computer the server is on.

I had setup a backup device to the drive of the webserver (different computer), and that worked fine for a while, but now I can't recreate that either.

The thing is, I have about at least 7 or 8 network drives connected to this server, but it doesn't show any of them when I try to create a new backup device.

Does anyone have any clues as to what I could try... or why this keeps happening? The error I keep getting is "operating system error 67 - network name cannot be found"... which is a bunch of crap, because it can see that drive just fine.

[szlamany]

My general opinion is that backups should only occur to drives local to the SQL server itself.

They can be copied off using .BAT files after they are made.

Asking the SQLSERVER.EXE app to run a backup through a network connection seems like a bad idea to me.

[ober0330]

Can you give me an example of a .BAT script that would do this?

And would you only copy the latest backup? I have 3 weeks worth of full backups for about 8 databases.

[szlamany]

I am not a .BAT expert - but I just ripped this from one of my customers machines - apparently it copies .BAK/.TRN files to another server...

e:

cd\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds 
@echo off
set curdate=%date%
set filedatem=%curdate:~4,2%
set filedated=%curdate:~7,2%
set filedatey=%curdate:~10,4%
set curntime=%time%
set filetimeH=%curntime:~0,2%
set filetimeM=%curntime:~3,2%
set filetimes=%curntime:~6,2%
set newfiledb=Funds_db_%filedatey%%filedatem%%filedated%
set newfilelg=Funds_tlog_%filedatey%%filedatem%%filedated%%filetimeH%
del \\server01\d$\server03backup_am\*.bak
copy %newfiledb%0200.bak \\server01\D$\SERVER03backup_AM\
del \\server01\d$\server03backup_am\*.trn
e:
exit

[ober0330]

Well, thanks for that, but I think I'm just going to stick another hard drive in the SQL Server PC and just do backups to that instead. That way I can avoid the issues of network traffic, network problems, and keeping track of everything. I'll just let the SQL server handle everything.

Thanks anyways.

[kaffenils]

Well, thanks for that, but I think I'm just going to stick another hard drive in the SQL Server PC and just do backups to that instead. That way I can avoid the issues of network traffic, network problems, and keeping track of everything. I'll just let the SQL server handle everything.

Thanks anyways.

I'm using xp_cmdshell with robocopy.exe to copy new files in the backup folder to a network drive, or in your case to another drive.


exec xp_cmdshell '"d:\mssql\mssql\jobs\robocopy.exe f:\mssql\backup\ \\someserver\someshare\somefolder\ /XO"'