The first thing you have to remember before back up of sql server data base is Recovery model..
The Recovery Model describes what data to keep in the transaction log file and for how long.By recovery model types you can select these options.
Basically we have three types of recovery models
1)Full
2)Simple
3)Bulk-Logged
every data base has only one recovery model.but each data base use a different recovery models.that is you can select one database for simple recovery model and another data base for full recovery model and so on..
The selections is made up of the type of data base and backup needs.The only exception to this is the TempDB database which has to use the "Simple" recovery model.
We can change the database recovery model at any time.but it will impact your back up chain..so for this reason it is better to take full back up after you changed the recovery model.
The recovery model can be changed by either using T-SQL or SQL Server Management Studio. Following are examples on how to do this.
Using T-SQL to change to the "Full" recovery for the AdventureWorks database.
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
1) SQL SERVER FULL Recovery Model:
The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file. This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason. So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.
Here are some reasons why you may choose this recovery model:
Syntax:
ALTER DATABASE dbName SET RECOVERY recoveryOption
GO
The Recovery Model describes what data to keep in the transaction log file and for how long.By recovery model types you can select these options.
Basically we have three types of recovery models
1)Full
2)Simple
3)Bulk-Logged
every data base has only one recovery model.but each data base use a different recovery models.that is you can select one database for simple recovery model and another data base for full recovery model and so on..
The selections is made up of the type of data base and backup needs.The only exception to this is the TempDB database which has to use the "Simple" recovery model.
We can change the database recovery model at any time.but it will impact your back up chain..so for this reason it is better to take full back up after you changed the recovery model.
The recovery model can be changed by either using T-SQL or SQL Server Management Studio. Following are examples on how to do this.
Using T-SQL to change to the "Full" recovery for the AdventureWorks database.
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
1) SQL SERVER FULL Recovery Model:
The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file. This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason. So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.
Here are some reasons why you may choose this recovery model:
- Data is critical and data can not be lost.
- You always need the ability to do a point-in-time recovery.
- You are using database mirroring
- Complete backups
- Differential backups
- File and/or Filegroup backups
- Partial backups
- Copy-Only backups
- Transaction log backups
Syntax:
ALTER DATABASE dbName SET RECOVERY recoveryOption
GO
Example:
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
How to set using SQL Server Management StudioGO
- Right click on database name and select Properties
- Go to the Options page
- Under Recovery model select "Full"
- Click "OK" to save
This all about Full Recovery model .Next post We will Discuss about Simple recovery model and uses..
repair database sql corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc. Tool supports the following files: Microsoft SQL Server 7.0, 2000, 2005, 2005 64-bit, 2008 and 2008 R2. Recovery process is performed with step-by-step graphic interface not requiring any special skills.
ReplyDelete