Backing up multiple databases on the Daily baises

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Backing up multiple databases on the Daily...

14 posts, Page 1 of 2

1 2 »»

Backing up multiple databases on the Daily baises

Rate Topic

Display Mode

Topic Options

Author

Message

tt-615680

Posted Tuesday, August 24, 2010 2:55 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:42 AM
Points: 225, Visits: 809

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

SQLRNNR

Posted Tuesday, August 24, 2010 3:02 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #974472

tt-615680

Posted Tuesday, August 24, 2010 3:15 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:42 AM
Points: 225, Visits: 809

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

SQLRNNR

Posted Tuesday, August 24, 2010 3:41 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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.

Post #974497

tt-615680

Posted Tuesday, August 24, 2010 4:07 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:42 AM
Points: 225, Visits: 809

CirquedeSQLeil (8/24/2010)

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

Steve Jones - SSC Editor

Posted Tuesday, August 24, 2010 4:22 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,423, Visits: 13,736

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

SQLRNNR

Posted Tuesday, August 24, 2010 4:24 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

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.

Post #974524

tt-615680

Posted Wednesday, August 25, 2010 1:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:42 AM
Points: 225, Visits: 809

CirquedeSQLeil (8/24/2010)

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

Steve Jones - SSC Editor

Posted Wednesday, August 25, 2010 7:24 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,423, Visits: 13,736

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

tt-615680

Posted Wednesday, August 25, 2010 9:09 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:42 AM
Points: 225, Visits: 809

Steve Jones - Editor (8/25/2010)

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions