Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: SQL 6.5 Backup Question

    First off, I have Zero experience on a SQL 6.5 Server, please be patient with me if these are dumb questions.

    We have a SQL 6.5 Server running on a Windows NT (SP5) server on our network. I have been told to monitor this server, but not to do too much with it. In theory, it should be going away sometime soon, but I'm not going to hang my hat on that theory. My questions relate to backups on this server.

    Problem #1. Backup job fails
    According to the entry in SQL Executive, there is one scheduled job to backup the single production user database. It has failed more or less continuously. It appears that this failure is because the server caanot write to the shared folder to which it is attempting to back up. In the event log, I see an error indicating that the file could not be opened due to Access being Denied (Error 5).

    Problem #2. Backup Succeeds, but where?
    Perusing the Windows EventLog (this is Windows NT4), I see entries indicating that all the databases on the server are being backed up daily. The events all occur at more or less the same time of the day. The SQL Error Log entry is:

    DATABASE dumped with the following info: Database name: tempdb, Creation Date and Time: NULL(NULL), Pages dumped: 64, Current Sequence: 38103 18667167, Sort Order: 52, Striped:NO, Number of Dump Devices: 1, device infoVOLID=NULL NAME=\\.\pipe\dbasql60\dbagent0s0 TYPE=NMPIPE FILE=1)

    I see a similar entry in the NT log file.

    I also know the following information:
    1. The SQL Server and SQL Executive accounts are both set up to run under local system.
    2. There is not enough room on the local drive to backup the production database.

    So several questions:
    1. How can I backup to a shared drive on another server? Do I have to configure SQL to run under a Domain User account?

    2. Where is the backup job(s) in #2 above running from? I see no entry in SQL Executive and there is no entry under scheduled tasks for the NT box.

    3. Where is the dump device located for the backups that are being run in #2?

    Sorry for stupid questions. Many thanks in advance for your help.

  2. #2
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    For
    Problem #1. Backup job fails

    >>enter one line code above all sql code of backup, and start backup job.

    dbcc traceon(1807)
    go

    are you using unc path for shared folder destination
    -----------------------------------------------
    Problem #2. Backup Succeeds, but where?

    >>can u paste the output of below query... for more information

    use msdb
    select * from sysjobs
    select * from sysjobschedules
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    #1a. I will set the trace flag on.
    #1b. No, the previous administrator set it up by logging in to the server, mapping a drive and then created the backup device using the mapped drive.

    #2a. There is no sysjobs in msdb (this is SQL 6.5). There is a table called systasks. It has one entry; the one that fails in #1. I did not find a table called sysjobschedules, nor could I find anything that resembled it.

    I was wondering if the successful backup job that I see is originated from a different server?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Well, I finally found out the answer to question #2 (where were the successful backups originating from). The answer came in a a round about way. They were (past tense) being performed by a remote server using ArcServe 2000 with an agent for interfacing with SQL Server 6.5. I found out because for some reason, ArcServe quit doing the backups and the database died when the transaction log filled up.

    So with renewed urgency to find a solution, I configured the service account for SQL Server to run under a local account. I created an identical account on another server and I now back up the server over the network (complete daily, log backups every 6 hours). There's only 4 GB of HD space on the local drive, so that's not an option.

    This is what happens when mgmt keeps saying, "just milk it for a little while longer; we have a project underway that will replace it in 30 days...er...90 days...er...well, it's going to be replaced!"

    Regards,

    hmscott

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The entry in the error log you posted did look like an indication of a third-party backup utility.

    In 6.5 world I saw quite a bit of confusion on behalf of those 3rd-party tools, so the most reliable backups were the native, or SQLMAINT.EXE

    In order to do a network backup you have to run the SQLExecutive service under domain account with WRITE permissions on the share. Apparently ArcServe was running under such account and was able to write to a share. My understanding is it was using the native backup to create a file and then backed up that file, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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