Backup questions.

I made a dsicovery today that must change the way I do backups. I realized that each backup ADDs the whole database to the previous backup without deleting the previous instances (for previous backup days). If it is true, and I want Jens or someone else to confirm it, then what shall I do?

I do not need this old archive. I want to just store the latest version of my database. What I've had so far is a waste.

The log files also grow to enormous size for no reason.

Shall I delete all backup files each time and recreate them before doing backups?

Also, I have trouble opening up log files. I think I did it once or twice in the past but now do not remember how I did it. The system is asking me for a proper editor and anything I tried did not work.

How shall I open a log file

Thanks.

hi,

AlexBB wrote:

I made a dsicovery today that must change the way I do backups. I realized that each backup ADDs the whole database to the previous backup without deleting the previous instances (for previous backup days). If it is true, and I want Jens or someone else to confirm it, then what shall I do?

I do not need this old archive. I want to just store the latest version of my database. What I've had so far is a waste.

the "default" for the BACKUP syntax, is the NOFORMAT media set option, which preserves older existing headers and backups of the destination media.. you have to add the FORMAT option to reinitialize your media set.. very simply just execute

BACKUP DATABASE [yourDB] TO DISK = 'c:\bck.bak' WITH FORMAT;

for your convenience, please have a look at the BACKUP statement synopsis

The log files also grow to enormous size for no reason.

you should "truncate" the log, from time to time.. please start reading at http://msdn2.microsoft.com/en-us/library/ms345382.aspx

Shall I delete all backup files each time and recreate them before doing backups?

you are not required to.. as already stated, just initialize them accordingly..

Also, I have trouble opening up log files. I think I did it once or twice in the past but now do not remember how I did it. The system is asking me for a proper editor and anything I tried did not work.

How shall I open a log file

Thanks.

what do you mean by "opening up log files".. if you mean the database's transaction log files, you are not supposed to "open them" as you can not get any info from a text editor or even a hex editor as well.. the internal structure of the transaction log is proprietary and is not supposed to be "scanned"...

if you mean the "error log" files, you can see in the \Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG folder, named "errorlog", "errorlog1" and the like, they are just plain text files, and you can "open" them with Notepad as well..

regards

|||

Alex,

As explained by Andrea by default backup gets appended......so you have two options while scheduling backup as a job.........

1. In the job step the 1st step should be a delete step which will delete your previous backup.........it can be an OS command as follows,

del 'D:\Test\*.bak' which will delete all the .bak files residing in the Test folder..............

and the next step in the job will be to backup the db as follows

Backup database dbname to disk='path of your backup'

2. If you dont want to include delete step you can perform ina single step as follows,

Backup database dbname to disk='path of your backup' WITH INIT

the WITH INIT option will overwrite the old backups present............if you dont specify anything it will append the backup to the existing .bak file...........

hope this helps

Thanxx

Deepak

|||

Many thanks to Andrea as well as Deepak Rangarajan. I just read your responses.

. I have been derailed somewhat in my development when my WinXP partition lost Internet connectivity. I could not restart it after trying for a few days and decided to install a Vista. It seems to be a great OS and now I am putting it everywhere. Decided to do a complete overhaul of software and hardware setups. Time flies.

Thanks again.