Creating a COPY_ONLY Backups in SQL server

Leave a Reply
COPY_ONLY backups are used to create backups in the SQL server to keep the Log Sequence Number intact. The Log Sequence number is stored in the backup files, which gets used during the restoration. When we create a backup with COPY_ONLY, the sequence number in the log does not get shuffled, rather it is kept in a proper sequence. These sequence numbers are used during the restoration of backups. The feature of COPY_ONLY option was implemented starting SQL Server 2005 edition.

 In case we try to restore a backup file that was without COPY_ONLY out of the sequence then the sequence number will be overwritten by an update and we may encounter the error as shown below:

ERROR: Restore failed

System.Data.SqlClient.SqlError: The log in this backup set beings at LSN 5400000054410000001, which is too recent to apply to the database. An earlier log backup that includes LSN 5400000054410000001 can be restored. (Microsoft. SqlServer.Smo)

The database name and the Log Sequence Number can be fetched using the below script:

SELECT DB_NAME(database_id) AS [DATABASE_NAME], differential_base_lsn AS [LOG SEQUENCE
NUMBER]
FROM sys.master_files
WHERE database_id =DB_ID('SQLARENA_TEST')

Result:





To overcome the above error, we take the full backups with COPY_ONLY to restore the hassle-free restoration process.

T-SQL script for creating the full backups with COPY_ONLY is shown below:

BACKUP DATABASE SQLARENA_TEST
TO DISK = N'C:\SQLARENA\SQLARENA_TEST_Backup.trn'
WITH  COPY_ONLY,NOFORMAT, STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:

1.  Right-click on the “database” name.
2.  Click the option “Tasks”.
3.  A new window pops with a drop-down to select either “Full” or “Log”.
4.  Select the “Full” or “Log” option with a specific disk under the option “Back up to”.
5.  Check the option “COPY_ONLY” to create backups with COPY_ONLY.
6.  Click “OK” to take the full backups or log backups with COPY_ONLY.

The above for the same is shown below:


In the production server, mostly the COPY_ONLY backups are created when the business sites are offline. This is made since creating the backups offline will not affect the Log Sequence Number of the backups created and also the respective database business. 

0 comments:

Post a Comment