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.are you able to backup the database through the QA or EM?|||Hi
It does not work in EM but I will give QA a quick try.
Neil.|||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 :(|||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|||how much space is on the destination drive and how big is your database?|||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.|||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.|||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.|||Have u changed the location of ur backups lately....try changing the folder location there might be some permissions issues on that particular drive......|||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?|||Seems ok now we backed up to L drive ok. Now tried D drive again and that works. Weired...
