Category Archives: Database Recovery

How to migrate SQL Server from one machine to another

I recently was involved in a major migration project which included the move of few database production servers from one location to another. It was a very big project which implied a lot of inner coordination between teams and scheduling, so that the impact for the end-users would be minimal.

In case you are in the position of moving an SQL Server from one machine to another make sure you stick to the below presented checklist. The main purpose of the migration is to create a clone SQL Server with the same configuration, databases up to date, security and SQL Agent jobs in place. For this I always chose to restore both master and msdb along with all user databases. It seemed a lot easier than moving users/passwords, resolving orphaned users, updating default databases, updating system properties (sp_configure), migrating jobs, SQL Agent operators, recreating DB mail profiles, change data capture functionality, restoring partitioning information, and whole other specific database options that where once enabled.

After the migration everything must go smoothly and with no errors, otherwise, the migration didn’t go successfully. You have to make sure you cover all points and when you make the switch in connection strings everything is completed from DBA side of view.

Please if you find there is something to be added to the list below, feel free to comment and I will update this post.

I. Steps to do on the current Production server – [Server A]

  1. Check SQL Server properties – These must stay the same on the future server.

    use master
    select SERVERPROPERTY ('Collation')
    select SERVERPROPERTY ('Edition')
    select SERVERPROPERTY ('InstanceName')
    select SERVERPROPERTY ('ProductVersion')
    select SERVERPROPERTY ('ProductLevel')
    select SERVERPROPERTY ('ServerName')
  3. Check global configuration settings – These must stay the same on the future server.

    use master
    sp_configure 'show advanced options' , 1
  5. Check Databases Integrity
    It is necessary in order to be sure that restored backups on migrated server will be valid.

  7. Note down model database growing options.
    This setting is important for every new created database. My strong advice is that you never leave the default setting because it can be a big cause for poor performance. The default setting is 1 MB per data file and 10% per log file – imagine what it means for a database to allocate MB after MB if it needs to allocate say 50 MB or grow a log file that is 10 GB in size.

  9. Note down physical path for master, model, msdb, tempdb databases data and log files.

    use master
    select name, filename from sys.master_files where database_id in (1,2,3,4) order by database_id
  11. Note down physical path for mssqlsystemresource data and log files.
    SQL 2008: It should be in :\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\
    For SQL 2005: same as master database location.
    Query to run on SQL 2008 and upper versions in order to find the physical location:

    use master
    select name, filename,dbid from sys.master_files where database_id in (32767) 
  13. Perform Full backup on master and msdb

II. Steps to do on the future Production server – [Server B]

  1. Install same edition/version of SQL Server on the new machine.
    Keep in mind the following when installing:

    • Make sure the new server has mounted the same drive letters where system databases reside on old SQL Server. Otherwise, after the restore of master SQL will not be able to start because SQL will want to start the rest of system databases on physical paths from [Server A].
    • If you want to restore system databases then you need to keep the same edition (Standard, Entreprise, Business Intelligence) and version (ex: 2008 RTM, 2008 SP1, 2008 R2 RTM etc.). You cannot restore a backup of a system database (master, model, msdb) on a server build that is different from the build on which the backup was originally performed. An workaround would be, to replace manually the system databases files copied from [Server A] to [Server B]. This can be done only if the migrated server can be stopped. If not, then you must follow the procedure with “restore database….”
    • If you want to restore only user databases, then you can install the same or upper edition/version. If you install a lower edition/version than the current Production one, you might not be able to restore some of the databases having higher builds features which won’t be supported on lower builds.
    • Keep the same collation when installing SQL Server.
    • Create a Domain/Windows user account that will be used to start SQL Services.
  3. Backup system databases – master, model, msdb – and put in a safe location… in case you want to recover them.
  4. Stop SQL Server. Copy all system databases files and add them to the safe location.
  5. Create the physical locations for master, model, msdb, tempdb, mssqlsystemresource databases noted down at steps I.5 & I.6
  6. Give to the user running SQL Full security rights on the new folders created.
  7. Copy system databases backups made on [Server A] to [Server B] (step I.7).
  8. Stop SQL Server.
  9. Copy existing model, msdb and mssqlsystemresource database files from the installed location on [SERVER B] to the new created ones (step II.4). Afterwards you will be able to change the location for these databases. For now SQL will need old locations in order to load msdb, model, tempdb and mssqlsystemresource.
  10. Start SQL Server in single user mode. From an elevated cmd (started with administrator rights), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn”

    There where cases when other services like SQL Agent or SQL Reporting took the single one connection and prohibited the administrator to restore master. This error is given:

    Reason: Server is in single user mode. Only one administrator can connect at this time.
    Before starting the SQL in single user mode, make sure you have stopped every SQL service and there is no one that will make a default connection to it (application, reporting)

    cd [drive]:\folder_name 
    sqlservr.exe -c -m
  11. Restore master database from the backup copied from [SERVER A] using a NEW cmd line started with administrator permissions or using a DAC (Dedicated Administrator Connection) connection.

    -- connect to SQL
    -- make sure you are connected to right server: 
    select @@servername 
    RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE;
  12. Exit the cmd prompt and Start SQL from Services (in multi-user)
  13. Now the system databases (except master) will be loaded from the new created paths. Check new files location for system databases. master will point to the right location. msdb, tempdb and model must be changed. If you will restore msdb you can specify at that time, the new location for the files. So here I will show how to move model and tempdb. For msdb is the same.

    select * from sys.master_files 
    use master
    Alter database tempdb modify file
    (name=tempdev, filename='[drive]:\new_location\tempdb.mdf')
    Alter database tempdb modify file
    (name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf')
    Alter database tempdb modify file
    (name=templog, filename='[drive]:\new_location\templog.ldf')
    Alter database model modify file
    (name=modeldev, filename='[drive]:\new_location\model.mdf')
    Alter database model modify file
    (name=modellog, filename='[drive]:\new_location\modellog.ldf')
  14. Stop SQL Server and move the model files from old location to new location. tempdb will be re-created on the new specified location at every SQL restart so no need to move the files.
  15. Start SQL and make sure that the system database point to the right locations.

    select * from sys.master_files 
  16. [OPTIONAL] Restore msdb database. I personally encountered problems running Database mail after msdb restore on SQL 2008 R2. I ended up leaving the msdb orginal files and migrated all jobs by scripting, created msdb.dbo.cdc_jobs table (because cdc was in place as well) and re-configured database mail.
  17. Remove folders created at step II.4.
  18. Test some SQL users for connection.
  19. Linked Servers will not work because their credentials where encrypted with Service master key from originated server. In order to fix this you need to backup service master key from [Server A], give permission on the file to your user and copy to [Server B] to restore it.

    -- on [Server A]
    BACKUP SERVICE MASTER KEY TO FILE  ='[Drive]:\share\master_key' ENCRYPTION BY PASSWORD ='password'
    -- right click on file, properties, security, permissions: Give full permissions to your user. 
    -- on [Server B]
    RESTORE SERVICE MASTER KEY FROM FILE = '[Drive]:\master_keymaster_key' DECRYPTION BY PASSWORD = 'password' force
  20. Change the server name in system tables. @@servername variable will point to old server. It must be changed.

    -- check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found.  
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    EXEC sp_helpserver 'SERVER A'
    SELECT srvname FROM sysservers where srvid = 0
    -- DELETE old references to old servername.
    EXEC sp_droplinkedsrvlogin 'SERVER A', null
    EXEC sp_dropserver  'SERVER A'
    -- ADD new server name: [SERVER B]
    EXEC sp_addserver [SERVER B] , 'local' ,'duplicate_OK' 
    EXEC sp_addlinkedsrvlogin 'SERVER B', 'true'
    -- RESTART SQL. [SERVER B] will replace the old server name. 
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    SELECT srvname FROM sysservers where srvid = 0
  21. On versions below SQL 2008, you have to update also the originating server name from sysjobs as well. On higher versions, the reference in sysjobs is made by server_id which is always 0.

    SELECT * FROM msdb.dbo.sysjobs
    UPDATE msdb.dbo.sysjobs SET originating_server = @@SERVERNAME WHERE originating_server <> @@SERVERNAME

    III. Remaining Steps to do on the current Production server – [Server A]

  22. Perform Full backups for all user databases. It is a good idea to use backup compression and backup verification (RESTORE VERIFYONLY)
  23. Perform Differential backups.
  24. Copy all backups on [SERVER B].
  25. Restore Full backups and differential backups on [SERVER B] with ‘NO RECOVERY’ option.
  26. Perform Transactional backups.
  27. Copy all tran backups on [SERVER B] and restore them using with ‘Recovery’ option.
  28. And a last step, after you restore all user databases, keep in mind to change databases owners to ‘sa’ user, otherwise you will receive errors of executing sys schema objects like these ones:

    The EXECUTE permission was denied on the object ‘’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

  29. use Database_Name
    EXEC sp_changedbowner 'sa'

Restore SQL 2008 R2 Database on SQL 2008 SP1 instance

It has been a long time since I wrote a new post, but now I ‘m back after a very busy period and I would like to share with you a recent problem I had with a restore from a higher version of SQL Server – SQL 2008 R2 to a lower version – SQL 2008 SP1.

If you search the internet, you will be sad to find out that:

  1. The restore from a database backup made on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  2. The attaching of data and log files created on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  3. Scripting entire database (SQL 2008 R2: Tasks –> generate scripts –> script entire database –> advanced: select to script schema and data) and executing on destination server can get you what you need but it works only if the database is not very big and the output of the sql file is in reasonable size parameters.
  4. Another scripting method is to do it step by step (object by object): create database, export tables with export/import tool (easier because you don’t have to deal with constraints), create primary keys, foreign keys, default and other check constraints, indexes, views, stored procedures, migrate users security, cdc feature, triggers… etc. As you can see it is time consuming and you can’t do a single mistake. If the database was as big as I had to deal with (over 60 tables) then it can become a nightmare.

Gladly, there is one single possibility of “restoring” the database and having all of needed objects inside in a very elegant way 🙂
The duration depends on the database size. If you have do it relatively urgent like I did and can’t afford to go through the above alternative steps (3 and 4), please read further. By the courtesy of one of my readers, as he pointed out: very important to know is also that this method will work if the database migrated doesn’t have encrypted procedures. To use this method, you need to remove encrypted procedures and recreate them after the migration. I couldn’t find anywhere this tip even if this is an SQL feature and I thought many can find it usefull. On MSDN, the solution presented below that works for a database downgrade is treated only as an database upgrade option. (Using the Copy Database Wizard).

Things that are good to now

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following:

SELECT @@version;

will display information about the SQL Server installation build, meaning the whole engine. The database version however is not the same.
Running this will get you the database version:

USE master
SELECT DatabaseProperty ('DATABASE NAME', 'version');

Database compatibility level determines how certain database behaviors work. This is set on database level. You can have a lower database compatibility level than the default one. On 2008 instances the default compatibility level is 100, and it can be 90 or 80 if you need the database to be compatible with a lower SQL Server edition. Setting a lower compatibility version does not allow the database to be restored on a lower SQL Server version.

In the following example, I will detail how you can use the Copy Database Wizard to migrate a database created on SQL 2008 R2 to SQL 2008 SP1, meaning to downgrade it from database version 661 to 655.

As a note to this, and by the courtesy of one of my readers I have to add that this option is only available if you don’t have encrypted stored procedures inside your database. If you do you will receive an error and will need to escalate this problem: either drop the procedures and re-create them after the copy database process described below, either un-encrypt the procedures before the copy database process and re-encrypt after.

Copy Database method

  1. Step 1 – Select source and destination servers
  2. Start by selecting the database that you want to downgrade. Right click on it and go to: Tasks –> Copy Database… Press Next. In “Select Source Server” window specify the source server name and the authentication type. In my example I used Windows. If you use SQL authentication, it should be with a user that has sysadmin role assigned. Both Windows and SQL need sysadmin role.

    Press Next and in following screen, select the destination server with same considerations on authentication type as on previous screen.

  3. Step 2 – Select Transfer method
  4. There are 2 options: Use detach and attach method and Use SQL Management Object method.
    If I would select the detach/attach method, the database would be detached, the data and log files would be copied to the destination server and finally attached to the destination server. This method would fail because the attach method is not supported on lower SQL versions.

    This is why you need to select the second option: SMO method.

    Select Transfer Method

    Select Transfer Method


  5. Step 3 – Select Database
  6. Select Databases

    Select Databases


  7. Step 4 – Select Database file location
  8. Select the location on disk for the future database files:

    Configure Destination Database (1 of 1)

    Configure Destination Database (1 of 1)


  9. Step 5 – Select Server objects
  10. You can migrate Logins, SQL Agent Jobs and other objects. I selected only the logins for the migrated database.

    Select Server Objects

    Select Server Objects


  11. Step 6 – Configure the Package
  12. The SSIS package created will be integrated into a new SQL Agent job with the same name as the package but you can change it. This can be executed immediately or scheduled. In this example I will execute it immediately. Also, you can store the log file and to check afterwards what exactly happened beyond this wizard.

    Configure the Package

    Configure the Package

    Schedule the Package

    Schedule the Package


  13. Step 7 – Finalize the migration
  14. Review that all the information displayed in below screen are valid.

    Complete Wizard

    Complete Wizard


At last if someone is curious of what exactly this SSIS package is doing, I thought it would be useful to post the package log from both types of methods: Detach/Attach & SMO. You will see the difference between them starting the moment when the data and log files are created. In SMO, these are created not copied.


Transferring database DB_2008_R2 from SERVER2008R2 server as DB_2008_R2 to SERVER2008SP1 server
Dropping all connections to DB_2008_R2
All active connections to DB_2008_R2 are dropped
Detaching database DB_2008_R2 from source SQL Server
Database DB_2008_R2 detached from source SQL Server
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2.mdf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf overwrite set to False
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2_log.ldf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf overwrite set to False
Attaching database DB_2008_R2 to destination SQL Server as database DB_2008_R2
Attaching database DB_2008_R2 to SERVER2008SP1, SetBrokerState=True, AttachOptions=None
OnError,SERVER2008R2,DOMAIN\SQLUSER,SERVER2008R2_SERVER2008SP1_Transfer Objects Task,10/21/2011 12:00:32 PM,10/21/2011 12:00:32 PM,0,0x,Attach database failed for Server ‘SERVER2008R2’.
InnerException–>The database ‘DB_2008_R2’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘DB_2008_R2’. CREATE DATABASE is aborted.


Transferring database DB_2008_R2 from backenddc server as DB_2008_R2 to backenddc\LCS server
Added data file DB_2008_R2 in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf to file group [PRIMARY]
Added log file DB_2008_R2_log in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf
Transferring data to database DB_2008_R2 from DB_2008_R2
Transferred data to DB_2008_R2
Transferring server objects to server backenddc\LCS from backenddc
Database transfer is complete.
Transfer objects finished execution.

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

I will continue in this part, detailing the case of a database failure with SUSPECT status when the Log file is corrupted or deleted. As I mentioned in part 2,  you can easily find out why your database can’t be successfully recovered and is marked with SUSPECT status from the SQL Server Log file. The error will point to the exact problem – whether data file (.mdf) or log file (.ldf) is inaccessible. In part 2, I described the disaster recovery method if you lost your data file (.mdf). In that case you absolutely needed at least a Full backup to recover the database for all types of recovery models (SIMPLE, FULL, BULK-LOGGED).

Things are different if you lost only the Log file. If you have a Full backup and one/more Log backups than the recovery process for databases set in Simple/Full/Bulk-Logged recovery model is the same as in part 2 except that for Full/Bulk-Logged models you can’t backup the tail of the log it being corrupted or deleted.

If you don’t have a Full backup and your Log file can’t be opened but the Data file is intact, then – good news… you can bring the database online for users with minimal loss of data. The main idea is that you can only recover the database to a partially consistent state – having lost every transaction from the Log File which where not written to disk (after a Checkpoint) before the disaster.

So let’s have some fun recovering a SUSPECT database with a corrupted Log file.
1. Prerequisites For A Database Failure: 

-- Create database
CREATE DATABASE [recovery_test_2] 

-- Create Test tables
CREATE TABLE Products (Product nvarchar(100), Price INT);

CREATE TABLE Invoices ([Id] INT IDENTITY, Product nvarchar (100), Value INT);

-- Make some transactions

INSERT INTO Products VALUES ('T-shirt',200);
INSERT INTO Products VALUES ('Boots', 150);
INSERT INTO Products VALUES ('Skirt', 500);
INSERT INTO Products VALUES ('Belt', 300);

-- Make one uncommitted transaction written to Disk with forced Checkpoint. 

Begin transaction
Update Test2 set price= 1000 where product= 'Boots'

-- From another user session make one uncommitted transaction with
   no forced Checkpoint.

Begin transaction
INSERT INTO Invoices VALUES ('T-shirt', 200);
INSERT INTO Invoices VALUES ('Skirt', 500);

I just updated the price of Boots and sold two products.
2. Provoke Database Failure: 

-- Simulate failure 


-- Physically Rename or Delete LOG File of recovery_test_2 database  

-- Re-start SQL Server

-- Check database status

SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status';


-- Try to re-open the database



File activation failure. The physical file name "C:Program Files
Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_log.ldf"
may be incorrect. The log cannot be rebuilt because there were open
transactions/users when the database was shutdown, no checkpoint
occurred to the database, or the database was read-only.
This error could occur if the transaction log file was manually
deleted or lost due to a hardware or environment failure.

Msg 945, Level 14, State 2, Line 1
Database 'recovery_test_2' cannot be opened due to inaccessible files or
insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
ALTER DATABASE statement failed.

3. Recover The Database:
As a GOLD rule is to never detach a SUSPECT database if you don’t have backups to recover from lately. In SQL 2008 there is a very wise restriction which prohibits to detach a SUSPECT database. In SQL 2005 and SQL 2000 however this is possible but strongly NOT RECOMMENDED . If you do this, even if your Log file is missing and is not actually corrupted – you will not be able to use the ATTACH_REBUILD_LOG option on CREATE DATABASE because SQL knows there where 2 active transactions and can’t create a new Log file.
You will have to fool SQL into believing that it is attached by creating a new dummy database (same database name, same file locations and sizes) –> bring database offline –> delete dummy’s database files –> replace them with the original Data Files (Log file is already missing) –> bring database online –> and obtain again the SUSPECT database’s status. 

Now comes the fixing part. 

The only way to access a SUSPECT database is to use EMERGENCY mode.


File activation failure. The physical file name "C:Program Files
Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_log.ldf"
may be incorrect.
The log cannot be rebuilt because there were open transactions/users
when the database was shutdown, no checkpoint occurred to the database,
or the database was read-only. This error could occur if the transaction
log file was manually deleted or lost due to a hardware or environment failure.

Warning: The log for database 'recovery_test_2' has been rebuilt.
Transactional consistency has been lost. The RESTORE chain was broken,
and the server no longer has context on the previous log files, so you
will need to know what they were. You should run DBCC CHECKDB to validate
physical consistency. The database has been put in dbo-only mode. When
you are ready to make the database available for use, you will need to
reset database options and delete any extra log files.

SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status';


Now let’s take a look at Products and Invoices tables:

-- Set database with multi-user access 

select * from Products

Product	Price
T-shirt	        200
1000	        150
Boots	        1000
Belt	        300

select * from Invoices

ID	Product	Value
(0 row(s) affected)

It can be noticed that ‘Boots’ product has the price changed to 1000, while the transaction was not committed before the crash but it was however written to disk. It means that SQL had no information stored in the newly created Log file about the transaction executed before the crash in order to decide whether to Rollback or to Roll-forward. So, transactions that where written to disk (due to self manageable SQL process known as Checkpoint) but not committed must be manually identified and rolled forward or rollbacked by the functional teams. It is a risk that must be considered while recovering from disaster with a lost Log file using the described method. Sometimes this is the only option you have.

On the other hand the insert before the crash into Invoices table that was not written to disk and not committed, can’t be found in the table after recovery. It was contained only in the corrupted Log file. The recovery process without backups has good parts and bad parts. This is the reason, why Full, Differential and Log backups are a mandatory step to do in the big chain of administrative tasks. 

Previous <<- Part 2

SQL 2005/2008 Database Recovery procedure – Data File deleted (Part 2)

Sometimes we need to deal with unfortunate and unplanned events like: hardware failure, human mistakes that lead to a SUSPECT database. Let’s face it, this is the moment when the management actually values the fact that he hired a DBA. Usually these unforgettable moments, either makes you a hero or…. you could search for another job. Let’s be heroes instead 🙂 and make sure that you did all that you could to recover from some catastrophic events. There are more situations that I can think of, but I will describe some of them which can come on handy when it happens. Let’s think of it like a disaster test. It is always easier to treat disaster recoveries when you have tested before similar situations.

Possible factors for a database to be marked SUSPECT:

  • Denial of access to a database resource by the operating system
  • Anti-virus/3rd party software blocking access
  • The hardware/network failure or leading to corruption of database file(s)

Any of these factors affect the database files: Data file becomes corrupted, Log file becomes corrupted  or both are corrupted.
If the database can’t be recovered after failure it will be marked as Suspect and you can’t bring ONLINE to users until you fix the SUSPECT state.
You’ll get an Error when accessing the Suspect database:

Database 'recovery_test_1' cannot be opened due to inaccessible
files or insufficient memory or disk space. See the SQL Server
errorlog for details. (Microsoft SQL Server, Error: 945

Next take a look in the SQL Server Log file and you will see the reason the database could not be opened.
When the Data File is corrupted or missing the error will look like this:

FCB::Open failed: Could not open file C:Program FilesMicrosoft SQL Server
MSSQL10.TESTMSSQLDATArecovery_test_simple.mdf for file number 1.  
OS error: 2(failed to retrieve text for this error. Reason: 15105).

In this case you absolutely need a recent  Full backup and a couple of Log backups for Full or Bulk-Logged Recovery models. The Data File contains the actual Data, all tables, Indexes, Procedures etc.

1. Simple Recovery model

The things are quite simple:

  • Delete the suspect database
  • Restore the most recent Full backup with RECOVERY option and that’s it.

2. Full Recovery model

You would want to recover the Suspect database to the last point of failure including the last transactions that where not backed up in a log backup.
For this to work you need a Full backup and at least one valid Log backup

Here is how I will simulate the crash and recovery of a database: I will create a new database, take a Full backup, a log backup, make some changes, put the database Offline, simulate the crash by deleting the data file and then try to bring it Online.

2.1. Prerequisites For A Database Failure:

-- Create database
CREATE DATABASE [recovery_test_1] 

-- Create a Test table
CREATE TABLE Test1 (a1 INT IDENTITY, b2 nvarCHAR (100));
-- Take a full backup
BACKUP DATABASE recovery_test_1 TO DISK =
'C:TESTSFull_recovery_test_1.bck' WITH INIT;

-- Take a Log backup
BACKUP LOG recovery_test_1 TO DISK =
'C:TESTSTran_recovery_test_1.trn' WITH INIT;

-- Make some transactions

INSERT INTO Test1 VALUES ('record 1');
INSERT INTO Test1 VALUES ('record 2');
INSERT INTO Test1 VALUES ('record 3');
INSERT INTO Test1 VALUES ('record 4');

2.2. Provoke Database Failure:

-- Simulate failure

-- Physically Rename or Delete DATA File of recovery_test_1 database  

-- Try to re-open the database


Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "C:Program FilesMicrosoft SQL Server

ALTER DATABASE statement failed.

-- Check database status
SELECT DATABASEPROPERTYEX ('recovery_test_1', 'STATUS') AS 'Status';


2.3. Recover The Database:
First thing to do is to immediately try to backup the tail-of-log.

BACKUP LOG recovery_test_1 TO DISK = 'C:TESTSTran_recovery_test_1_2.trn'

Processed 1 pages for database 'recovery_test_1', file
'recovery_test_simple_log' on file 1. BACKUP LOG successfully
processed 1 pages in 0.037 seconds (0.211 MB/sec).

OK, so now I have all that I need to make a successful recovery: the Full backup + 2 Log Backups (one made previously the crash and of the tail-of-log).

  1. Drop Database:

    Drop database recovery_test_1
  3. Remove the original Log file because the Drop will not do it.

  5. I need to find out the logical names of the crashed database from the full backup. I can’t do a restore if I don’t know them.

    RESTORE FILELISTONLY FROM DISK = 'C:TESTSFull_recovery_test_1.bck'
  7. Restore Full backup with NORECOVERY option:

    RESTORE DATABASE recovery_test_1 FROM  DISK =
    N'C:TESTSFull_recovery_test_1.bck' WITH  FILE = 1, MOVE N'recovery_test_simple'
    TO N'C:Program FilesMSSQLDATArecovery_test_simple.mdf', MOVE N'recovery_test_simple_log'
    TO N'C:Program FilesMSSQLDATArecovery_test_simple_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10
    Processed 176 pages for database 'recovery_test_1',
    file 'recovery_test_simple' on file 1.
    Processed 1 pages for database 'recovery_test_1',
    file 'recovery_test_simple_log' on file 1.
    RESTORE DATABASE successfully processed 177 pages
    in 0.279 seconds (4.956 MB/sec).
  9. Restore 1’st and 2’nd  Log backups with NORECOVERY and sequential log with RECOVERY option:

    RESTORE LOG recovery_test_1 FROM  DISK =
    RESTORE LOG recovery_test_1 FROM  DISK =

Hooray… the database is recovered and a select on Test1 table reveals the last rows inserted before the crash:

a1   b2
1    record 1
2    record 2
3    record 3
4    record 4

Previous <<- Part 1

SQL 2005/2008 Database Recovery Procedure – Recovery Models (part 1)

When you create a new database and prepare the environment for a certain application to be deployed, you must take into consideration the database recovery model configuration. Each new database created will have the same Recovery model set to model system database. model database works as a template for each new created database. The default setting is “Full”.

The first thing to do is to decide whether you need Full, Simple or Bulk-Logged Recovery Model on your databases. I will not enter in many details about what each model means as there a lot of very good articles describing the pros and cons for each (links to them are posted at the bottom on this post).

I will mainly focus on recovery procedures in specific types of recovery models. They are different and there are some factors that need to be considered before you decide which model to set on your databases.

1. Full Recovery Model

In Full recovery model all transactions are logged and you have full control over your business – human or technical mistakes are not longer a disaster. So, you can recover the database to the last transactional backup log done – be it 30 minutes or 10 minutes ago – depending on your schedules.

In order to be able to do so: Full backups, Differential backups (if you want to reduce the recovery process to a smaller backup chain) and Log backups must be enabled according to specific schedules that best fit your transaction flows. The transaction log needs to be backed up regularly to prevent not only the loss of work but also to reduce the transaction log size.

The recovery process of a database can be done by:

  1. Recovering the last Full backup + each one of the Log backups performed since the last Full Backup.
  2. Recovering the the last Full backup + last Differential made after last Full + each one of the Log backups performed after the last Differential Backup.
  3. Alternatively if the database is damaged, corrupted and has entered in suspect state you can recover the database to the last transaction from the current transaction log (not-backed up). This can be done by backing up the tail-of-the-log if possible and try applying the 1’st or 2’nd of the above methods + the tail-of-the-log just backed – up. (see Database Recovery procedure – Data File deleted (Part 2)

Conclusion: Stick to the Full recovery option if your business is critical and transactions can’t be re-created or restored easily from other tools.

2. Simple Recovery Model

I will keep it simple. Choose this model if your databases are for DEV/Test purposes or…. are Production but not critical ones such as: databases with static data, with data that be re-created or recovered from other tools and mostly databases containing read-only data (data warehouse databases).

You must be sure that your system can leave with a loss of all transactions processed since the last Full or Differential was made. Although Log backups are not possible, I suggest you enable anyway more frequent differentials as these can be really useful for lost wide developments.

The recovery process of a database can be done by:

  1. Recovering the last Full backup + last differential since the last Full Backup.

3. Bulk-Logged Recovery Model

It is quite similar to Full recovery model, with the single difference that in Bulk-Logged recovery model NOT all transactions are fully logged and you CAN’T have full control over your business. Transactions that are minimally logged are: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations are not recoverable. You still need to do full, differential and transaction log backups.

If a disaster forces you to recover a database set in Bulk-Logged recovery model you will able to do so by:

The recovery process of a database can be done by:

  1. Recovering the last Full backup + last Differential made after last Full + each one of the Log backups performed since the last Full Backup that DO NOT contain any bulk- logged transaction. In other words, you will be able to recover completely the database to the last log backup made only if it’s not containing bulk activity since the last Full backup made. If there was, you will be able to recover the database using log backups only to the beginning of that log backup which has bulk activity.
  2. Contrary to Full recovery model there are exceptions when you can backup the tail-of-log in order to completely recover the database to the last transaction made.
  • If there has been a bulk-log operation performed in the BULK-LOGGED recovery model since the last log backup made – in that case a tail-of-the-log backup is not possible at all, and you’ll have lost all transaction log generated since the last log backup.
  • If you are lucky and there are no bulk-logged operations performed in the Bulk-Logged recovery model since the last log backup made – in that case a tail-of-the-log backup is possible, but if there are other bulk-logged operations in some of the previous log backups since the last Full – the tail-of-log can’t be used in a sequential restore.
  • You can backup the tail-of-log and use it only if there are no bulk-logged operations caught in any of the needed previous log backups and non in the current not-backed up transaction log.

Conclusion: Why you should use Bulk-Next Logged recovery model? If you have a big amount of bulk insert operations that are scheduled at specific hours (nightly) the bulk-logged recovery model may be the best choice. Do a Full backup after the Bulk insert and you will have a good backup log chain to recover from until the next bulk operation.

Any option you choose be sure you choose it wisely and plan your recovery options accordingly. In part 2 and part 3, I will simulate several recovery methods considering different scenarios.

Log File Usage – in Full and Simple Recovery Model

I heard many times people complaining that their database log file is growing huge even if the database is in Simple recovery model. It is absolutely normal because the SQL transaction mechanism (ACID) doesn’t change when recovery model is changed. In order to keep a transaction atomic transaction log will be used no matter which recovery model you use. Atomicity refers to the idea that a transaction needs to be processed completely or not at all. In other words, if a transaction fails at any point in the process, the entire transaction must be rolled back and the log is the place where all modifications are recorded.

At each DML statement (INSERT / DELETE / UPDATE) the transaction log will be used. How? At each data modification statement, SQL will read the data pages affected by the modification into buffer cache, updates with new values also in Buffer cache and records the modification into the Log file. This is why the Log is used and you can see big Log growth during a transaction.

In Simple recovery model – when a Checkpoint occurs (usually an SQL internally managed operation) – the dirty data pages (which were modified in buffer cache) are written (flushed) to disk. Also at checkpoint the inactive part (not used by any transaction) of the log is truncated making the Transaction log re-usable for other transactions. After the log was truncated, the Log file than can be shrinked in order to reduce the physical size if needed.

In Full recovery model, the log is truncated only after a log backup. The checkpoint does not truncate the inactive part of the log file. This is the main difference between Full and Simple recovery mode. A good start in understanding better all this can serve the following articles:

  1. MSDN article – Transaction Log Truncation
  2. Louis Nguyen article – SQL Server Database Engine Basics
  3. SQL Server Performance blog –Database Recovery Models in SQL Server