I have seen many problems on the Internet about how to deal with large data log files. I have also seen many unrealistic practices. Some people are anxious to handle wrong operations but there is no log,
In fact, the log files in the database record the traces of database update and delete operations. They also prevent database accidents (such as hacker attacks and System database damages) we can ensure that the database can be restored to a specific time point,
So it is important !! We should back up the database logs!
Let's analyze the types of database faults:
I. Internal transaction faults
That is, we cannot guarantee the consistency of transactions. For example, you have two bank accounts. For some reason, you need to remove 1 million from one bank account to another,
However, due to a bank database failure, one of your bank accounts is indeed less than 1 million, but the other account does not increase by 1 million, what do you do now: (of course, we can add rollback to the corresponding transaction to force rollback .)
Ii. system faults (software faults-soft crash)
System failure refers to any event that causes the system to stop running and is reluctant to restart the system. such as specific types of hardware faults (CPU faults), operating system faults, DBMS code faults, and sudden power outages. Such faults affect running transactions,
But the database is not damaged. at this time, all the data in the memory is lost, and all the transactions are terminated abnormally. the results of some unfinished transactions may have been sent to the physical database, so that the data in the database is in an incorrect state, to ensure data consistency
Yes, you need to clear the transaction tampering with the database!
At this time, after the system restarts, it forcibly revokes all unfinished transactions for all committed transaction redo and all abnormal finally transaction Undo!
Iii. Media fault (hardware fault -- hard crash)
This is an external storage fault, such as disk damage, magnetic head collision, and instantaneous strong magnetic field interference. This type of fault is the least likely, but the most destructive.
Iv. Computer Viruses
The principle of recovering the above faulty database is very simple, that is, the two words "redundancy". Now let's talk about how to establish the "redundancy" technology!Backup
Now let's take a look at the backup technology of SQL Server. All databases have the same backup purpose, and SQL server also has the same purpose to prevent data loss!
This chapter includes the following content:
1. in SQL Server, we can set the database recovery model to the full recovery model, large-capacity insertion model and simple model,
2. When do we need to back up the database?
3. There are several database backup methods
4.0 demonstrate several backup methods (preparations before backup)
5. Several backup policies
The above content can be found in book on line. If necessary, I will write it again :)
Exercise: Example: 0. First, you must have a full backup (otherwise, you will not be able to successfully back up the log file :))
Backup database northwind
To nwbakup1, nwbakup2
With init
-- View backup information
Restore filelistonly from nwbakup1, nwbakup2
You can also right-click the SQL Server-northwind-in the Enterprise Manager-all tasks-back up the database, and then perform corresponding operations in the graphic interface. This is very simple. 1. Use northwind
Insert employees (lastname, firstname) values ('aaa', 'aaa ')
Delete from employees where lastname = 'aaa'
Write down the current time
23:33
2. Input in 3 minutes
Use northwind
Insert employees (lastname, firstname) values ('bbb ', 'bbb ') 3. Now select * from employees will be able to see these two records 4. Back up logs, select the northwind database, all tasks, and backup database.
In the dialog box, select transaction log backup as the backup type, select an existing backup device, and append it to the media.
Click OK to back up logs with two new records.
Backup log northwind to nwlogbakup 5. The following shows how to restore
Restore database for all tasks
In the dialog box, select time point to restore, enter the time you recorded, and click OK to restore.
Restore database northwind
From nwbakup1, nwbakup2
With norecovery Use northwind
Server: Message 927, Level 14, status 2, Row 1
The database 'northwind' cannot be opened '. The database is being restored.
This is because the option "with norecovery" is specified" 6. now resume the transaction log
Restore log northwind
From nwlogbakup
With recovery,
Stopat = & #392003;-08-02' 6. Run select * from employees. Only the first newly inserted record is displayed.
-- View backup information
Restore filelistonly from disk = 'C:/program files/Microsoft SQL Server/MSSQL/backup/db1_backup'
DBCC sqlperf (logspace) -- view the log Space
1 * you should back up logs
Backup log database name to disk = 'C:/aalogbak 'with init, no_truncate In this way, your logs are cleared and backed up. You can compress the logs.
When maintaining the log backup sequence, the scheduled backup log statement occurs at intervals so that the transaction log does not grow to exceed the expected size. If you do not want logs or the logs are useless, consider the following implementation scheme: 1. backup log dbname with [no_log | truncate_only] [no_truncate]
/*
No_log | truncate_only If you do not need to back up and copy logs, you can delete the inactive logs and truncate the logs. This option releases space. Because log backup is not saved, you do not need to specify a backup device. No_log and truncate_only are synonymous. Changes recorded in the log cannot be recovered after logs are backed up using no_log or truncate_only. To recover the database, execute backup database immediately. No_truncate Logs can be backed up when the database is damaged.
*/ 2. Shrink database files
Select fileid, filename from sysfiles Use dbname
DBCC shrinkfile () -- contract the file with fileid = 2 to 10 MB
Or
Enterprise Manager-right-click the database you want to compress-all tasks-shrink database-shrink file-Select log file-select shrink to xxm In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK. 3: delete log
1: Database Separation
2: delete log files
3: attach a database
This method generates a new log with a size of more than 500 kb. 4. You can configure your recovery model to simple first.
Alter database db_name set recovery simple 5. Exec sp_dboption 'test2', 'autoshrink', 'on' 5
Expand the server group and then expand the server. Expand the "Database" folder, right-click the database from which you want to delete data or log files, and then click the "properties" command. To delete a data file, click the "General" tab. To delete a log file, click the Transaction Log tab. In the "file name" column, click the arrow next to the file name to be deleted, and then press Delete. A cross cursor appears next to the file name, indicating that the file will be deleted. Only empty files can be deleted. |
