+ Post New Thread
Results 1 to 10 of 10
Windows Thread, [ANSWERED] How Do I Backup MS SQL to a UNC path? in Technical; Hi everyone, Got a question here on MS SQL. I have 2 MS SQL servers, both the 2005 edition and ...
  1. #1
    link470's Avatar
    Join Date
    Nov 2007
    Location
    Canada
    Posts
    252
    Thank Post
    86
    Thanked 8 Times in 6 Posts
    Rep Power
    16

    Thumbs up [ANSWERED] How Do I Backup MS SQL to a UNC path?

    Hi everyone,

    Got a question here on MS SQL. I have 2 MS SQL servers, both the 2005 edition and running the SQL Server Management Studio. One of the servers is the full software suite, the other is the express suite due to not having a second license. Both work great. So here's the issue, I've been programming proper backup schedules over the summer. Everything runs at it's specified time, all files etc. and important items on the network are now backed up automagically, and the only part I'm wondering about now is these two SQL servers and how to back them up.

    I started with a Maintenance plan in SQL Server 2005 [Full] and thought I could program a backup there, but it didn't like that. I'm trying to backup to a UNC path. However, when I enter in a UNC path the backup errors out and doesn't like it. When I was originally configuring the backup, it took me about 12 clicks of the OK button at the bottom before it finally accepted the UNC path, it kept saying it couldn't resolve the path I had placed in there for where the backup was to be sent. Somehow I had the great idea of continuously clicking ok, and it accepted and allowed it. Now if I go in and edit it, place in the same UNC path, it accepts it [weird]. So anyway, I tried to run the backup and again I get an error. I checked the logs and it said access is denied when trying to create the backup at that location. I've been backing up with an account for backups only, and my domain administrator account, those are the only 2 users who have access to the backups, no other users can read them or write to those shares. So I thought ok, MAYBE it's because I'm using the SA account as SQL authentication and not my integrated Windows login. So I tried logging in as integrated while logged into my domain administrator account and to verify I had access, I entered in the UNC path into the run command in windows and made a new folder in the share when it came up. So far so good, nothing wrong with the share.

    So the only issue now is getting MS SQL to actually write to the share. Am I doing anything wrong that you can see?

    As an alternative, I know SQL Express isn't going to allow me to do that since there's no automated backups. Is it possible for these two database servers, to just backup the database folder in program files that holds the actual databases using NTBackup? Or is that a bad idea?

    Thanks!

    Edit: Just changed the backup location to my desktop in a new folder and it works perfectly there.
    Last edited by link470; 2nd September 2009 at 06:51 PM.

  2. #2
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    3,098
    Thank Post
    314
    Thanked 296 Times in 206 Posts
    Rep Power
    122
    I have a couple of scripts that run and backup the files to a location that gets backed up to our backup server and also gets taken off site.

  3. #3
    AXE
    AXE is offline
    AXE's Avatar
    Join Date
    Dec 2008
    Location
    Right here. Right Now.
    Posts
    192
    Thank Post
    188
    Thanked 55 Times in 18 Posts
    Rep Power
    22
    You can script backups:
    save the following code in a file <script file>

    Code:
    USE master;
    
    BACKUP DATABASE <database> TO DISK='<backup file>';
    then from a command prompt or batch file (flags are case sensitive):
    Code:
    sqlcmd -S <server|instance> -i <script file> -o <output log file>
    you can toggle Trusted Connection using the -E flag

    You CAN backup the files in the database directory, but you have to shut down the SQL Server services first.

    Usually the account the service is running under (either SQL Server or SQL Server Agent) needs to have permission to access the folder you're backing up to.

  4. Thanks to AXE from:

    link470 (2nd September 2009)

  5. #4
    link470's Avatar
    Join Date
    Nov 2007
    Location
    Canada
    Posts
    252
    Thank Post
    86
    Thanked 8 Times in 6 Posts
    Rep Power
    16
    Quote Originally Posted by AXE View Post
    <server|instance>
    My bad, but what's a "server instance"? How would I write the server and then instance there? Thanks!

  6. #5

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,250
    Thank Post
    404
    Thanked 630 Times in 575 Posts
    Rep Power
    185
    Such as servername\database I think?

  7. #6
    link470's Avatar
    Join Date
    Nov 2007
    Location
    Canada
    Posts
    252
    Thank Post
    86
    Thanked 8 Times in 6 Posts
    Rep Power
    16
    Quote Originally Posted by Edu-IT View Post
    Such as servername\database I think?
    I was wondering about that but I'm not sure if that would work, it looks like you call the database in the first part of the script up top. Then down below in the bat file you have to do servername and something else for "instance". How is that written? Thanks!

  8. #7
    AXE
    AXE is offline
    AXE's Avatar
    Join Date
    Dec 2008
    Location
    Right here. Right Now.
    Posts
    192
    Thank Post
    188
    Thanked 55 Times in 18 Posts
    Rep Power
    22
    To clarify:
    <server|instance> Refers to the server.
    You can have more than one 'copy' of the SQL Server Software on that server, each 'copy' (instance) installed after the first must be given a name.

    <database> The name of the database to back up. Don't change 'master' in the script.

    For example:
    A basic server with one instance of SQL Server and the Server name SQL1 would look like:
    sqlcmd -S SQL1 -i <script file> -o <output log file>

    A server with more than one instance of SQL Server (say ABC and XYZ) and the Server name SQL1 would look like:
    sqlcmd -S SQL1\ABC -i <script file> -o <output log file>
    or
    sqlcmd -S SQL1\XYZ -i <script file> -o <output log file>

  9. Thanks to AXE from:

    link470 (2nd September 2009)

  10. #8
    link470's Avatar
    Join Date
    Nov 2007
    Location
    Canada
    Posts
    252
    Thank Post
    86
    Thanked 8 Times in 6 Posts
    Rep Power
    16
    Quote Originally Posted by AXE View Post
    To clarify:
    <server|instance> Refers to the server.
    You can have more than one 'copy' of the SQL Server Software on that server, each 'copy' (instance) installed after the first must be given a name.

    <database> The name of the database to back up. Don't change 'master' in the script.

    For example:
    A basic server with one instance of SQL Server and the Server name SQL1 would look like:
    sqlcmd -S SQL1 -i <script file> -o <output log file>

    A server with more than one instance of SQL Server (say ABC and XYZ) and the Server name SQL1 would look like:
    sqlcmd -S SQL1\ABC -i <script file> -o <output log file>
    or
    sqlcmd -S SQL1\XYZ -i <script file> -o <output log file>
    Thank you very much for taking the time to clarify that. I'm going to give er a try today and see what I can do. Thanks so much! I'll let you know how it goes if I remember.

    Edit:
    I think I have everything right with the syntax. I've got the script outputting a log. The backup doesn't want to take place because I'm still receiving the access is denied message. I researched on Microsoft's website and found that the SQL Express service has to be started with an account that has access to that share instead of the local service. So I created a new account and made the changes to the share so the new account could access the share. Then I edited the services to start with the new account I had created. It worked, fired up the service great. SQL's running correctly. Now I go to run the backup script again, and it fails with the same access denied message. Is there anything else I'm obviously doing wrong? Thanks!
    Last edited by link470; 2nd September 2009 at 10:28 PM.

  11. #9
    AXE
    AXE is offline
    AXE's Avatar
    Join Date
    Dec 2008
    Location
    Right here. Right Now.
    Posts
    192
    Thank Post
    188
    Thanked 55 Times in 18 Posts
    Rep Power
    22
    If all else fails, you could try backing up to a folder on the SQL Server, then copy the backup from there.

  12. #10
    link470's Avatar
    Join Date
    Nov 2007
    Location
    Canada
    Posts
    252
    Thank Post
    86
    Thanked 8 Times in 6 Posts
    Rep Power
    16
    Quote Originally Posted by AXE View Post
    If all else fails, you could try backing up to a folder on the SQL Server, then copy the backup from there.
    Ya I think that's what I'm going to do. Run a backup to a local folder and then run ntbackup to send the contents of the local backup folder to the backup server.

SHARE:
+ Post New Thread

Similar Threads

  1. UNC Path from Mapped drive
    By Pashers in forum Windows
    Replies: 5
    Last Post: 7th July 2009, 12:24 PM
  2. WM6 Std Smartphone UNC path access?
    By SYNACK in forum Hardware
    Replies: 6
    Last Post: 27th February 2008, 10:27 AM
  3. SIMS .net backup path
    By Jawloms in forum MIS Systems
    Replies: 2
    Last Post: 26th October 2007, 03:17 PM
  4. Deploy/run SIMS from UNC path?
    By ajbritton in forum MIS Systems
    Replies: 8
    Last Post: 4th April 2007, 02:25 PM
  5. Heinemann Geography/Goal Plugin UNC Path Problems
    By CHR1S in forum Educational Software
    Replies: 3
    Last Post: 20th December 2006, 01:05 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •