I recently came across a question on the #sqlhelp Twitter feed that I thought was interesting.  The question pertains to whether or not SQL Server zero outs the log file when doing a restore on an existing database.

We already know that even with instant file initialization, the log file is always zeroed out upon creation or file growth.  But what about a restore?  Given the restore is a page by page copy of the data, shouldn’t it just over lay the existing file?

Let’s find out!

In order to test this, we will:

1. Create a new database and a new table
2. Insert some data into the new table
3. Take a backup of the database
4. Truncate the table
5. Restore the database
6. Look at the logs to see what is happening

Let’s get started!

-- Create our database 
CREATE DATABASE LogFileRestoreDemo
GO
USE LogFileRestoreDemo
GO
CREATE TABLE Table1 (id int identity(1,1), number INT)
GO
-- in order to increase the log file, take a backup first to set the log chain.  This will prevent the log file from rolling around.
BACKUP DATABASE LogFileRestoreDemo 
TO DISK = 'C:\Backups\LogFileRestoreDemo_FULL_20150318.bak'
WITH COMPRESSION, STATS = 5, INIT
GO
-- Insert 100K random numbers
INSERT INTO dbo.Table1 (number)
    SELECT ABS(CHECKSUM(NEWID()))
GO 100000
-- Let's check, we have data right?
SELECT TOP 100 ID from dbo.Table1

Now that we have inserted some data, the log file should be a decent size.   You can confirm the file size by looking at the physical LDF file within Windows or you can use this script:

-- Do we have some size to the log file?
SELECT size, size*8 as 'Size (Kb)', name, physical_name, type_desc FROM sys.master_files
WHERE database_ID = db_ID('LogFileRestoreDemo')
GO

We know now that the log file has some size to it, let’s take another backup of it and then truncate the table.

BACKUP DATABASE LogFileRestoreDemo 
TO DISK = 'C:\Backups\LogFileRestoreDemo_FULL.bak'
WITH COMPRESSION, STATS = 5, INIT
GO
TRUNCATE TABLE dbo.Table1
GO

Turning on two specific trace flags will help us see some of the magic that takes place during the restore.  Keep in mind that truncating the table is just a way to confirm that the restore worked as expected.  

Trace Flag 3004: this flag will allow you see when SQL Server is doing zeroing out operations.  This works for both the data files as well as log files.

Trace Flag 3605: this flag will output results of certain DBCC commands to the SQL Server log.  It’s very similar to trace flag 3604 which outputs the results of certain DBCC commands to the results window.

Note that both of these trace flags are undocumented so you won’t find specific information about them directly from Microsoft.  However, there are several blogs on the internet that discuss them.

USE [tempdb]
GO
DBCC TRACEON(3004,3605)
GO
RESTORE DATABASE LogFileRestoreDemo
FROM DISK = 'C:\Backups\LogFileRestoreDemo_FULL.bak'
WITH RECOVERY, REPLACE, STATS= 5
GO
DBCC TRACEOFF(3004,3605)
GO

With those trace flags on, we can now look into the SQL Server log and see what it’s doing.

We can clearly see that SQL Server zeroed out the pages required for the log file. This shows that even if the database is already present on the server, the log file gets zeroed out when doing a restore.

As we have shown here, if you have to restore a database where the log file has grown to a significant size, SQL Server will have to zero out the pages for it.  This could take a significant amount of time to do so.  If you find yourself in this situation, please make sure that you plan accordingly.

As a final clean up, make sure that the trace flags are turned off.  If they are left on you would potentially put a lot of restore information into the log file.  Let’s also drop the database to clean everything up.

-- This will show you all of the trace flags currently enabled
DBCC TRACESTATUS(-1)
GO
USE tempdb
go
-- Drop the database
DROP DATABASE LogFileRestoreDemo
GO

Enjoy!