SQL Daily Backups

[katman96]

I am trying to use the backup utility within SQL Server to do daily backups. (I know don't use the Microsft Software to do backups) But anyways... I had the daily backups working fine. I then installed all the "critical updates" as recommended by Microsoft (I know another rookie move) but it seems after that the daily backups stopped working. I again set all the parameters for a daily backup, click ok, and when I go back to look at it again all the options are back to their default state. I am getting no error messages of any kind. When I manually initiate a backup it works fine. Any suggestions? Thanks in advance..

 

[SQLBill]

What version of SQL Server do you have? Does it have the latest service pack? (Run SELECT @@VERSION). The last three numbers returned by @@VERSION will tell you if you have the latest SQL Server SP applied. For 2000 it will be something like 8.00.760. I believe if you check the FAQS, there is one that lists the versions and which SPs or patches they equate to.

You can also check out

http://www.sqlsecurity.com

and click on the FAQS/DOCS menu and then select SQL Server Version Database.

-SQLBill

 

[katman96]

The version I am running is 8.00.760 I have applied sp3a.
Does this shed any light on my backup dilemma? Thanks..

 

[mrdenny]

katman,
I have a stupid question. After you installed Service Pack 3a did you restart the SQL Server Agent? The installer stops both services, but doesn't always restart the agent after the service pack has been applied.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[katman96]

Yes SQL server agent was restarted... thanks

 

[mrdenny]

ok, figured that I'd check that first.

Is the backup job there? Does it show that it has been trying to run? What's in the job history?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[katman96]

How do I check the job history? How do I check if it is trying to run?

Sorry but I'm a little new at using SQL

The only backups that are there are from several days ago. It worked fine until I brought it out to our customer's site and applied the latest upadates as explained above...thanks

 

[mrdenny]

Open SQL Server agent, select jobs. You should see a job listed for the backup. Right click on it, and select View history.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

It might be a rights problem for the SQL Server agent login.

Questions about posting. See faq183-874

 

[SQLBill]

Okay, you didn't say that it worked at one location but didn't work after you moved it.

I agree with SQLSister, it seems to be a permissions issue...probably with the SQLServerAgent service.

Error messages.....

Check the Windows Event Viewer logs for any messages
Check SQL Server Error logs
If the backup was done via the Jobs, right click on the job and select Job History.

Is the backup being done over a domain? If so the services (MSSQLServer and SQLServerAgent) must be domain accounts.

-SQLBill

 

[katman96]

The backup is not being done over a domain, the backups are local.

The server is not at my location, I will have to investigate the points mentioned above as soon as I can get back there.

Thanks

PS When I was in SQL Server Enterprise Manager, trying to investigate why the backups weren't happening, I remember clicking on "overwrite existing media" in the Backup Section. Just by clicking that check box it actually backed up the DB I was working on. Could it still be a "rights" problem?

 

[SQLSister]

Yes, the login used for scheduled jobs is not necssarily the one you use when you are running enterprise manager. The rights you need to check are for the account that SQL Server Agent runs under, not your own rights.

Questions about posting. See faq183-874

 

[SQLBill]

Are you backing up to tape? If so, at the distant end the tape device might be 'screaming' for another tape to complete the backup. Without 'overwrite' the job will append, but append can not 'wrap' from the end of the tape to the beginning of the same tape.

-SQLBill