| Author |
 Topic  |
|
|
arie01
Starting Member
5 Posts |
 Posted - 11/21/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/21/2012 : 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)
USA
6997 Posts |
Posted - 11/21/2012 : 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 |
Edited by - Michael Valentine Jones on 11/21/2012 17:53:49 |
|
|
|
arie01
Starting Member
5 Posts |
Posted - 11/22/2012 : 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
Flowing Fount of Yak Knowledge
India
1256 Posts |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
|
|
arie01
Starting Member
5 Posts |
Posted - 11/23/2012 : 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, |
Edited by - arie01 on 11/23/2012 09:22:48 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/23/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/23/2012 : 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
USA
1 Posts |
Posted - 01/28/2013 : 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 |
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 01/30/2013 : 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
693 Posts |
Posted - 01/30/2013 : 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 Posts |
Posted - 02/01/2013 : 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
693 Posts |
Posted - 02/05/2013 : 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
Constraint Violating Yak Guru
India
423 Posts |
Posted - 02/26/2013 : 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. |
|
|
| |
Topic |
|
Database backup ZIP and cleanup
