newbie Q: re: backup script

[IT247]

Hello
I followed one of the FAQ's and executed this in a sql agent scheduled job

CREATE procedure usp_BackupDatabase
@DatabaseName varchar(100)
as
declare @date varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)

set @BackupPath = 'c:\temp\'
set @date = convert(varchar(10), getdate(), 112)
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '.bak'
backup database @DatabaseName to disk=@BackupFile
GO

i looked in event viewer and see that all db's backed up exect tempdb.

the error is:

SQL Server Scheduled Job 'test sched bkup' (0xEDC0CF410BC683489C569D9D0A379FEB) - Status: Failed - Invoked on: 2006-09-21 16:16:00 - Message: The job failed. The Job was invoked by Schedule 1 (test sched). The last step to run was step 1 (DB Bkup).

can someone tell me what went wrong or even if i should be trying to backup tempdb???

 

[Catadmin]

Go into the job properties, edit the step in question, click Advanced and click on the VIEW Log. This will give you a more detailed error message than what you're seeing now. Post that and we should be able to help you locate the problem.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[IT247]

I did view job history, show step details. Here it is

Executed as user: NT AUTHORITY\SYSTEM. ...n file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 1969 pages in 4.243 seconds (3.799 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 80 pages for database 'model', file 'modeldev' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'model', file 'modellog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 81 pages in 0.310 seconds (2.117 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 1464 pages for database 'msdb', file 'MSDBData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 1465 pages in 3.889 seconds (3.084 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 320 pages for database 'Northwind', file 'Northwind' on fil... The step failed.

mind you, when i checked my c:\temp there was a file for all but tempdb.

 

[mutley1]

Hi IT247,

Are you saying you run this on each database as a seperate job and the tempdb one is failing, or are you running 1 job that covers all the databases?

Short answer is no, you don't need to back up tempdb, but if you let us know the answer to the above then might help solve the prob.

Cheers,

M.

 

[IT247]

Hello
I am running one job that runs all of them
with this statement in the job step

sp_MSForEachDB 'master.dbo.usp_BackupDatabase ?'