Petri.com forums Home Forums Start Page Forums Frequently Asked Questions FAQ Member List Members List
Go Back   Petri IT Knowledgebase Forums > Databases > SQL 2005
Petri.com is happy to award RicklesP the title of Most Valuable Member !!!
Register Calendar Calendar Search Petri IT Knowledgebase Forums Search Todays Posts Today's Posts Mark Forums Read
Notices

sql maint plan to unc path with diff user account

sql maint plan to unc path with diff user account

this thread has 7 replies and has been viewed 8339 times

Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1  
Old 20th July 2010, 07:46
norman1nz norman1nz is offline
Casual
Casual
 
 Join Date: Oct 2008
  6 month star 12 month star
 Posts: 12
 Reputation: norman1nz is on a distinguished road (10)
Question sql maint plan to unc path with diff user account

Hi there,
I am trying to schedule backups for a bunch of databases to another server which i can remote to from our sql server using serveraname\userid.
sql service runs with account serverbname\sqlservice.
The sql server is in a standalone / workgroup config.

I've set the backup to be on local disk for now.
I've tried creating a mapped drive and using either this or the unc path, trying to add the serverbname\sqlservice to back share on serveraname, trying to create a robocopy script to copy the .bak files from serveraname to serverbname but none of these have worked


How do we go about doing this?


Also a nice to have feature would be some staged cleanup task - im guessing i'd have to use a separate script / command to do this - along the lines of keep daily backups for a week or two, then weekly backups for 6 weeks, then monthly backups for a year etc.
Currently I'm manually creating occasional copies of database backups for long term storage
  #2  
Old 20th July 2010, 21:43
BrotherOfAllDBAs BrotherOfAllDBAs is offline
Casual
Casual
 
 Join Date: Jul 2010
  6 month star 12 month star
 Posts: 2
 Reputation: BrotherOfAllDBAs is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

If the backup job is running under serverbname\sqlservice, you will need to give write permissions to serverbname\sqlservice on both the servera share and the servera folder in the local filesystem.

Share permissions allow remote connection but do not override filesystem permissions, so you need to add those, too.

In an Active Directory scenario you would need SQL running as a domain user instead of a local user to allow the jobs to access a network share.
  #3  
Old 20th July 2010, 23:33
norman1nz norman1nz is offline
Casual
Casual
 
 Join Date: Oct 2008
  6 month star 12 month star
 Posts: 12
 Reputation: norman1nz is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

I tried adding serverbname\sqlservice to the share and folder security on serveraname, however as it's a workgroup config and local account without AD this doesn't work..
I can create a mapped drive if this is might be any help with a script to perform a move and 'archiving' of files from local SQL backup to the remote computer..?
  #4  
Old 21st July 2010, 00:35
BrotherOfAllDBAs BrotherOfAllDBAs is offline
Casual
Casual
 
 Join Date: Jul 2010
  6 month star 12 month star
 Posts: 2
 Reputation: BrotherOfAllDBAs is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

My memory failed me. I recalled being able to use a foreign security db, but that was probably way back in LANMAN days with mixed Win95/WinNT machines.

Okay, I took two lab machines off the domain and into a workgroup, and here is what I got to work. It isn't pretty, but it works.

I have server SQL1 running SQL as local user account SQL1\SQLDE . On server TDUM I created C:\Share, shared as "Share". I created account TDUM\SQLDE and assigned the same password I have on SQL1\SQLDE, then gave modify permissions to TDUM\SQLDE on share TDUM\Share and on C:\Share folder on TDUM. Then, as sa, ran BACKUP DATABASE AdventureWorks to DISK = '\\TDUM\Share\AdventureWorks.bak' . It worked.

The ugly bit is that Windows will try to use your local login name and password to log into a foreign share, so if the other machine has an identical account name and identical password then you get access.

So, translating to your situation, create account sqlservice on servera and assign the same password that serverb\sqlservice is using. Then assign modify share and folder permissions to servera\sqlservice.

(In case you're wondering, at one time TDUM had a companion server TDEE.)
  #5  
Old 21st July 2010, 01:21
norman1nz norman1nz is offline
Casual
Casual
 
 Join Date: Oct 2008
  6 month star 12 month star
 Posts: 12
 Reputation: norman1nz is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

Thanks Brother..!
Yeap that works great as a query but doesn't work when set in maintenance plan (from the GUI at least)..
Is there a way to incorporate this into maintenance plan so I can view success history etc?

Also is there a way to tidy up the backups..

We have one DB backup that is generally >1GB in size and includes all historical DB content for last week so we only need to retain a weekly backup plus the latest one.
Also would be nice to then retain monthly backups after about 6 weeks if poss...
  #6  
Old 19th August 2010, 00:09
norman1nz norman1nz is offline
Casual
Casual
 
 Join Date: Oct 2008
  6 month star 12 month star
 Posts: 12
 Reputation: norman1nz is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

OK this has been working fine backing up SQL running as serverbname\sqlservice of one standalone server to another.
Now I need to backup SQL which is running on a standalone AD DC to the other workgroup server.
How do I do this as the DC doesn't have local user accounts..?

SQL is currently running as local system so do i set it to run as a AD user, then create a local user on the workgroup computer with same username and password and write access to backup for the maintenance plan??
  #7  
Old 13th September 2010, 02:27
norman1nz norman1nz is offline
Casual
Casual
 
 Join Date: Oct 2008
  6 month star 12 month star
 Posts: 12
 Reputation: norman1nz is on a distinguished road (10)
Default Re: sql maint plan to unc path with diff user account

bump..
Anybody able to comment on the above?
  #8  
Old 13th September 2010, 08:31
Ossian Ossian is offline
Administrator
 
 Join Date: Nov 2003
  6 month star 12 month star
 Location: Bonnie Scotland
 Posts: 16,709
  Send a message via Skype™ to Ossian
 Reputation: Ossian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant futureOssian has a brilliant future (1549)
Default Re: sql maint plan to unc path with diff user account

Use a domain account?
__________________
Tom Jones
MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
PhD, MSc, FIAP, MIITT
IT Trainer / Consultant
Ossian Ltd
Scotland

** Remember to give credit where credit is due and leave reputation points where appropriate **
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
IIS 7 setup advice: unc path and multi websites roguecoolman Windows Server 2008 / 2008 R2 0 24th September 2009 01:51
Running Applications from a UNC Path on Windows TS 2003 R2 jhmorris Terminal Services 4 18th October 2007 02:00
Reconnecting a Mailbox to a User in diff domain chrispogi05 Exchange 2000 / 2003 2 8th August 2007 06:18
Install and Run account for the Limited User Account hops33n Windows 2000 Pro, XP Pro 4 7th August 2007 16:17
Change UNC Path to an unknown network drive letter jarmar General Scripting 30 24th February 2007 16:02


All times are GMT +3. The time now is 08:07.

Steel Blue 3.5.4 vBulletin Style ©2006 vBEnhanced
Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
 

Valid XHTML 1.0!   Valid CSS!

Copyright 2005 Daniel Petri