SQLServer backup and processing of logs
SQLServer backup and processing of logs
I saw a lot of questions about how much the data log file is too big. I also saw many unreal practical practices, and some people were impressive because of the wrong operation but there was no log. In fact, the database log file is recorded database Update, Delete The tracking of the operation is also when the database is unexpected (such as hacking, the system database is destroyed, etc.) We can restore the database to a specific time guarantee, so it is very important !! We should make a log backup for the database! We are now Analyze the type of database failure: 1: The fault inside the transaction is that we cannot guarantee the consistency state of the transaction. For example, you have two bank accounts. For some reason, you now need from a bank account. Take 1 million to another bank account, but because of the fault of the bank database, your bank account is indeed less than 1 million, but another account does not increase 1 million, you will do it now: (Of course we You can add ROLLBACK to the corresponding transaction to stroke rolling 2: System failure (Software Failure-Soft Crash) system fault refers to any event that causes the system to stop operation, and should not be restarted. Such as a specific type of hardware failure (CPU failure ), Operating system failure, DBMS code fault, sudden power outage, etc., such faults affect transactions that are running, but do not destroy the database. At this time, all memory data is lost, all transactions are absent, some unpleained transactions It may have been given to the physical database, so that the data in the database is incorrect. To ensure the consistency of the data, you need to clear these transactions to the database's tamper! At this time, after the system is restarted, all submitted transactions REDO, All non-normal business undo is forcibly undo all unfinished transactions! Three: Media faults (hardware failure - Hard Crash) This is an exemplary fault, such as disk corruption, head collision, transient strong magnetic field interference, etc. The possibility of faults is the smallest, but the destructive is the largest. I have a simple principle of computer virus's above-mentioned fault database. It is two words "redundancy" now we say how to build "redundancy" technology! Back up! Look at the backup technology of SQL Server, all databases are the same, SQL Server is the same, prevent data loss! This chapter mainly includes the following aspects: 1. In SQL Server we can set up the database recovery model To complete recovery model, large-capacity insertion model and simple model, 2. When is it necessary to make a database backup 3, there are several database backup methods 4.0, demonstrate several backup methods (preparations before backup) 5. Several The contents of the backup strategy can find a very good answer in Book on Line, such as If you need, I will write :) Exercise: Example: 0. First, there is a full backup (otherwise, you are not able to successful backup log files :)) Oh, BACKUP DATABASE Northwind to NWBAKUP1, NWBAKUP2 with INIT - View Backup Information Restore FileListonly from NWBAKUP1, NWBAKUP2 can also also be in the Enterprise Manager -Northwind-Right-Right-Right-All Task-Backup Database, and then perform the appropriate operations under the graphical interface, this is a very simple 1.Use Northwind Insert Employees (Lastname , firstname ('aaa', 'aaa') delete from employees where lastname = 'aaa' records this time 23:33 2. Insert User Northwind Insert Employees (Lastname, Firstname) VALUES ('BBB ',' bbb '
3. At this time, Select * from Employees will be able to see these two records 4. Log backups, select the Northwind database, all tasks, Backup Database In the dialog box, select Transaction Log Backup to Backup Type, select Existing Backup Equipment, and append to the media click OK to back up. This generates two new records of log backup Backup log northwind to nwlogbakup 5. The following demo restores the time to restore in all task Restore Database dialog boxes, enter the time that is just recorded. Click OK Recovery Restore Database Northwind from NWBAKUP1, NWBAKUP2 WITH NORECOVERY USE Northwind server: Message 927, Level 14, State 2, Row 1 Unable to open Database 'Northwind'. The database is in the middle of the restore operation. This is because we specify the option "with norecovery" 6. Restore transaction logs to 23:35 Restore log northwind from nwlogbakup with recovery, stopt = & # 392003; -08-02 23:35 '6. Execute Select * from Employees At this time, you can only see the first newly inserted record - View Backup Information Restore Filelistonly from Disk = 'C: / Program Files / Microsoft SQL Server / MSSQL / Backup / DB1_BACKUP' DBCC SQLPERF (Logspace) - View Logs Space 1 * You should back up the log backup log database name to disk = 'c: / aalogbak' with init, no_truncate like this, your log is empty, the log is also emptied, you can compress the maintenance log backup sequence, schedule The Backup LOG statement occurs at intervals to make the transaction log grow up to more than expected size. If you do not want the log or if the log is nothing to do, you can consider the following implementation: 1. Backup log dbname with [no_log | truncate_only] [no_truncate] / * no_log | Truncate_only does not have to back up the replication log is deleted. And truncated the log. This option will release space. Because the log backup is not saved, there is no need to specify the backup device. NO_LOG and TRUNCATE_ONLY are synonymous. After using the NO_LOG or TRUNCATE_ONLY backup log, the changes recorded in the log cannot be recoverable. For recovery, do Backup Database immediately. NO_TRUNCATE allows you to back up the log when the database is corrupted. * / 2. Shrinkage database file SELECT FILEID, FileName from sysfiles use dbname dbcc shrinkfile (2, 10) - shrink fileID = 2 files to 10 MB or Enterprise Manager - Right click on the database you want to compress - All Tasks - Shrink Database - Shrink File - Select Log File - Select to shrink to XXM in the shrink mode, which gives a minimum number of Mixes that are allowed to shrink, and enter this number directly. It can be 3: Delete LOG 1: Separate Database 2: Delete Log File 3: Additional Database This method is new log, only more than 500 k 4. You can configure your recovery model for Simple Alter Database DB_NAME SET Recovery Simple 5.exec sp_dboption 'Test2', 'AutoShrink', ' ON '5 Expand the server group and then expand the server.
