Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Unanswered: Backup Device Locations Help

    I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.

    I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.

    I have two drives setup for data and transactions logs (D: E

    My backups are running but backups are being sent to the d:systems data\mssql\backup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.

    My question is this:

    How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Its not good idea to perform backup over network share.
    Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

    Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19
    Originally posted by Satya
    Its not good idea to perform backup over network share.
    Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

    Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.
    ------------------------------------------------------------------------------------
    Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

    And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?

  4. #4
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    Originally posted by Arra2
    ------------------------------------------------------------------------------------
    Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

    And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?
    easiest thing to do is type xcopy /? or copy /? at a command prompt, which will give you the syntax and definitions of the options

    put that in a batch file and schedule it with 'at' or the Task Scheduler, you can also add that step to your MSSQL job if you want
    drew
    ------------------
    http://wjtechnologies.com

  5. #5
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Talking

    Thanks. That helps.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Satya
    Its not good idea to perform backup over network share.
    Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

    Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.
    I've never heard a valid argument against network backup devices. Here's BOL excerpt:

    Add a network disk backup device
    This example shows a remote disk backup device. The name under which SQL Server was started must have permissions to that remote file.

    USE master
    EXEC sp_addumpdevice 'disk', 'networkdevice',
    '\\servername\sharename\path\filename.ext'

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Though the facility is provided to perform backups over network, in a real-time environment you will have troubles such as process will block other connections while performing backups.

    I agree and allow for small databases which are below 1GB in size, if not I approach other way to copy the local backup file.

    As far as my experience is concerned its not a good approach on a enterprise wide network hosted SQL databases.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Satya,

    How will a backup process possibly block other connections??? I certainly respect your experience, but it makes me wonder...

    Here we have a dedicated file server with 1.5T disk space, that we use to store backup files for a 1-day retention. All 120 (!!!) servers are being backed up onto this server. The disk is organized into subdirectories with names corresponding to source SQL Server names. Once all the backups are complete the tape backup kicks in. The only problems we have occasionally (a couple of times a year) is when there is a network glitch. But the jobs a written in such a way that the backup job continues processing remaining databases after creating an exception job that would contain failed backups. At the end the job would report an error and DBA on-call gets notified and resolves the issue by running the exception job . In the past 5 years that exception job run failed only once, when the network team failed to inform us that the LAN would be down during the certain time frame. Once Change Management was implemented 4 years ago we never had any issues with this approach since!

  9. #9
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    rdjabarov

    Thanks for your explanation and my intention is not to contradict your statements, I'd delivered what was experienced and you had already facted that situation and if the network and infrastructure is well built then you can test the process and deploy it.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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