The "COPY_ONLY" backup mode in SQL Server is a special type of backup that doesn't interfere with the normal backup sequence. Normally, when you perform a full or differential backup, SQL Server logs the fact that the backup was done. This information is used when you perform a later incremental or differential backup, or when you are restoring backups.
However, when you perform a backup with the "COPY_ONLY" option, SQL Server doesn't log the backup in the backup sequence. This means you can perform a "COPY_ONLY" backup at any time, without affecting subsequent backups. This kind of backup is useful for situations where you need a quick copy of the database, but don't want to interfere with the regular backup routine.
To create a full backup of a SQL Server database in SQL Server Management Studio (SSMS) using the COPY_ONLY option, follow the steps below:
1. Open SQL Server Management Studio (SSMS):
Launch SQL Server Management Studio and connect to the server instance where the database is hosted.
2. Navigate to the database:
In the "Object Explorer" pane on the left, expand the server tree to see the list of databases. Locate and right-click on the database you want to back up.
3. Start the backup wizard:
In the context menu that pops up, go to `Tasks -> Back Up...`. This will open the "Back Up Database" window.
4. Configure backup options:
In "Source", select "Database" and choose the name of the database you want to back up from the dropdown list.
In "Backup type", select "Full".
Check the "Copy Only Backup" option. This creates a standalone backup that does not interfere with the normal backup sequence.
Under "Destination", choose where the backup file will be saved. Select "Disk" and click "Add" to choose a location and name for the backup file.
Under "Backup set", you can assign a name to the backup set and optionally provide a description. By default, SSMS fills in these fields with suggested values.
5. Configure advanced backup options:
In the left side menu, select the "Options" tab.
Under "Overwrite media", select "Append to the existing backup set" or "Overwrite all existing backup sets", depending on your need. "Append" will add this backup to the existing file, while "Overwrite" will replace any existing backups.
Under "Reliability", you can opt to verify backup completion and compress the backup.
6. Execute the backup:
Click "OK" to start the backup. A progress bar will be displayed, and a message will be shown when the backup has successfully completed.
Remember that you must have appropriate permissions to perform backups. For large databases, the backup may take some time. Also, the backup does not include objects like database users, scheduled tasks, etc. For these objects, you may need to back up the master database or the msdb database.
コメント