Restore the database and the database prompts that the database is being restored. How to deal with it

When restoring the database, it prompts that the restoration is successful, but the database is shown in the database list as being restored:

Execute this command. Can be:

RESTORE DATABASE EnterPriseBuilding WITH RECOVERY

1. At least one previous database full backup was accidentally deleted.

2. The recovery mode of the database is "Full".

1. Setting the conditions required for restoration

Using the command is through the transaction log of sqlserver and a complete backup of the database before accidental deletion To restore, so in the maintenance plan wizard of sqlserver2012, you need to create a full backup, differential backup and transaction log, as follows

and in the database properties and option settings , set it to a full backup, as shown below

After doing the above two settings, it will be very easy to retrieve the data after the database is accidentally deleted. Now let’s talk about how to restore sqlserver data to the point of failure.

2. Restore command

Restore is mainly divided into four steps:

1. After a failure occurs, first execute the backup transaction log command, here AdventureWorks is used as the database name. The command is as follows:

BACKUP LOG AdventureWorks TO DISK = 'C:\SQLServerBackups\AdventureWorks_transcationlog.bak' 
   WITH NORECOVERY;

2. Restore data from full backup

RESTORE  DATABASE [QASupervision] FROM DISK='M:\Database\OA\AdventureWorks_Fullbackup_2014_03_18_010002_0155764.bak' 
WITH  NORECOVERY, REPLACE

3. Restore data from differential backup

RESTORE DATABASE [QASupervision] FROM DISK='M:\Database\OA\AdventureWorks_diffbackup_2014_03_18_020002_0155764.bak' WITH NORECOVERY, REPLACE

4. Restore data from transaction log to a certain Before the time point

DECLARE @dt datetime   
SELECT @dt=DATEADD(HOUR,-16,GETDATE())   
select @dt
RESTORE LOG [QASupervision] FROM DISK='C:\SQLServerBackups\AdventureWorks_transcationlog.bak'  WITH  STOPAT=@dt,RECOVERY

5. Restore the database. If the database prompts that it is being restored, just execute this command.

RESTORE DATABASE AdventureWorks WITH RECOVERY

The above is the detailed content of Restore the database and the database prompts that the database is being restored. How to deal with it. For more information, please follow other related articles on the PHP Chinese website!