Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78

    Unanswered: Database won't backup or anything...

    Hi

    I have a database on my SQL Server which since Saturday will not backup. It is part of a maintenance plan which should do backup, log and optimisations. None of these will work on this database. Running a backup manually or using the maintenance plan jobs once started the job just sits there saying 'Executing job step 1'.

    We had a problem with the server on Friday to do with tape backups and the server was rebooted. There was no issue with SQL Server its self. All other databases are OK and backup normally even those in the same maintenance plan as this dodgy database.

    The system which uses this database works fine with no problems. EDIT: I should add that the first attempt at backing up where I realised there was a problem I stopped the backup from running and it said it had been going for 102 Hours! Even though it went for that amount of time it made no progress. The database only normally takes about 30 secs to a minute to backup.

    What can I do to fix this?

    Neil.
    Last edited by NeilA; 08-02-07 at 06:59.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are you able to backup the database through the QA or EM?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Hi

    It does not work in EM but I will give QA a quick try.

    Neil.

  4. #4
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Have tried QA and that isn't working either. I set stats to 10 so it should give me some progress but I don't get anything

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't use a Maint Plan?

    Write the backup yourself, or steal one

    You should be able to find something here


    http://weblogs.sqlteam.com/tarad/category/95.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how much space is on the destination drive and how big is your database?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run sp_who2 in Query Analyzer. Look for a session that is labeled "Backup database". Kill that SPID. It is possible that you have a failed backup that is blocking all others.

  8. #8
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Database is 50Mb and directory has about 25gig spare. Have just tried sp_who2 and there i only one thing in the list relating to my database and its me. There are no Backup Database commands running.

  9. #9
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Something I did get yesterday but forgot to post was when running the backup in QA it responded after several minutes saysing:

    Server: Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'D:\'. Device error or device off-line. See the SQL Server error log for more details.
    Server: Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Is this trying to tell me my D drive is offline or something? If so it lies because that's where everything else is backing up to OK.

    Neil.

  10. #10
    Join Date
    Dec 2006
    Posts
    19
    Have u changed the location of ur backups lately.....try changing the folder location there might be some permissions issues on that particular drive.......

  11. #11
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Mystery almost solved...

    Have changed the backup directory to use the L drive instead and I can backup the database sucessfully. If I try a backup on the usual D drive area it fails or never starts.

    This is what I think happened:

    The server I am using is a cluster between two servers so and at the weekend our techies had a problem with the tape software on one of the servers say ServerX. The cluster was currently running on ServerX and when they had to reboot it the cluster failed over to ServerY. So now the cluster is on ServerY and for some reason I can't backup this database to ServerY D drive.

    Does that sound about right? Anyone know why I woul have issues with one database not saving to the D drive?

  12. #12
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Seems ok now we backed up to L drive ok. Now tried D drive again and that works. Weired...

Posting Permissions

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