Skip to main content

Recovery models

SQL Server offer 3 recovery models


  • Simple
  • Bulk-logged
  • Full
In the case of a simple recovery model, you are essentially agreeing that it is ok to lose all changes made to your database after the last full backup. The upside of this recovery model is of course that it takes very little administration. You don’t have to worry about anything like a log chain or tons of log backups, or even when the log will be truncated. Obviously this will never be good enough for mission critical databases, but it definitely has its place and its uses.
The purpose of the bulk logged recovery model is to allow you perform bulk operations without writing each transaction to the transaction log, and as such improve the performance of your bulk operation. The downside to this is of course that not each transaction is recorded in the log, and as such it does not allow you to do a point in time restore.
In order to be able to restore to a specific point in time, you need to have your database set to use the full recovery model. This means that every event which takes place in the database gets written to the log, which is why it then makes it possible for you to restore up to a specific point. But just having the database set to the full recovery model is not enough. Unless you have a complete log chain, you will still not be able to restore your database to the point in time you require.
Labels:

Comments

Post a Comment

Popular posts from this blog

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.                      SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1 FYI, id, startTime and endTime columns should be changed based on your table schema. you can change the output format here like alias and the separator.
Post a Comment
Read more

T-SQL to set Max memory in MSSQL

 To set max memory for Microsoft SQL server, use the below Transact SQL. USE master EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE GO --To set a maximum memory limit, type the following, pressing Enter after each line: USE master EXEC sp_configure ''max server memory (MB)'', <MaxServerMemory> RECONFIGURE WITH OVERRIDE GO --MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate. --To hide the maximum memory setting, type the following, pressing Enter after each line: USE master EXEC sp_configure ''show advanced options'', 0 RECONFIGURE WITH OVERRIDE GO exit
Post a Comment
Read more