when we restore and when we recover a database?

Question

Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.

Question: Why copying/moving a database to a new location is a restore not a recover?

Answer 1

Because, as the Restore and Recovery Overview doc says:

Recovery is the process used by SQL Server for each database to start in a transactionally consistent - or clean - state.

The recovery process is responsible for bringing a consistent database back online, and the database doesn't have to be moved or restored from a backup for that action to happen (a crashed database would trigger a recovery before coming back online). After a backup is restored a recovery should happen to guarantee a consistent database. Restore and recovery walk close together.

---

If you have a SQL Server in your desktop (or a lab environment that you can restart), follow these steps:

Recovery:

1. Restart SQL Server service;
2. Connect to SQL Server and check SQL Server Logs: Management > SQL Server Logs;
3. On the Current log, look for a message like this:

   Recovery is complete. This is an informational message only. No user action is required.

4. You didn't do any restore of a backup, yet a recovery just happened because SQL Server does it before bringing a database online.

Restore:

1. Create a folder C:\BackupSQL on the computer SQL Server is installed;

2. Connect to SQL Server and run the following:

   EXEC sp_cycle_errorlog; --this will help you better see the relevant information for your question 
   
   CREATE DATABASE Lab;
   
   BACKUP DATABASE Lab TO DISK = 'C:\BackupSQL\Lab.bak';
   BACKUP LOG Lab TO DISK = 'C:\BackupSQL\Lab.trn';
   
   DROP DATABASE Lab;
   
   RESTORE DATABASE Lab FROM DISK = 'C:\BackupSQL\Lab.bak' WITH NORECOVERY;
   

3. Check the current log to see messages like:

   The database 'Lab' is marked RESTORING and is in a state that does not allow recovery to be run.
   
   Parallel redo is started for database 'Lab' with worker pool size [4].
   
   1 transactions rolled forward in database 'Lab' (5:0). This is an informational message only. No user action is required.
   
   0 transactions rolled back in database 'Lab' (5:0). This is an informational message only. No user action is required.
   
   Restore is complete on database 'Lab'. The database is now available.

4. As you can see, you issued a RESTORE and SQL Server executed the restore and a recovery process to guarantee a consistent database in the end of your request.

Consistency:

Imagine a situation where you owe me U$$ 500.00 and you're going to transfer the money from your bank account to mine:

1. Connected to SQL Server run these commands: USE Lab;

   CREATE TABLE BankAccount(userName varchar(50), myMoney money);
   
   INSERT INTO BankAccount(userName, myMoney)
   VALUES ('Ronaldo', 1000.00), ('learn9909', 1000.00);
   
   SELECT userName, myMoney FROM BankAccount;
   

2. As you can see, each of us has U$$ 1000.00;

3. Run the commands to transfer the money:

   BEGIN TRAN
       UPDATE BankAccount SET myMoney = myMoney - 500.00 WHERE userName = 'learn9909';
       WAITFOR DELAY '02:00'; --it waits for 2 hours before proceding
       UPDATE BankAccount SET myMoney = myMoney + 500.00 WHERE userName = 'Ronaldo';
   COMMIT
   

4. Open another tab on Management Studio and execute this query:

   SELECT userName, myMoney FROM BankAccount WITH (NOLOCK);
   

5. You can see the U$$ 500.00 was reduced from your account, but it hasn't been added to mine yet;

6. Now let's cause a database crash by abruptly ending SQL Server service: start menu > run > taskmgr. Look for the process called something like SQL Server Windows NT and end it;

7. That transaction was interrupted in the middle and it won't be finished when SQL Server service is available again. You're in big trouble if it stays like that because U$$ 500.00 was taken from your account and you still owe me U$$ 500.00 and I can prove it with my bank statement. To avoid inconsistencies like that SQL Server executes the recovery process before bringing a database online.

8. To start SQL Server service again and check our bank accounts start menu > run > services.msc, look for SQL Server service and start it;

9. Query the table again and feel relieved that the recovery process did it's job and put the database in a consistent state where that half transaction was undone:

   SELECT userName, myMoney FROM BankAccount;
   

10. You can check SQL Server Log again and see that process, but once again, no backup was restored, only a recovery process occurred.

Answer 2

Don't expect too much consistency when it comes to this terminology. To many people, restore and recovery are used in the same way. However, if we try to be formal:

Backup and restore. You know what backup is, I assume. Restore is when you restore from a backup that you produced earlier. Nothing strange here.

Recovery: Imagine that your SQL Server crashes, possibly in the middle of a running UPDATE statement. You expect that when SQL Server comes backup, you only have committed transactions. Everything that wasn't committed need to be rolled backup, at startup of your SQL Server. This is recovery. Also known as REDO and UNDO, crash recovery and automatic recovery-

Answer 3

To restore a database means to load the data from a previous backup. Recovery is the process of making the database consistent by rolling back uncommitted transactions or rolling them forward if information is available on backed up transaction logs.