Are you sure you want to use the SQL Server backup and restoration function? 3: Use copy to back up the database

Does SQL server allow simple copy to back up database data and log files? The answer is yes. However, the premise is that data files are no longer used by SQL Server.

When the database is active, you cannot simply copy its files. We recommend that you use full database backup (Backup Type ). This is because the database data and log files cannot be in a different state. As a result, only the modified data pages in the transaction log may not be able to write data files. This will also affect the failure to commit transactions in the process or roll back the data that has been written into the file.

The following is a simple example:

10: 00	Copying of data files start, takes 10 minutes.
10: 05	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 ).
10: 10	Copying of data files end, copy of transaction log starts.
10: 15	Copying of transaction log ends.

The last step is to copy the transaction log, which means that the data file has included all the changes after the last checkpoint, but in fact the data file only contains some changes.

Copy a file that has been opened by other applications. This method is very useful, but as mentioned above, this method does not seem to be suitable for backing up SQL Server databases. If you must use the copy method to replace the common database backup method, we recommend that you use the sp_detach_db stored procedure to separate the database:

Sp_detach_db 'adventureworks'

SQL Server writes all dirty pages back to the disk before separating the database from the SQL server instance. Then, you can copy the database files, and the database files are in a consistent state. Then, you can use sp_attach_db to re-append the database:

Sp_attach_db 'adventureworks', 'e:/mssql_data/adventureworks. MDF ', 'e:/mssql_data/adventureworks_log.ldf'

If your database is composed of only data files and no longer requires transaction log files, use the sp_attach_single_file_db stored procedure:

Sp_attach_single_file_db 'adventureworks', 'e:/mssql_data/adventureworks. MDF'

When the database instance is offline, you can also securely copy database files.

The main trend of copying database files is that you have to take the database offline for a while so that you can copy data files. Additionally, you cannot use the attached database as the starting point for differential backup and transaction log backup.

 

This article translated from sqlbackuprestore, more exciting content please visit http://www.sqlbackuprestore.com