2

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?

2
  • 2
    Those terms are logically synonyms. To recover, you need to restore backups of different types. So recover == restore. copy == move == restore too (although you can use attach method as well for just moving). You can (and often do) keep at least some backup files online while also copying them offline or/and to a DR site. – SMor Dec 30 '20 at 3:15
  • 1
    @SMor, that was my first thought too, and I believe the reason is that as DBAs we often use both terms interchangeably, but if you check the doc I mentioned in my answer, you'll see that SQL Server uses them consistently to distinguish two different processes. I had to read that doc a few times to catch the difference. – Ronaldo Dec 30 '20 at 10:53
2

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.

7
  • Many thanks both! Sorry Ronaldo, can you please explain what do you mean by "bringing a consistent database back online"? Why does it have to be online? – learn9909 Dec 30 '20 at 9:25
  • 1
    @learn9909, online is the term used to refer to a database that is in a state that can be used, e.g., you can query, insert, update, delete data from a database that is online. see the Database States doc to better understand the possible states a database can be. – Ronaldo Dec 30 '20 at 10:46
  • Many thanks @Ronaldo. Sorry in this definition "Database recovery is the process of restoring the database to a correct (consistent) state in the event of a failure. " it says that recovery is the process of restoring, can you please explain an example that recovery and restore are different? – learn9909 Dec 30 '20 at 11:05
  • 1
    @learn9909 Yes, I'm gonna update my answer with that example, but where did you get that definition from? Please, add the source link. Did you see my comment answering SMor? DBAs use those terms as synonyms, but SQL Server docs (at least the ones explaining Restore an Recovery) don't. – Ronaldo Dec 30 '20 at 11:38
  • 1
    @learn9909, example added ;) – Ronaldo Dec 30 '20 at 14:02
1

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-

1
  • Many thanks Tibor for the great explanation!!! – learn9909 Dec 30 '20 at 14:24
1

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.

1
  • Many thanks. So are restoring and recovering a database different? – learn9909 Jan 9 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.