I would like some advise on the best methods for backing up.
Problem:
We have maintenance plans which run well. When doing restore testing recently i have found the following problems...
Restores do not complete due to corruption errors or they do restore then there are errors after running dbcc checkdb(DBNAME).
The files we are trying to restore are .zip files.
These have been chosen to be zip files in the 'backup file extension option in the backup database task.
Would we be best saving as .bak files?
Is it possible to save as a .bak and automate a zipping procedure?
Any advise is welcomed :)
You would be better storing them as BAK files.
SQL Server actually has an option for an instance to back-up with compression. Setting the compression on, will reduce your need to Zip the backups, and the Microsoft SQL server can restore directly from its own compressed backups.
see: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/configure-backup-compression-sql-server?view=sql-server-2017 for how to set up compression.
see : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-2017 for more details on the compression.