A couple of weeks ago I was troubleshooting a performance problem with the variations feature in MOSS 2007 and I needed to copy the content database to another environment for further analysis and testing. An easy (an unobtrusive) way to "snapshot" a database and copy it to another environment is to create a backup with the COPY_ONLY option:
BACKUP DATABASE [WSS_Content]
TO DISK = N'H:\WSS_Content.bak'
WITH NOFORMAT, NOINIT
, NAME = N'WSS_Content-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
, COPY_ONLY
From SQL Server 2005 Books Online:
Taking a backup normally changes the database, in turn affecting other backups and how they are restored. Sometimes, however, a backup must be taken for a special purpose that should not affect the overall backup and restore procedures for the database.
A data backup is normally a base backup for one or more differential backups taken after it. Microsoft SQL Server 2005 introduces support for creating copy-only backups, which do not affect the normal sequence of backups. Therefore, unlike other backups, a copy-only backup does not impact the overall backup and restore procedures for the database.
In other words, by using the COPY_ONLY option I avoided screwing up the scheduled differential backups on the database.
However, there are a couple of issues with this approach:
You cannot specify the COPY_ONLY option through the UI in SQL Server Management Studio, but this is no big deal -- you can start by configuring most of the backup options using the UI, script the action to generate the corresponding SQL, and then add the COPY_ONLY option as shown above
You cannot restore a backup created using the COPY_ONLY option through the UI in SQL Server Management Studio; in the Restore Database dialog, when you select the From device option and then specify the backup file previously created with the COPY_ONLY option, no backup sets are displayed
The second problem was puzzling to me. After specifying my backup file, when I attempted to change to the Options page, I encountered the following error:
You must select a restore source.
When I first encountered this problem, I thought I had a corrupt backup file. However, by once again reverting to SQL instead of the UI, I was able to verify the backup was, in fact, valid:
RESTORE FILELISTONLY
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
To restore from a COPY_ONLY backup, use a command similar to the following:
RESTORE DATABASE [WSS_Content_TEST]
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
WITH FILE = 1
, MOVE N'WSS_Content'
TO N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST.MDF'
, MOVE N'WSS_Content_Log'
TO N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST_Log.LDF'
, NOUNLOAD, STATS = 10
BACKUP DATABASE [WSS_Content]
TO DISK = N'H:\WSS_Content.bak'
WITH NOFORMAT, NOINIT
, NAME = N'WSS_Content-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
, COPY_ONLY
From SQL Server 2005 Books Online:
Taking a backup normally changes the database, in turn affecting other backups and how they are restored. Sometimes, however, a backup must be taken for a special purpose that should not affect the overall backup and restore procedures for the database.
A data backup is normally a base backup for one or more differential backups taken after it. Microsoft SQL Server 2005 introduces support for creating copy-only backups, which do not affect the normal sequence of backups. Therefore, unlike other backups, a copy-only backup does not impact the overall backup and restore procedures for the database.
In other words, by using the COPY_ONLY option I avoided screwing up the scheduled differential backups on the database.
However, there are a couple of issues with this approach:
You cannot specify the COPY_ONLY option through the UI in SQL Server Management Studio, but this is no big deal -- you can start by configuring most of the backup options using the UI, script the action to generate the corresponding SQL, and then add the COPY_ONLY option as shown above
You cannot restore a backup created using the COPY_ONLY option through the UI in SQL Server Management Studio; in the Restore Database dialog, when you select the From device option and then specify the backup file previously created with the COPY_ONLY option, no backup sets are displayed
The second problem was puzzling to me. After specifying my backup file, when I attempted to change to the Options page, I encountered the following error:
You must select a restore source.
When I first encountered this problem, I thought I had a corrupt backup file. However, by once again reverting to SQL instead of the UI, I was able to verify the backup was, in fact, valid:
RESTORE FILELISTONLY
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
To restore from a COPY_ONLY backup, use a command similar to the following:
RESTORE DATABASE [WSS_Content_TEST]
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
WITH FILE = 1
, MOVE N'WSS_Content'
TO N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST.MDF'
, MOVE N'WSS_Content_Log'
TO N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST_Log.LDF'
, NOUNLOAD, STATS = 10
No comments:
Post a Comment