Database backup ZIP and cleanup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Database backup ZIP and cleanup

Author

Topic

arie01
Starting Member

8 Posts

Posted - 2012-11-21 : 13:36:45

Hi there,

I'm a kind of new to SQL serverr administration and I have a request regarding database backups.

We have a few SQL servers at the company I work for, most are 2005 and one 2008 R2 (if this is important to know). Each server ised for a specific app's database and daily backup jobs are configured on all of them. Most were configured before my time at that company, I only configured the 2008 SQL server.

So here are requests:

1. I would like all backups to be zipped upon creation using an external program (winRAR)

2. I don't need to maintain more than two weeks of backups for any of my servers, so how do I automatically erase backups that are more than 15 days old?

I appreciate any help in that matter.

Thank you,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-21 : 14:55:34

Backup compression is already available in SQL 2008.
You can use maintenance plan to erase backup more than 15 days or write your own logic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-21 : 17:53:17

What is the purpose of compressing the backups with WinRAR? That just makes them harder to use if you need to do a restore.

If you really need to compress the backups for versions of SQL Server that don't have built-in compression, it would be better to use one of the third-party tools, like Litespeed, Redgate Backup, etc., that compress the backups directly and are able to restore them directly.

For versions of SQL Server with native backup compression (2008 Enterprise or Developer Editions, and all Editions of 2008 R2 or 2012), use the built-in compression.

CODO ERGO SUM

arie01
Starting Member

8 Posts

Posted - 2012-11-22 : 08:26:57

Hi, thank you for your suggestions.

For compression, it doesn't have to be winRAR, I just gave it as an example.

How do I add compression to my backup on SQL 2008? Could someone please providea sample code?

Also, for the maintenance plan, could you please provide a detailed code, I'm not sure I know how to do that properly.

Thank you again,

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2012-11-22 : 08:40:12

you can see in this link
http://www.techrepublic.com/blog/datacenter/configure-compressed-backups-with-sql-server-2008-r2-standard-edition/2788

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-22 : 12:19:02

Simple google search will give you:
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-maintenance-plan-using-wizard.html

arie01
Starting Member

8 Posts

Posted - 2012-11-23 : 08:09:42

Hi Sunsavin,

I followed your link and my backup is now 360MB instead of almost 2GB, that's a greate disk space savings.

Now, what do I need to do if I want to restore that database on a SQL 2005 Server? Is it even possible?

Now I need to try to compress my backups on the SQL 2005 servers and I'll be good.

Thank you,

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-23 : 11:41:51

You can't restore a backup from any later version of SQL Server:
For 2005, you can't restore backups from 2008, 2008 R2, or 2012.
For 2008, you can't restore backups from 2008 R2, or 2012.
For 2008 R2, you can't restore backups from 2012.

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-23 : 11:57:26

As MVJ said, you can't restore from latest to previous version. All you can do is script out all data and objects from latest to old.

mrduckstoo
Starting Member

1 Post

Posted - 2013-01-28 : 13:01:17

For those who don't have a SQL Server edition with built-in compression, SQL Backup Master can zip backup files. Compression ratios are around 50-60 percent, depending upon data. Basic edition is free.

http://www.sqlbackupmaster.com[url][/url]

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-01-30 : 09:39:19

Hi,

I want to figure out how to achieve similar goal as aire01 2) part.
"...so how do I automatically erase backups that are more than 15 days old?"

sodeep has this said:
"You can use maintenance plan to erase backup more than 15 days or write your own logic"

Let's say I have a 40 Gb E Drive. I run full backup twice (Wed & Sun) per week. Each is 14 Gb. I want my next backup to replace the older bak, so i will always have 2 latest bak files.

In maintenance plan, I tried "Backup set will expire" after 7 days, but it did not work.

I am running sql08 R2.

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-01-30 : 10:09:05

OK, now I saw this Clean Up History task. I have added it into the flow. That seems is the answer.

cyberguest
Starting Member

1 Post

Posted - 2013-02-01 : 13:11:11

You can do something like this:

forfiles /P "E:\SQL_Backup" /m *.zip /d -14 /c "cmd /c del @file

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-02-05 : 09:43:05

My Clean Up history did not work as I thought it should.

As the log showed here, it "Succeeded", but the older files are still there.

What did I miss?

Thanks!

---------------

Microsoft(R) Server Maintenance Utility (Unicode) Version 10.50.1600
Report was generated on "myVMserv".
Maintenance Plan: my plan
Duration: 00:04:11
Status: Succeeded.
Details:
Clean Up History (myVMserv)
Cleanup history on Local server connection
History type: Backup,Maintenance Plan
Age: Older than 6 Days
Task start: 2013-02-02T00:25:04.
Task end: 2013-02-02T00:25:05.
Success
Command:declare @dt datetime select @dt = cast(N''2013-01-27T00:25:04'' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,''2013-01-27T00:25:04''

GO

quote:
Originally posted by Hommer

OK, now I saw this Clean Up History task. I have added it into the flow. That seems is the answer.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-02-26 : 00:08:30

fortfiles is an executable you need to install on the machine,which deletes the old files based on the retention period the user gives.

AlexVlg
Starting Member

3 Posts

Posted - 2013-07-22 : 02:07:02

Hi,

You can create and schedule such jobs with SQLBackupAndFTP:
1. It can compress backups with embedded archiver or external 7-zip program.
2. Just setup how many days you'd like to keep backups on your destinations on a destination configuration window.

It allows you backup your SQL databases to local/network folders, FTP, Dropbox, Box, Google Drive, Amazon S3 or SkyDrive destinations. You can scheduled backup jobs and setup email notifications.
Basic features are available in free version or you can try all features in trial mode.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources