Backup failure

Question

Backup failure

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

michelles (11/5/2009)
I'm sorry - I'm not sure how to do that. From the toolbox, do I choose "Execute SQL Server Agent Job Task"? I can't seem to paste the code you suggested any where. I'm still trying to figure all this out...

From SSMS, expand SQL Server Agent

right click jobs

select New Job

a new window opens

type a name of the job

click on Steps tab

Click on New

type a name of the step of the job (any name)

in the textbox provided for command, paste the code i wrote earlier.

click on ok

---

Again right click jobs from SSMS and select refresh

you'll see the job you just created

right click that job and select start job at step... the job will start executing job steps...

Pradeep Singh

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 2

Thank you for your patience and instructions. I was able to create the job as you stated, but it failed. I just called the job "test1", here's the error posted in the log file:

Date11/5/2009 10:19:29 AM

LogJob History (test1)

Step ID1

ServerPYTHIA4R\P4_2005

Job Nametest1

Step NameTest1

Duration00:00:00

Sql Severity16

Sql Message ID3013

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: PSC\SQL_ADMIN. Could not locate entry in sysdatabases for database 'test'. No entry found with that name. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I feel like I'm shooting in the dark. I'm responsible for backups and minor PC updates, etc. I'm not a "code" person and am starting to feel like I'm never going to "get" this.

ps. One Orange Chip Points: 29422 More actions · Answer 3

That's because there is no database named test in your system

backup log test to disk=@log_name

Edit the job and Replace the word test with actual database name for which you want to take log backup and re run the job.

Pradeep Singh

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 4

IT WORKED!!! Now that I have a job, I can at least set a regular schedule. I can't understand why the maintenance plan keeps failing.

Can you please tell me how to manually write a job to run the full backups or provide me a link the will show the code?

Thank you so much for your patience and assistance!

ps. One Orange Chip Points: 29422 More actions · Answer 5

A similar code and a seperate job would suffice for full backups.

declare @log_name as varchar(50)

set @log_name='c:\backup\test_Full_' + convert(varchar,getdate(),112) + '.bak'

backup database test to disk=@log_name

As stated earlier, do not forget to replace the word test with actual database name...

you should decide the interval at which you want to take the log backup and full backup and then set the schedule accordingly..

Pradeep Singh

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 6

You are the master! I just set up another job with the code you sent, and it worked like a charm!

Thanks for everything!

ps. One Orange Chip Points: 29422 More actions · Answer 7

Thanks for the feedback. Much appreciated:-)

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 8

One thing to note here..

Backup files will continously be created. You'll need to delete old files manually to prevent the disk from getting full.

Pradeep Singh

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 9

Thanks for letting me know. I'll be sure to clear them out once a week. One more question, if I wanted to also set up differential backups, would I replace the ...\test_full_' with ...\test_diff_'

ps. One Orange Chip Points: 29422 More actions · Answer 10

you'll need to use with differential clause...

sample code:

declare @log_name as varchar(50)

set @log_name='c:\backup\test_Diff_' + convert(varchar,getdate(),112) + '.bak'

backup database test to disk=@log_name with differential

Pradeep Singh

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 11

Thanks, I guess I almost had it. I never would have gotten the end code right.

Thanks again for all your help. It is immensely appreciated.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 12

Another thing to note is that if you are in full recovery mode you will need to watch the transaction logs and make sure they are not growing out of control. You mentioned that you took the code provided here and made full and differential backups. I would suggest making a job for transaction log backups too. Especially if point in time recovery is important to your users. I cannot remember the code that was provided verbatim, but you will need to replace the "BACKUP DATABASE" text with "BACKUP LOG". Also, where the code had .BAK you will need to have .TRN to signify a transaction log backup.

Joie Andrew
"Since 1982"

michelles Mr or Mrs. 500 Points: 578 More actions · Answer 13

I think Pradeep gave me the code to run the log backups:

declare @log_name as varchar(50)

set @log_name='C:\Backup\test_log_' + convert(varchar,getdate(),112) + '.bak'

backup log test to disk=@log_name

Now that you mention I guess I'm not sure if this is actually the "Transaction Log" backup or something else since the file extension is .bak instead of .trn??

Thanks for bringing it up.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 14

By the looks of the command it is backing up the log, but traditionally you would not end a transaction log backup with .bak. I think this is not so much of an issue of if SQL would be able to restore it as much as it is the DBA being able to determine what are his/her full backups from the log backups.

Joie Andrew
"Since 1982"