Backing up the database files by copying

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

Can you back up a database by simply making a copy of its data and log files?  Yes, but only when the data files are no longer used by SQL Server.

When a database is active, you cannot simply make a copy of its files and assume that it's good enough to be used as a full database backup.  This is because its data and log files may not be in a consistent state.  There may be modified data pages that have not been written to the data files, only recorded in the transaction log.  There may also be transactions that are in progress, that have neither been committed nor rolled-back, but have been written to the data file.

A simple example is as follows:


Copying of data files start, takes 10 minutes.


A checkpoint occurs, modifies some pages at the beginning of the data file (which have already been copied), and some pages at the end of the data file (yet to be copied).


Copying of data files end, copy of transaction log starts.


Copying of transaction log ends.


What you end up with is a transaction log that thinks that the data files already contain all changes made during the last checkpoint, and a data file that contains only part of the changes.


There are utilities that are able to make copies of open files i.e. files that are in use by other applications, but for the reasons stated above, this is not the right way to back up your SQL Server databases.  If you must make a copy of the database files instead of backing up the database the usual way, the recommended approach is to first detach the database using the sp_detach_db stored procedure e.g.

sp_detach_db 'AdventureWorks'

SQL Server will write all dirty pages to disk, before it takes the database offline and detaches it from the SQL Server instance.  You can then make a copy of the database files, which will be in a consistent state.  You can then reattach the database later using the sp_attach_db stored procedure e.g.

sp_attach_db 'AdventureWorks', 'e:\mssql_data\AdventureWorks\AdventureWorks.mdf', 'e:\mssql_data\AdventureWorks_log.ldf'

or the sp_attach_single_file_db stored procedure if your database consists of only one data file and you no longer have the transaction log file e.g.

sp_attach_single_file_db 'AdventureWorks', 'e:\mssql_data\AdventureWorks\AdventureWorks.mdf'

You can also safely make copies of a database's files when the entire SQL Server instance is offline.

The main disadvantage of making a backup of the database this way is that you have to take the database offline for the duration that it takes to make a copy of the database's files.  Also, you cannot use the attached database as the starting point to restore differential or transaction log backups.

Document history
8/15/2008    Added example.    
8/1/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.