Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backing up multiple databases on the Daily baises Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 2:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
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.
Post #974467
Posted Tuesday, August 24, 2010 3:02 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 17,808, Visits: 15,729
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
Post #974472
Posted Tuesday, August 24, 2010 3:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
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.
Post #974481
Posted Tuesday, August 24, 2010 3:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 17,808, Visits: 15,729
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
Post #974497
Posted Tuesday, August 24, 2010 4:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
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.
Post #974515
Posted Tuesday, August 24, 2010 4:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:39 AM
Points: 31,176, Visits: 15,615
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #974522
Posted Tuesday, August 24, 2010 4:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 17,808, Visits: 15,729
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
Post #974524
Posted Wednesday, August 25, 2010 1:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
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.
Post #974671
Posted Wednesday, August 25, 2010 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:39 AM
Points: 31,176, Visits: 15,615
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #974837
Posted Wednesday, August 25, 2010 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
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
Post #974936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse