Three little SQL Server Backup Tricks

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?

BACKUP DATABASE  [AdventureWorks2008R2]
TO DISK =''AdventureWorks2008R2.bak'' 
WITH INIT, COPY_ONLY;

 

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:

image

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:

image

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:

EXEC sp_configure ''backup compression default'', ''1'';
RECONFIGURE WITH OVERRIDE;

 

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:

DBCC TRACEON(3042)
GO

BACKUP DATABASE  [AdventureWorks2008R2]
TO DISK =''AdventureWorks2008R2.bak'' 
WITH INIT, COPY_ONLY, CHECKSUM;

DBCC TRACEOFF(3042)
GO

 

Be aware, this setting this flag may impose a slight performance penalty.

Cheers, Marcin

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration and tagged , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.