SQL Server Backups


From SQL Server 2008 onwards you can elect to use compressed backups. This can be set at the SQL Instance level and is found on the "Database Settings" page of "Server Properties". You can also set it during a backup, to either on, off or "Default Server Setting".


It is worth noting that when restoring a backup, you can restore from an older version of SQL Server but not a newer. So SQL Server 2012 can restore backups from 2005, 2008 & 2008 R2 but SQL 2008 R2 cannot restore a SQL 2012 backup. One way round this is it use the "Generate Scripts" option, which is on the Tasks menu when you right click a database in SSMS. When you do this, make sure you click the Advanced button if you want the data as well as the schema. There are other ways to do this including using the "Import and Export Wizard", which might work better if the two instances can see each other over the network.