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!