Julius.S


We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea or better option would be weekly full & daily incrementatl

Do we need to do any special backup on system databases or transactional logs

Please advice





Re: What is the best Backup strategy

Arnie Rowland


The normal questions to answer are:

  • How much data can you afford to lose
  • How much time can you afford to take to restore

By making daily backups, you have decided that you are willing to lose an entire days work if a failure occurs.

If there is light activity, that may be a reasonable plan. If handling other folks data, and those other folks wouldn't be so understanding if their data was lost, then you should consider other options.

A reasonable secure plan is:

  • Full Backups -weekly
  • Differential Backups -Daily
  • Transaction Log Backups -Hourly







Re: What is the best Backup strategy

Madhu K Nair

just to add few points to Arnie's comment... since it is small , i would say , if possible its always take full backup so that the recovery process will be easy. or even Differential Backup can be taken. TL backup will complicate the restoration process. If you don't want point in time restore then there is no need to take the backup of TL

Madhu







Re: What is the best Backup strategy

S.Julius

How do I take Full or Differential backup when user activity is going on

Is SQL Server take of consistent backup whenever we do backup using Studio

What could be the reason TL size is 40 GB but Data size is 200 MB only

Thank you all for your help






Re: What is the best Backup strategy

Arnie Rowland

SQL Server Backups can execute while users are using the database. There is no problem.

Backups are consistent when using T-SQL Backup commands.

Transaction Log could have grown that large while a database is in development (lots of changes, additions deletions, etc.) The Transaction Log does not reduce unless you specifically take actions to Backup the Transaction Log and reduce the size.






Re: What is the best Backup strategy

S.Julius

This TL size problem on production.

TL supposed to shrink as soon as COMMIT happened..correct

I thought that data changes will be transfered to data file from TL once commit happens

What is the reason TL size is related to TL backup






Re: What is the best Backup strategy

Madhu K Nair

refer following links.. you are righly said that the commited transaction s are written to the datafile . Then that log entry is called inactive part of the log. re-use of inactive part is depend upon the recovery model. So if you are in full recovery model, then you need to take the transaction log backup frequently so that the size is under control. read these link for more info
Madhu
Transaction Log Physical Architecture

msdn2.microsoft.com/en-us/library/ms175495.aspx

msdn2.microsoft.com/en-us/library/ms345382.aspx

support.microsoft.com/kb/907511

support.microsoft.com/kb/873235






Re: What is the best Backup strategy

S.Julius

Thanks for links..

Problem is I dont have space to backup 40 GB TL file. Should I execute DBCC shrinkfile before taking TL backup

We are doing daily datafiles backup using maintenance plans. then What is the use of inactive part of TL file It should shrink automatically ..correct






Re: What is the best Backup strategy

Arnie Rowland

After your next full backup, you could

BACKUP LOG MyDatabase

WITH TRUNCATE_ONLY

Then follow up with DBCC SHRINKFILE






Re: What is the best Backup strategy

Madhu K Nair

shrink the TL then take full backup and schedule the TL backup more frequently if its in full recovery mode

Use yourdatabase

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

DBCC Shrinkfile (yourLogical LOg Filename,Size)

to get the logical file name run SP_Helpfile and copy paste the name

Madhu






Re: What is the best Backup strategy

S.Julius

How do I find out Optimal Size for Transactional Log File So that It can be used in DBCC shrinksize parameter




Re: What is the best Backup strategy

Madhu K Nair

i don't think there is any simple formula... its again depends... if its a transaction oriented db then you should give such a size that there is no need to increase the size dynamically by the system.

Madhu






Re: What is the best Backup strategy

S.Julius

I just found out that , this database bulk-logged recovery model.

I am still dont understand is part of SQL server concept,

The transaction log is a wrap-around file, then why it is keep on extending filesize instead use the existing inactive part of data(expired data which are commited to datafile) irrespective of what kind of recovery model






Re: What is the best Backup strategy

Bryan C. Smith

Kalen Delaney's Inside MS SQL Server 2005: The Storage Engine goes into log architecture quite well. You may want to take a look at that book to better understand what's going on.

So, do you need point-in-time recovery Could you afford to lose a days' worth of data If so, switch to the Simple recovery mode to minize log growth. Alternatively, just take regular log backups throughout the day to release portions of the log in your current recovery model.

Keep in mind that with both of these options, the portion of the log used by a transation cannot be released until the transaction is committed. Is it possible you have a long running transaction in the database that is preventing the log from being released You can check this by issueing the following command with approprate substitution: DBCC OPENTRAN ('db_name')

Good luck,
Bryan Smith






Re: What is the best Backup strategy

S.Julius

I dont see much difference between Full & Simple Recovery Model ( consider full daily night backup for both model) .

In Both Model, If Data Disk Crashes , we can only restore & recovery uptil last night backup because we will data files as well as TL file.

Then what is the use of Full Recovery Model