SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database backup ZIP and cleanup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arie01
Starting Member

8 Posts

Posted - 11/21/2012 :  13:36:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/21/2012 :  14:55:34  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/21/2012 :  17:53:17  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 11/21/2012 17:53:49
Go to Top of Page

arie01
Starting Member

8 Posts

Posted - 11/22/2012 :  08:26:57  Show Profile  Reply with Quote
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,
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1274 Posts

Posted - 11/22/2012 :  08:40:12  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/22/2012 :  12:19:02  Show Profile  Reply with Quote
Simple google search will give you:
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-maintenance-plan-using-wizard.html
Go to Top of Page

arie01
Starting Member

8 Posts

Posted - 11/23/2012 :  08:09:42  Show Profile  Reply with Quote
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,

Edited by - arie01 on 11/23/2012 09:22:48
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/23/2012 :  11:41:51  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/23/2012 :  11:57:26  Show Profile  Reply with Quote
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.
Go to Top of Page

mrduckstoo
Starting Member

USA
1 Posts

Posted - 01/28/2013 :  13:01:17  Show Profile  Reply with Quote
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
Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 01/30/2013 :  09:39:19  Show Profile  Reply with Quote
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!
Go to Top of Page

Hommer
Aged Yak Warrior

794 Posts

Posted - 01/30/2013 :  10:09:05  Show Profile  Reply with Quote
OK, now I saw this Clean Up History task. I have added it into the flow. That seems is the answer.
Go to Top of Page

cyberguest
Starting Member

1 Posts

Posted - 02/01/2013 :  13:11:11  Show Profile  Reply with Quote
You can do something like this:



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

Hommer
Aged Yak Warrior

794 Posts

Posted - 02/05/2013 :  09:43:05  Show Profile  Reply with Quote

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.

Go to Top of Page

ahmeds08
Aged Yak Warrior

India
693 Posts

Posted - 02/26/2013 :  00:08:30  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
fortfiles is an executable you need to install on the machine,which deletes the old files based on the retention period the user gives.
Go to Top of Page

AlexVlg
Starting Member

3 Posts

Posted - 07/22/2013 :  02:07:02  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000