August 24, 2010 at 2:56 pm
I want to backup multiple Databases on Daily biases and create a file for each Database. I would like the backups to have datetime stamp. I was going to use either Differential or incremental but I don't know the best solution. I would be grateful for your suggestions.
Thank you in advance.
August 24, 2010 at 3:02 pm
Let's start with some information gathering.
How big are the databases?
How many databases?
Have you considered transaction log backups?
What is the recovery model of your databases?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2010 at 3:15 pm
CirquedeSQLeil (8/24/2010)
Let's start with some information gathering.How big are the databases?
How many databases?
Have you considered transaction log backups?
What is the recovery model of your databases?
Thank you for your reply.
I'm going to perform the Backups on an external hard drive of 480GB.
The Databases combined together are 108GB, with the main Database being 90GB.
All the Databases are all set to Simple Recovery Model, so from what I know I don't need perform any Transaction Log Backups.
Thank you.
August 24, 2010 at 3:41 pm
How long does it take you to perform the full backups currently?
What is your allowable data loss in the event of a server down?
How many backups do you intend to keep?
If you elect to do a differential backup, you would need to keep your most recent full backup and most recent differential.
Correct, in simple recovery you do not need to perform transaction log backups. If the data loss potential is acceptable for that, then you are fine. However, if data loss of a day is not acceptable, you should consider changing to full recovery model and backup your tran logs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2010 at 4:07 pm
CirquedeSQLeil (8/24/2010)
How long does it take you to perform the full backups currently?What is your allowable data loss in the event of a server down?
How many backups do you intend to keep?
If you elect to do a differential backup, you would need to keep your most recent full backup and most recent differential.
Correct, in simple recovery you do not need to perform transaction log backups. If the data loss potential is acceptable for that, then you are fine. However, if data loss of a day is not acceptable, you should consider changing to full recovery model and backup your tran logs.
At the moment it takes about 2 hours and 30 minutes and growing.
It is a live Database so the less loss Data, the better but I would say few hours.
I would like to keep all the Database Backups.
I am looking for a Backup that would take less space, meaning I would like to recover the data easily if disaster occurs but at the same time I would like the backup to perform quickly and take minimal space, if that would be possible.
August 24, 2010 at 4:22 pm
If you want less than a few hours of data loss, you need to make more differentials or add log backups.
There are numerous third party backup software products that can speed up the backup process and take up less space. Red Gate (my employer) has SQL Backup, and there are also products from Idera and Quest that do the same thing.
August 24, 2010 at 4:24 pm
If you can only have a couple hours data loss at the max, then enable Full recovery model and setup a backup schedule for your tran logs.
As for the duration of the backup:
A differential will backup the changes since the last full all the way to the current. Thus, over time the differential will start taking longer and start getting bigger. But that seems like the closest to what you want.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2010 at 1:51 am
CirquedeSQLeil (8/24/2010)
If you can only have a couple hours data loss at the max, then enable Full recovery model and setup a backup schedule for your tran logs.As for the duration of the backup:
A differential will backup the changes since the last full all the way to the current. Thus, over time the differential will start taking longer and start getting bigger. But that seems like the closest to what you want.
So you're saying incremental backup is out of the question? If that's the case how about if I create different files for each Database on the Daily biases and over write the files each night?
Thank you.
August 25, 2010 at 7:24 am
A differential backup is an incremental. Please use that terminology or it becomes hard to talk about this. As Jason mentioned, the more changes that occur, the further (in time) from the full, the more time and larger this backup will be.
To restore the most amount of data, you always need:
- a full backup
- The last differential since that full backup
The schedule of your differential backups should be made to limit data loss, according to your business/environment/needs.
I'm not sure what you mean with "daily biases".
If you use the maintenance plans, you can have it create new files each day, with the timestamp in the name. If you only want to keep one day's worth of files, and if you are sure you are backing things up to tape every night, that might be OK. I don't recommend it, and I have EVERY backup with a unique file name.
There are lots of scripts here: http://www.sqlservercentral.com/search/?q=SQL+2000+backup&t=s
August 25, 2010 at 9:09 am
Steve Jones - Editor (8/25/2010)
A differential backup is an incremental. Please use that terminology or it becomes hard to talk about this. As Jason mentioned, the more changes that occur, the further (in time) from the full, the more time and larger this backup will be.To restore the most amount of data, you always need:
- a full backup
- The last differential since that full backup
The schedule of your differential backups should be made to limit data loss, according to your business/environment/needs.
I'm not sure what you mean with "daily biases".
If you use the maintenance plans, you can have it create new files each day, with the timestamp in the name. If you only want to keep one day's worth of files, and if you are sure you are backing things up to tape every night, that might be OK. I don't recommend it, and I have EVERY backup with a unique file name.
There are lots of scripts here: http://www.sqlservercentral.com/search/?q=SQL+2000+backup&t=s
Thank you for your reply, what I mean about daily baises is that I have to back them up daily hence why I need to do differential backup.
I have looked at the link that you have sent me and I was going to use the following script:
CREATE procedure sp_Differentialbackup as
DECLARE @BackupFile varchar(255), @DB varchar(50)
DECLARE @BackupDirectory nvarchar(200), @Name varchar(50)
DECLARE backup_cursor cursor
FOR SELECT name FROM master.dbo.sysdatabases WHERE name not in ('master','model','msdb','tempdb','northwind','pubs')
OPEN backup_Cursor
FETCH next FROM backup_Cursor INTO @DB
WHILE @@fetch_status = 0
BEGIN
--SET @name = @DB + '-'+CONVERT(varchar(50), CURRENT_TIMESTAMP ,112) + '.diff'
select @name = @DB +'\'+ @DB + '_'+convert(char(4),datepart(yyyy,getdate()) )+
replicate('0',2 - len(convert(varchar(2),datepart(mm,getdate())))) + convert(varchar(2),datepart(mm,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(dd,getdate())))) + convert(varchar(2),datepart(dd,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(hh,getdate())))) + convert(varchar(2),datepart(hh,getdate())) +
replicate('0',2 - len(convert(varchar(2),datepart(mi,getdate())))) + convert(varchar(2),datepart(mi,getdate())) +
'.diff'
SELECT @BackupDirectory = '(Backup Path)' ----Insert Desired Backup Path
SET @Backupfile = @BackupDirectory + @name
--print @name
--print @BackupDirectory
--print @Backupfile
backup database @DB to disk = @Backupfile WITH NAME = @Name, differential
FETCH next FROM backup_Cursor INTO @DB
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
but obviously I will need to do a full backup first so I will have to perform a full backup first then the next night I will start doing Differential backup. It might be an obvious answer but would I have to replace the following line to full or do I just take out the differential blank:
Backup database @DB to disk = @Backupfile WITH NAME = @Name, differential
August 25, 2010 at 9:24 am
The differential backup is just the addition of the differential option, as you have listed. More details here: http://msdn.microsoft.com/en-us/library/aa225964%28v=SQL.80%29.aspx
However, you need to still do full backups. Based on what you are saying, a few hours data loss, you need to do:
- Full backup once a day
- Differential backup every few hours
If you do this schedule:
00:00 - Full
02:00 - Diff
04:00 - Diff
06:00 Diff
08:00 Diff
10:00 Diff
...
through 22:00 for a diff.
That gives you a two hour data loss window. If you plan on a full backup one day, a diff the next, then you are increasing your risk every day, and could lose up to a day's worth of data.
August 22, 2011 at 1:23 am
Hey, I just met the same question. And I don't really want to cost big. So I searched software which can do incremental and deferential backup and restore, as well as some other management features. It seems the feature is quite new. I don't know weather it's safe to use...Does anyone have any idea about it? Thanks!!!!
August 24, 2011 at 12:56 am
hey 106919046,
i used todo backup to protect my sql server 2008 r2. its differential backup function woks well.
safe to try.
July 31, 2012 at 4:13 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply