backup job on SQl agent

[yuchieh]

We are using Lyris Listsev, which runs on SQL Agent databse. We set up a backup job and code is below (all in one line) --

BACKUP DATABASE [TestLyris] TO DISK = N'E:\Backup\TestLyris.bak' WITH NOINIT , NOUNLOAD , NAME = N'TestLyris backup', NOSKIP , STATS = 10, DESCRIPTION = N'Weekly Backup', NOFORMAT

The job runs fine. However, the first night the backup file came up with 2GB. the next night, the backup file was repalced (which we want) but the file size came up with almost 4GB. The ffile size accumulates (doubles) every night. It's really scary.

Can somebody check the code? or tell me what's the proper way to do backup?

We did setup maintenance plan. Because the database is SQL agent, after making the plan into nightly job, it disappeared from the job list. And it never really ran.

thanks

 

[mutley1]

Hey Yuchieh,

If you want to overwrite the backup, you should have the clause set to INIT as opposed to NOINIT, otherwise you will be appending to the backup file.

HTH,

M.

 

[yuchieh]

Hi M.

this is exactly what I needed and it fixed the problem. Thank you Thank you Thank you!!!

I am not familiar with that line of command and what they means. somebody else did the code. where can I find such information?

Thanks again!!!

 

[mutley1]

Have a look in BOL (Books online) which is part of the SQL install, under backup and restore. It should have all the syntax you need in there.

Cheers,

M.

 

[yuchieh]

OK. thanks.

 

[mutley1]

NP.

Cheers,
M