Recovery Model
- how your dml operation to handle in log file
- what back up operation you can use
1.1 what is
- Database property that controls how transactions are logged and what restore operations are available
- Typically a database uses Full or Simple recovery models
- Can be switched at any time
1.2 Recovery Models
1.2.1 Simple
Little to no log backups
Reclaims space used by logs to keep requirements small
Unable to use Log Shipping, AlwaysOn, Database Mirroring, Point in Time Restores, and Media Recovery
doesn't hold any record in log file longer, doesn't have any log backup
performance faster, but lost log file
但是他立刻去了mdf
？？？怎么做back up？？？
1.2.2 Full
- No work is lost
- Can recover to any point as it logs all transactions
1.2.3 Bulk-Logged
- Permits high performance bulk copy operations for logs
- Records bulk operation logs
1.3 note
full;bulk-logged
- 就算删除了数据，数据还是会在ldf，因为要做tail back up
在没做back up之前，flag会是no,意味着不能重写，在做完log back up 之后，可以重写
区别：
bulk insert
full: 所有数据都插入
bulk-logged: 1000条1000条插入，只有10个enterence,当系统crush时候，back up的时候可以保存一部分（minial logging)
non bulk: row by row，log 存在ldf
bulk operations: 一块一块的插入，
2. back up
2.0 why we need it
- prevent data loss
- 访问量过大, high traffic
- upgrade to different version
- move database from one server to other server
- hacker attacks
- arctive(?) 旧的数据不用了，从sqlserver取出来
2.1 what is
Backups are used to make a compressed copy of the data in a database
Only backup data if it is online, any offline databases can’t be backed up
If a backup is started when a DB is being created, the backup will wait or time out
It is a mechanism that is provided by SQL Server to make copies of data at different levels. To handle situations like server/DB crashes or to maintain another copy of DB to support high traffic to DB situations. Most common reason is to prevent data loss in case of a crash.
2.2 type
2.2.1Full Backup
Copies all the data in a specific database with enough logs for recovering data
With this SQL Server takes back up of complete DB until the point of the back up. Usually this would take longer and resource intensive. For that reason it is not carried out very frequently. Depending on the size of DB or probability of crashing or minimum time required (defined by business) to recover the DB it is done once a month or twice a month.
complete db is backed up
eg: 200 gb delete need 3hr or 4hours, so we have a plan: once a month at 1st of this month 12:00am
2.2.2 Differential Backup
Records all the data that has been changed or modified since the last Full Backup
With this SQL Server takes back up of the data which is modified/added/deleted after last Full Back Up. Usually this will take few minutes to less than an hour depending on the frequency of changes on DB. If the frequency of changes on DB is high then more Differential back ups are recommended, it also depends on how frequently log back ups are taken.
eg: every day 120mb, but when 8am 40mb (full back up) differential back up(?)
2.2.3Transaction Log Backup
Records all the transaction logs that were not backed up in a previous Log Backup
This back up option backs up the data since last back up (any back up log/differential/full). This is most frequent back up type of all. Depending on the afford-ability (defined by business) of data loss, how fast the DB has to e brought online in case of crash, frequency of changes to DB it is decided that how many log back ups should happen in a given period. (This is not supported in Simple recovery model). Most common frequency is every 30 mins or 15 mins.
server doesn't fail
2.2.4 Tail-Backup
Records the latest log records that have not yet been backed up to prevent data loss
This option is used in case of a crash of DB in between 2 log back ups.
3. sql server job agent
3.1 what is
Tool in SQL that allows one to execute scheduled admin tasks or syntax
Great for performing backups late at night or doing long tasks that would take too much time to execute normally
3.2 opitions
- Jobs – Specified actions to be taken in SQL
- Schedules – Specified times when jobs run
- Alerts – Automatic response to an event
- Operators – Used to define contact for those responsible via Email
3.3 note
SQL Job Agent: It is a tool available in SQL Server to schedule a particular process (job). The job can be executing SQL code/SP, executing a SSIS package, running a batch file, processing a cube etc. A job can be created without schedule for ad-hoc execution. A job can contain multiple steps within it. Information about jobs and schedules are stored in MSDB. When a particular job executed successfully or failed you can see that info in the MSDB catalogs or history of the job.
4. disaster/recovery strategies
- cold solution: backups
- warm solutions: log shipping
- hot solution: database mirroring
- consider the following:
size of db
number of transactions that occur
acceptable down time and data loss
budget
