Does Database backup contain a copy of the database as it was at started time or completed time?

Unlike database restore operation, SQL Server allows us to take a backup of our database while it is being used, placing some insignificant limitations such as not allowing execution of ALTER DATABASE, not allowing execution of ADD FILE, etc. This means, users connected can continue with their actions against the database, not only SELECT, even INSERT, UPDATE and DELETE. This introduces a question regarding timing of associated with the content of the backup. Does the database backup contain a copy of it as it was at started time of the backup operation or completed time?

It is very important to understand how SQL Server performs the backup operation. Backup process collects all data pages to the backup device in sequence and it captures all data pages that are being modified by running transactions, accessing the transaction log. The captured transaction log portion is added to the end of the backupset. Theoretically, if the backup process starts at 10:00am and finishes at 11:00am, it contains all the transactions performed by users till 11:00am.

Let's test this. I have a copy of AdventureWorksDW2014 which has a large amount of data. It takes 12-15 minutes to take full backup of this database. Let's create a new table in this database, and insert a record while the backup is performed. And then let's restore the database with a different name and see whether it contains the inserted record which was inserted while database was being backed up.

USE AdventureWorksDW2014;
GO

-- Creating a test table
CREATE TABLE dbo.TestTable
(
 Id int identity(1,1) primary key
 , TestValue varchar(100) not null
 , DateEntered datetime default(getdate()) not null
);

-- Starting the backup operation
BACKUP DATABASE AdventureWorksDW2014 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\AdventureWorksDW2014.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorksDW2014-Full Database Backup';
GO

/* 
While database backup is running, opening a new query window
and inserting a record to the table
*/
INSERT INTO dbo.TestTable (TestValue) VALUES ('Test Value');

/*
and going back to previous window
*/

-- Checking the starting time and ending time of the backup
RESTORE HEADERONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\AdventureWorksDW2014.bak';
GO

-- Checking the inserted time of the record
SELECT * FROM dbo.TestTable;

-- Restoring the backup with a new name
USE master;
GO

RESTORE DATABASE AdventureWorksDW2014_New
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\AdventureWorksDW2014.bak' 
WITH  FILE = 1
, MOVE N'AdventureWorksDW2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorksDW2014_New_Data.mdf'
, MOVE N'AdventureWorksDW2014_Current_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorksDW2014_New_Current_Data.ndf'
, MOVE N'AdventureWorksDW2014_History_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorksDW2014_New_History_Data.ndf'
, MOVE N'AdventureWorksDW2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorksDW2014_New_Log.ldf'
GO


-- Checking the inserted time of the record
USE AdventureWorksDW2014_New;
GO

SELECT * FROM dbo.TestTable;

As you see, record inserted while database backup was performed is available. This proves that backup contains a copy of the database as it was at the completion of the backup process.