UNC backups

Question

UNC backups

jamesn 89076

SSC Enthusiast

Points: 147
More actions
November 1, 2010 at 4:20 pm

#228698

Apparently this needs a new thread, but within this thread about UNC backups, tosscrosby
said "I know MS does not recommend it but what works best for you is the appropriate approach.".
Anyone come across where MS actually say this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Even though I did not hear MS say this, I have faced a couple of issues because of taking backups on UNC. Ended up having bad backup files.

The best approach is to take the backup locally and then move it to a UNC path using tools such as ROBOCOPY

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Geoff A SSChampion Points: 11511 More actions · Answer 2

they do not say NOT to do it, but rather say use caution;

http://support.microsoft.com/kb/304261

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Geoff A (11/2/2010)
they do not say NOT to do it, but rather say use caution;
http://support.microsoft.com/kb/304261

That link is about locating database files, not backups, on a share or NAS.

We backup several hundered SQL Servers to file shares and are not having any problem with it, and have not been having problems with bad backup files.

Geoff A SSChampion Points: 11511 More actions · Answer 4

Michael Valentine Jones (11/2/2010)
Geoff A (11/2/2010)
they do not say NOT to do it, but rather say use caution;
http://support.microsoft.com/kb/304261
That link is about locating database files, not backups, on a share or NAS.
We backup several hundered SQL Servers to file shares and are not having any problem with it, and have not been having problems with bad backup files.

please read the entire article. It talks about NAS and SQL Server backups....

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 5

I use the same procedure sequence as Adiga.

Back in the days, with SQL7 we encountered a files server going offline during a backup, and that caused the server that hosted this sql7 to reboot.

After that, we changed the procedure to local backups and xcopy/robocopy to safezone.

The disadvantage is that you need the extra local space, but it the copy to safe zone fails, at least your backup didn't fail.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

jamesn 89076 SSC Enthusiast Points: 147 More actions · Answer 6

Thanks for the comments everyone.

How do you guys do the "xcopy/robocopy". Do you include it as apart of the Backup Agent job or do it schedule it as a scheduled task or.....?

Adiga One Orange Chip Points: 27284 More actions · Answer 7

I usually run ROBOCOPY as a SQL Agent Job Step.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

WayneS SSC Guru Points: 95460 More actions · Answer 8

jamesn 89076 (11/2/2010)
Thanks for the comments everyone.
How do you guys do the "xcopy/robocopy". Do you include it as apart of the Backup Agent job or do it schedule it as a scheduled task or.....?

I have it done in two different ways.

On some of my clustered SQL Servers, I have a nice, large SAN drive that all databases in that domain are backed up to. On those, I have a SQL Server Agent job that runs after all of the servers have done their backups to local disk, copying the backup files with RoboCopy and the /MIR option to mirror that local directory on the database server. (This way, as the cleanup task deletes old backups, they will also be deleted from the RoboCopy'd destination.)

On some of my other domains, I have a large SAN drive on a file server for the backups. For these, I have a scheduled task, run from Windows, to RoboCopy the files from the database servers to the file server.

Note that I have a backup destination set up at each geographic location (3), on each domain (up to 4) - this is why there are different methods going on.

On the clusters that have that locations domain backups, the database backups for the instances on that server are also copied to a second server.

This setup gives me two copies of all database backups - one on the local server; the other on a second server.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 9

On most of our servers I use xcopy as a last step of the sqlagent job.

If you have robocopy I've heard it should perform better than xcopy.