Saturday, 9 March 2013

View or Configure the backup compression default Server Configuration Option


use sp_configure to configure the server instance to create compressed backups by default.


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


By this, all further backups that you perform will be done with compression by default.



Then there is a very good backup scheme that I've developed.
Here it is-


DECLARE @dbName varchar(100);
DECLARE @backupPath varchar(100);
DECLARE @backupQuery varchar(500);

set @dbName = N'database_name'


EXECUTE master.dbo.xp_create_subdir N'\\your\path\to\the \folder\database_name'


set @backupPath = '\\your\path\to\the \folder\database_name\'


set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''


print @backupQuery


EXEC (@backupQuery)

1 comment:

  1. Make sure you have the backup compression compatible version of sql server installed on your machine.

    Check it by executing this command-

    SELECT @@VERSION

    ReplyDelete