Sql-server – when we restore and when we recover a database

backuprecoveryrestoresql server

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?

Best Answer

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.

Related Question