SQL SERVER – Sample Script for Compressed and Uncompressed Backup

Here is a sample script that demonstrates how to create a compressed and uncompressed backup of a database in SQL Server:

-- Compressed backup
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH COMPRESSION;

-- Uncompressed backup
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH NO_COMPRESSION;

In the first statement, the WITH COMPRESSION option is used to create a compressed backup of the YourDatabaseName database. In the second statement, the WITH NO_COMPRESSION option is used to create an uncompressed backup.

Replace YourDatabaseName with the actual name of your database and modify the path and file name of the backup file as needed.

It's also a good idea to specify the CHECKSUM option in the BACKUP statement to verify the integrity of the backup. For example:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH COMPRESSION, CHECKSUM;

This will ensure that the backup is checked for any internal errors that might have occurred during the backup process.

You can also use the WITH COPY_ONLY option to create a copy-only backup, which does not affect the database's backup chain. This can be useful if you want to create a backup for a one-time task without affecting your regular backup schedule. For example:

  
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH COMPRESSION, COPY_ONLY;