Several methods for restoring SQL Server logs to databases

SQL Server log recovery

To restore logs, the most important thing is:

1. A complete backup is required, and the backup must be performed before the data is modified or deleted.

2. log backup is performed after data is updated and deleted. The log backup is used to restore the previous data.

Purpose: to restore the deleted table

Visual operation steps in SQL Server Management:

1. Create a database and complete backup:

The code is as follows:	Copy code
-- Create a test database Create database Db GO -- Back up the database Backup database Db to disk = 'C:/db. Bak' WITH FORMAT GO

 

2. Create an empty table

The code is as follows:	Copy code
-- Create a test table Create table Db. dbo. TB_test (ID int)

3. Delete the empty table just created. Suppose the table is deleted by mistake.

The code is as follows:

Copy code

-- Assume that the table Db. dbo. TB_test is deleted by mistake. Drop table Db. dbo. TB_test In this step, we want to restore the deleted TB_Test table. Remember the time before the table is deleted, which will be used later. At this time, you need to back up the log, you can use the SQL Management interface to operate backup, you can also use T-SQL backup Backup log Db to disk = 'C:/db_log.bak 'WITH FORMAT GO

4. Restore the database. You can replace the original database or restore it to a new database db1. here, the new database db1.

For interface operations:

"Task"-"restore"-"database ":

On the "general" selection page, select the complete backup: db. bak,

Target database: DB1

Under "recovery status", select 2nd items "do not perform any operations on the database, and do not roll back the committed transactions .." Click "OK". The status of the DB1 database changes to "restoring .."

Next, right-click the DB1 database and choose "restore"> "transaction log". Select the transaction log backed up after the table is deleted: db_log.bak,

Select a time point below, which is the time before the table is deleted. Click OK to restore the table. The deleted table is returned.

Complete script:

The code is as follows:

Copy code

-- Create a test database Create database Db GO -- Back up the database Backup database Db to disk = 'C:/db. Bak' WITH FORMAT GO -- Create a test table Create table Db. dbo. TB_test (ID int) -- Delay 1 second, and then perform subsequent operations (this is because the SQL Server has a time precision of up to 3% seconds. If there is no delay, the restoration operation to the time point May Fail) Waitfor delay '00: 00: 01' GO -- Assume that the table Db. dbo. TB_test is deleted by mistake. Drop table Db. dbo. TB_test -- Save the time when the table was deleted SELECT dt = GETDATE () # GO -- After the delete operation, the database. dbo. TB_test table cannot be deleted. -- The following shows how to restore the accidentally deleted table Db. dbo. TB_test -- First, back up transaction logs (transaction logs can be restored to the specified time point) Backup log Db to disk = 'C:/db_log.bak 'WITH FORMAT GO -- Next, we need to restore the full backup first (the restoration log must be performed on the basis of the full backup) Restore database Db from disk = 'C:/db. Bak' with replace, NORECOVERY GO -- Restore the transaction log to before the delete operation (the time here corresponds to the deletion time above, and is earlier than the deletion time DECLARE @ dt datetime SELECT @ dt = DATEADD (MS,-20, dt) FROM # -- get a time earlier than the time when the table was deleted Restore log Db from disk = 'C:/db_log.bak 'with recovery, STOPAT = @ dt GO -- Check whether the table is restored. SELECT * FROM Db. dbo. TB_test /* -- Result: ID ----------- (The number of affected rows is 0) --*/ -- Test successful GO -- Delete the Test environment. Drop database Db Drop table #

SQL Server 2005 large-capacity log recovery

The code is as follows:

Copy code

-- On the "database attributes" page of SSMS, you can view the recovery mode specified for a given database. You can also query the sys. database Directory view. The basic syntax is as follows:   SELECT name, recovery_model_desc FROM sys. databases   -- Use the alter database statement to configure the recovery mode. The basic syntax is as follows:   Alter database <database_name>   Set recovery full | SIMPLE | BULK_LOGGED   -- As mentioned above, we recommend that you use the full recovery mode for the database in the production environment because it provides the most recoverable configuration. If you use the large-capacity mechanism to regularly import data, you can temporarily change the database recovery mode to the large-capacity log mode to achieve better high-capacity load performance. However, after the import process ends, the database should be restored to the full recovery mode.   -- Exercise: change the database recovery mode -- This exercise changes the database recovery mode to large-capacity logs to achieve good performance of large-capacity log operations, and then restores to the full recovery mode.   -- 1. Execute the following alter database statement to set the DATABASE recovery mode of the AdventureWorks DATABASE to the large-capacity log recovery mode. (Make a full backup of the database before changing the recovery mode .)   -- Note that you shoshould create the C:/Backup folder at Operating System level before running this backup.   Backup database AdventureWorks to disk = 'C:/Backup/AdventureWorks. Bak'   GO   -- Change the Recovery Model to Bulk Logged   Alter database AdventureWorks   Set recovery BULK_LOGGED   -- 2. after performing the large-capacity log operation, enter and run the following alter database statement to change the recovery mode back to the full recovery mode. Then, execute another full DATABASE backup to back up the data just loaded.   Alter database AdventureWorks   SET RECOVERY FULL   -- Perform a Full database backup   Backup database AdventureWorks to disk = 'C:/Backup/ AdventureWorks. Bak'   GO

SQL2008 restore a database based on logs

The code is as follows:

Copy code

-- Next, we need to restore the full backup first (the restoration log must be performed on the basis of the full backup) Restore database yp from disk = 'd:/YP. bak 'with replace, NORECOVERY GO -- ** The "NORECOVERY" here is critical. If you manually restore the initial database, an error may be returned when you use the log to restore the database .** Www.111cn.net -- Restore the transaction log to [50 minutes ago] before the delete operation (the time here corresponds to the deletion time above, and is earlier than the deletion time DECLARE @ dt datetime SELECT @ dt = DATEADD (n,-50, GETDATE ()) -- Get a time earlier than the time when the table is deleted: YP_LOG.bak is a log backup after misoperation Restore log yp from disk = 'd:/DATAZY/YP_LOG.bak 'with recovery, STOPAT = @ dt GO

 
This feature was previously used only in books and has never been used. Today, I suddenly made a mistake and accidentally deleted my data! This is my hard work for half a month! So I accidentally deleted it! Later, I thought about this function and found it online. The result was restored to the database before the misoperation! Haha.
 
The following conditions should be met:
1. The database has the original status [backup/new database].
2. After the original status, the log has not been deleted.
3. Remember The Time of Your misoperation.
If you have deleted logs or are not in the most vulnerable state, you should have no play!