How to restore a database

In local operations and debugging, everything seems to be under control. However, once submitted to the server, everything is obviously unfamiliar. Because you are not familiar with SQL, you can simply click Select, Update, Delete, and Insert. Yesterday, I accidentally deleted a table with a small amount of data but not in the scope of manual processing.

Because I know that SQL Server has such a log, I am very calm after the event, within an hour of the incident. An hour later, I found that I could not control the incident.

I found a way to restore data online. It is necessary to give it a sigh of relief.

 

Question 1:

Neither the remote client nor the Server Client can perform operations other than 'add, delete, query, modify 'on the database (assuming the database is called Test), such as separation, offline, and restoration... error message:

E. g.1"The Test database cannot be separated because it is currently in use ."

E. g.2"Because the database is in use and does not have exclusive access to the data, the operation is terminated unexpectedly"

In other words, you cannot block others' access. But we just want to do this... we have to say goodbye to it.

 

The method of saying goodbye is to turn off all the processes that access the database Test.

Createproc killspid (@ dbnamevarchar (20 ))
As
Begin
Declare @ sqlnvarchar (500)
Declare @ spidint
Set @ SQL = 'Clare getspid cursor for select spid from sysprocesses where dbid = db_id (''' + @ dbname + ''')'
Exec (@ SQL)
Open getspid
Fetchnextfrom getspid into @ spid
While @ fetch_status <>-1
Begin
Exec ('Kill '+ @ spid)
Fetchnextfrom getspid into @ spid
End
Close getspid
Deallocate getspid
End

-- Usage
Use master
Exec killspid 'database name'

PS:

I understand the code in this way and define a stored procedure for killing and accessing database processes. Write a piece of code to get the process ID and cyclically kill each process. Finally, call the stored procedure.

Although I do not understand the stored procedure, it means this.

Question 2:

How can I restore data without backing up a database?

There is a software called Log Explorer which can return to any past time point based on the database logs.

View Code

Log Explorer for SQL Server

Open log explorer file => attach log file-> Select Server and logon mode-> connect->

Select database> attach> browse> view log> In the left-side dialog box to view the log record.

Click View DDL Commands. There are many drop table Commands.

Click the "undo" button below to generate the table structure Statement (create table ....)

Click the "Salvage" button below to generate the insert Statement (insert into... values ....)

To restore the file, right-click the log record undo transation and choose Save file name and path> open the file and run it in the query analyzer.

T-SQL code.

For example, if log is delete table where..., the generated file code is insert table ....

Several Problems in log explorer

Complete database difference and log backup

Deleted non-active entries in the transaction log are selected during Backup.

When I try to view logs with Log explorer

Prompt No log recorders found that match the filter, wocould you like to view unfiltered data

Select yes and you will not be able to see the record.

If you do not select to delete non-active entries in the transaction log

Log explorer can be used to view logs.

Log Explorer for SQL Server is a good thing, but it cannot solve my problem at all. The database is on the server, and the school won't let me install a software on the server. I have also confirmed that this software must be installed on the server. Therefore, this is a bubble for me. But it does not mean this is not a powerful tool.

 

Finally, I used a very painful solution.

1. Separate the database and back up a Test;

2. Attach the database back and restore it to a State that was a long time ago using the built-in recovery method;

3. Copy the required table to the backup database;

4. Mount the database backed up and delete the original database.

 

This is a very painful method and it happens that the table has not been changed.

The first time you know the importance of DBA.

My approach is a very accidental one. I hope you can share with us some practical solutions to the problem. Of course, there is absolutely no error in backing up at intervals.

 

Method

In addition, we found a more technical and feasible method.

1. If there is a full-Database Backup (or there are multiple differential backups or incremental backups) before the misoperation, the first thing to do is to perform a log backup.

(If you set trunc. log on chkpt to 1 to prevent the log file from becoming larger, then you will die)

Backup log dbName to disk = 'filename'

---- Note: log backup is used!

2. Recover a full-database backup. Use with norecovery. If there are other differences or incremental backups, recover them one by one.

restore database dbName from disk='fileName' with norecovery

3. Restore the last log backup, that is, the log backup you just created. Specify the recovery time point to the time before the misoperation.

restore log dbName from disk='fileName'
with stopat='date_time'

 

Complete code

View Code

---- 1. If a full-Database Backup (or multiple differential or incremental backups exist) exists before the misoperation ), the first thing to do is to perform a log backup (if trunc is set to prevent the log file from becoming larger. log on chkpt. if option is set to 1, you will die)
Backup log dbName to disk = 'filename'

---- Note: log backup is used!
---- 2. Recover a full-database backup. Be sure to use with norecovery. If there are other differences or incremental backups, recover them one by one.
Restore database dbName from disk = 'filename' with norecovery
---- 3. Restore the last log backup, that is, the log backup you just created. Specify the recovery time point to the time before the misoperation.
Restore log dbName from disk = 'filename'
With stopat = 'date _ time'

After verification, this is technical! I am a child of "dunai's Tun 'tag...

 

References:

Http://blog.163.com/liushui19860112@126/blog/static/132373046201052841232492/

Http://blog.csdn.net/voyage_mh1987/article/details/4133798

Http://kenwu.blog.hexun.com/32603385_d.html