May 16, 2012 at 6:30 am
hi,
i am planing to implemnet new backup plan for my databases with compression.
can any one share the script to shedule as a job in sql server backups with compression
May 16, 2012 at 6:33 am
do you want compression on all backups, full, diff, tx log? if so just set the compression on as standard at the server level. otherwise simply add WITH COMPRESSION to the end of the backup database command
BACKUP DATABASE anthony TO DISK = 'C:\backups\anthony.bak' WITH COMPRESSION
May 16, 2012 at 6:57 am
i want code for all databases backups out in single lines of script it is for full,diff
May 16, 2012 at 7:02 am
[font="Courier New"]You can use this cursor........
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
with COMPRESSION
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor[/size][/font][/size]
Sagar Sonawane
** Every DBA has his day!!:cool:
May 16, 2012 at 7:06 am
Div Goud (5/16/2012)
i want code for all databases backups out in single lines of script it is for full,diff
How do you currently back up your databases?
All you need to do is to add the WITH COMPRESSION statement to the end of the existing backup statements.
You could have a look at the Ola Hallengren scripts (link in my signature), which is a very good script and you can specify if you want compression or not within that.
June 26, 2018 at 5:04 am
100% agreement with Anthony. Why write your own script for this? Ola Hallengren's scripts or the Minion Backup scripts will do the work for you. Why reinvent the wheel when a perfectly serviceable one is sitting next to you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply