Help a novice DBA with unc path

WvrLthr
WvrLthr used Ask the Experts™
on
Hello All,

I understand SQL Server architecture and we are currently running on SQL Server 2005.  The trick is that there is very little DBA work here,  so the knowledge/skills get very rusty.  We are moving to SQL Server 2008 R2 on VMWare and want to have a common set of disk on the vcenter server for our servers to use for backup, so I need a unc path.

In researching, it seemed like unc is not an option.  However, when I play with a maintenance plan, it seems to work fine.

My question to you is what am I missing?  Am I going to hit a brick wall down the road during a process?  
-restore or restoring to another server
-keeping a couple full backups and their associated diffs and trans logs
-scheduling backups on SQL Server Agent
-something I am not thinking of

I have attached a screen shot of how I had the maintenance plan set up and it works fine when I run it from SQL Server Agent.

I appreciate your advice and time,

Jeremy
sqlbak.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
You should be able to backup SQL db's to UNC path as long as the user running the command has sufficient rights to that path:
"A UNC name has the form \\Systemname\ShareName\Path\FileName. "


"Backing Up to a File on a Network Share"
http://msdn.microsoft.com/en-us/library/ms179313.aspx#NetworkShare
WvrLthrIT Manager

Author

Commented:
lcohan,

Thanks for the reply.  I took a look at the link and it helped break some things loose for me.  I ran it past my boss who is the windows network/domain guru here and it seemed to match up.




I am pretty confident, but one final picky question.  

In my example, I did not specify the full name of the device, for example,  \\vcenter\sqlbak\productiondb.bak.  I just have the path as \\vcenter\sqlbak\.  When I run the backup it creates the file name based on the name of the database which is what I prefer.  

So many posts on the internet talk about having the full file name specified by defining a backup device and media sets.  Your reply was "You should be able to backup SQL db's..."  Is it correct that we should also be able to do all the other tasks I listed and any I am not thinking about?  I don't want to find a place where not putting the full file name specified in the backup maintenance plan messes something up down the road.  


I know this is picky and I appreciate your patience, but this has ramifications on how we divide up the disk so we don't want to have to undo something several months down the line.

Thanks,

Jeremy
Database Analyst
Commented:
My guess and bet is (and as you tested it for yourself) the full path is obviously enough assuming the db backup name is either in the sql script or specified by a (dump) device.
And yes, our maintenance plans run fine as well against the full UNC path (network share) from SQL Agent - just make sure the account starting SQL agent has sufficient rights to that path.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

WvrLthrIT Manager

Author

Commented:
I will assume you have done restores successfully from a unc path and close the case.  Thanks.
WvrLthrIT Manager

Author

Commented:
Didn't specifically address that restores have/will work using unc, but it seems pretty likely that they do.
lcohanDatabase Analyst

Commented:
Restore works like a charm indeed - in SSMS you paste in the UNC full path \\vcenter\sqlbak\productiondb.bak like you put it in the SQL script restore command as you can't navigate(browse) to it from SMMS restore UI.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial