SQL backup options and feature details


Simple Recovery Model

Overview:

  • Transaction log is mainly used for crash recovery (no log backup)
  • Transaction log keeps all records after last backup (full or differential)
  • Supports Full and Differential backups only
  • Changes since last backup will be lost
  • Example: Full on Saturdays and differentials on weekdays.

Advantages:

  • Automatically reclaims log space

Dis-advantages:

  • The following features are not supported under the Simple Recovery Model:
    • Log shipping
    • AlwaysOn Database mirroring
    • Point-in-time restores
  • Changes since the most recent backup cannot be recovered

To view a database Recovery Model:
TSQL:

SELECT recovery_model_desc FROM sys.databases WHERE name = ‘MyDBName’

Powershell:

Using the SBSQL module:

Get-SQLRecoveryModel -DBName MyDBName -ServerName MySQLServerName | FT -a

To view Recovery Model of all databases on current SQL server, use the Get-SQLRecoveryModel powershell script.

Sample script output:

SQL01

To change a database Recovery Model:
TSQL:

USE master; ALTER DATABASE MyDBName SET RECOVERY SIMPLE

This will change the Recovery Model for database ‘MyDBName’ to Simple. Valid options are Simple, Full and Bulk_Logged

Powershell:

Using the SBSQL module:

Set-SQLRecoveryModel -DBName MyDBName -RecoveryModel Simple -ServerName MySQLServerName

To modify Recovery Model of all user databases on current SQL server use the Set-SQLRecoveryModel script.

Advertisements

One response

  1. Pingback: Powershell script to backup all user databases on current SQL server | Sam's Corner

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s