self-managed

The recovery mode/model of a database controls how restores can occur and if the log file can be backed up.

Simple model is ‘recovery to the last database backup” and “transaction logs are not backed up”. This means that you can not combine a (frequent) log backup to extend recovery to a last database backup (point in time recovery, and we can recovery to an intermediate time if we have the requisite logs) . if your recovery point needs to be recent, you need to backup the database recently.

Full recovery model means that the database is backed up, but the logs are backed up more frequently.  Recovery means getting the most recent full backup and the recent logs and combining them.  The expensive database backups are not done as often.  The cheaper and smaller log files are backed up often (and each time the log file is backed up and therefore can be recovered, the log can be made smaller).

Simple recovery is allowing SQL to manage itself, Full recovery means we have to manage the logs ourselves.  Obviously, if we don’t backup the database and in full mode the logs, we don’t have any recovery options.  Backup means recovery.

if we fail to backup the logs in Full recovery mode, they keep getting larger and larger.  In Simple recovery mode, each time we have a checkpoint, updates are written to the mdf file and the log gets smaller.

to change the mode, do a backup, then in SSMS, select the database, properties, options, recovery model drop-down, select and OK.  Change is immediate.

To view the mode in T-SQL:

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ; -- database name
GO

to change the mode

USE master ;
ALTER DATABASE model SET RECOVERY FULL ; -- simple, bulk-logged

After changing to simple, make sure there are no tasks running to backup the logs.  After changing to Full, immediately run a backup to start the log chain.

————-

Database files (data or logs) can be set to a size, to a maximum size and to autogrow when small and and if autogrowing, how to grow (% or specific amount).  If autogrowth is not enabled, the size of the file needs to be managed by the DBA, application or Agent so that the file is truncated or reduced periodically.  The log size will be reduced (or at least the percentage used) following a backup (Full) or a checkpoint (Simple).

SSMS, database, properites, files, column for autogrowth\maxsize.

 

 

 

 

Advertisements