Let me ask you some simple questions, all about the following statement:
1. Where will be the backup file stored?
2. Will it be compressed?
3. If your answer to the second question was Yes, how much space would be needed to store it in this location? No less than uncompressed or compressed file size?
The answers are: in the default backup directory, it depends and again, it depends. Now it is time to explain those answers.
Tip 1. Changing default backup directory
If you are using SQL Server 2012, changing default backup directory is easy – all you have to do is launch SSMS, right-click server name, select Properties and go to Database Settings tab. There, under Database default location you will find Backup option:
But if you get stuck with some older version, and have not set the default backup directory during SQL Server installation, you will have to modify registry to change it. This path is stored in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLServer key, where MSSQL11.MSSQLSERVER is your instance name. The BackupDirectory REG_SZ value stores the default backup directory – after changing this value you will NOT have to restart your SQL Server:
Note the missing backlash at the end of this path – this is no mistake, you don’t need it here.
Tip 2. Turning on default backup compression
By default, backup compression (great feature available in Enterprise and, started with SQL Server 2008 R2, in Standard Edition as well) is turn off. By turning it on you can benefit (but remember, there are exceptions to every rule, i.e. there is no point in compression encrypted databases) without having change anything in your backup scripts/tasks.
All you have to do to switch it on is execute the following statements:
Tip 3. Bypassing space pre-allocation for compressed backup files
By default, compressed backup file can be huge at the beginning of backup creation. By huge I mean two times larger than the final size of compressed backup file. As a result, you may not be able to store 100 GB backup file on 150 GB disk, even if the final file size will be like 80 GB. Nonetheless you can change this behavior and allow the file to grow only as needed to reach its final size by setting trace flag 3042 to on:
Be aware, this setting this flag may impose a slight performance penalty.