Share this
Twitter
Myspace
Mister Wong
Webnews
Icio
Digg
Del.icio.us
Reddit
Jumptags
Upchuckr
Simpy
StumbleUpon
Slashdot
Netscape
Furl
Yahoo
Blogmarks
Diigo
Technorati
Newsvine
Blinkbits
Ma.Gnolia
Smarking
Netvouz
Folkd
Googlize this
Blinklist
Facebook
Wikio
Hear this text
Recovery Models:-
Recovery Models decides how the transactions are maintained in LOG files.
Backup Methods and Restoring Methods depends on Recovery Modes.
In Sql Server a database can be set with any one of 3 recovery Models.
-- Simple
-- Bulk – Logged
-- Full
1.Simple Recovery Model :- No Operations maintained in transaction log
Advantages:-
operations gives better performance
Log files contains always free space.
Disadvantages:-
No Recovery is possible from transaction log.
We can’t take transaction log backup.
Recommended Environment:-
Development DB servers & Testing DB Servers.
2. Bulk-Logged Recovery Model :-
Simple operations completely maintained in transaction log and bulk Operations minimally logged.
Advantages:-
Bulk operations gives better performance.
Recovery is possible for simple operations from transaction.
Disadvantages:-
Point in time recovery is not possible.
Recommended Environment:-
Data ware house systems and OLAP Servers.
3. Full Recovery Model :- Simple and Bulk Operations maintained in transaction log
Advantages:-
Recovery is possible for all operations from transaction.
Disadvantages:-
Operations gives less performance
Log file uses more space.
Recommended Environment:-
Data ware house systems and OLAP Servers.
Note:-
In sql server for master, msdb, temp db and resource databases recovery model is Simple.
Model Db and all user databases default recovery model is FULL.
Syntax:-
Alter database dbname set recovery simple/bulk-logged/full.
Backup Methods :- Sql server natively supports 4 types of Backups.
Differential
Transaction log
File and File group
Syntax for Full Backup :-
Backup database dbname to tape=’ file path like c:\master.bak’ with name=’file logical name’, init [ here init means it will overwrite the existing backpsets]
Syntax for Restoration:-
Restore database dbname from tape =’filepath’ with file=1, norecovery
Syntax for Differential Backup:-
Backup database dbname to tape=’c:\master.bak’ with differential, name=’logical name’, noinit [ Here noinit means it will append to the existing backupsets].
Syntax for Restoration:-
Restore database dbname from tape=’filepath’ with file=2, norecovery/standby/recovery
Syntax for Transaction log Backup:-
Backup Log dbname to tape=’file path’ with no_truncate(tail log backup), norecovery/standby/recovery
Syntax for Restoration:-
Restore log dbname from tape=’filepath’ with file=3,norecovery/standby/recovery
Recovery States:-
Norecovery :- database is not accessible
Standby :- database is accessible but readonly
Recovery :- database is fully operationl.
Syntax for File and File group Backup:-
Backup Database dbname filegroup=’filegroupname’ to tape=’filepath’ with name=’logical name’, init.
Syntax for Restoration:-
Restore database dbname filegroup=’filegroupname’ from tape=’filepath’
Point in time recovery:- latest full backup +latest differential backup + latest transactional backup
Syntax:- restore log dbname from tape=’filepath’ with file=2 recovery, stop at = ‘26/10/2010 05:35 pm’
Reviewed By: Hari Hara Muvvala, Hyderabad Techies
Similar Articles
- Move a database from one server to another server in SQL Server 2008
- Get Date anad Time in different formats in MS SQL Server - Useful at migration from Oracle
- Roll Back command in SQL Server
- Difference between Truncate & Delete in SQL Server
- Working with DB Snapshots in SQL Server
- Saving binary data in SQL Server from backend
- Query to eliminate duplicates in sql server
- Information about Cross editions backup and restore in SQL Server 2008
- Cool Features in SQL Server 2008 R2
- Execute UPDATE STATISTICS for all SQL Server Databases
Comments
RSS feed for comments to this post