Backup plays very important role when the users are working with the database. Backup is a process of copying files and database, so that data can be preserved for future disasters. Same goes for SQL server database backup, from the point of disaster recovery, backups are very crucial for restoring the data back in database.
SQL database backup also includes retention time which specifies the length of time to retain each backup set. In this article we will go through the different situation that generates the need of SQL Server database backup retention periods and discuss the method to specify the retention time length for a SQL server backup.
How To Set SQL Server Database Backup Retention Time
Administrator can set the time length to retain the SQL backup. To set the retention period user can configure the media retention configuration option by using SSMS or Transact SQL.
Media retention specifies the duration of time to retain the backup.
The maximum length of time to retain the back is 365 and it’s default value is 0 days.
Note: SQL server generates warning message, if the administrator use the backup medium before the set retention time. SQL server does not generate any warning unless the administrator change the default value of media retention.
How To Configure Media Retention
Via SQL Server Management Studio
- Open Object explorer and right-click on Server.
- Select Properties.
- Click on Database settings node.
- In Backup/Restore, in the Default backup media retention box, user can select a value from 0 through 365.
Via Transact -SQL
To configure Media Retention via Transact -SQL user can follow below mentioned steps:
- Connect to Database engine
- Click on New Query, from standard bar
- User can use sp_configure command to set the retention time
Need for SQL Server database Backup Retention Period
The retention of backup is needed for recovery process, administrator must schedule daily, weekly or monthly basis backup. For following situations, one needs to retain SQL server database:
- Recovery Reasons: If the SQL server database get corrupted or there are other issues in SQL database such as server failure, database failure, site failure, data corruption, etc in such situation the only way to recover the database from it’s backup retained in the system.
- Legal Reasons: Due to legal and compliance issues any organization may leads the needs data backup.
- General Practice: It is very important to build a database plan as well it’s recovery plan. Admin must establish the full backup of SQL database weekly or monthly bases and main the data in a centralized form.
These are some of the reasons that generates the need of SQL database backup.
Conclusion:
With the help of media retention policy administrator can retain the data of SQL database for future recovery process. User can configure the media retention via Transact- SQL and SSMS. Retaining the backup of SQL database, helps the users to recover their data when the situations like corruption and system failures arises in the server.
EmoticonEmoticon