Differential Backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Differential Backup

Author

Topic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-07-06 : 15:27:28

I would like to use T-SQL to do a Differential Backup of a Database that has multiple filegroups and is also involved in replication :)

I've been through the backup_Database syntax and I don't see where to apply a daily differential schedule for the backup to run.

Is it the norm to set your backups and backup schedules via EM?

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-06 : 15:56:28

You setup a job using either Enterprise Manager or Query Analyzer. In the job, you use BACKUP DATABASE. For a differential backup, you use WITH DIFFERENTIAL option.

Tara

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-07-06 : 16:17:10

Tara , thanks for the replies to other posts so far helped a TON!!! What i am trying to do now is backup filegroup to a striped set using the following code. The command completes successfully but when I go into EMM the files are added but the complete database backup radio button is selected. I then select Filegroups option then specify the filegroup to backup and apply a schedule to it and it will not save the settings. It keeps going back to complete databse backup with no schedule attached. Any ideas?

backup database ISA
file = 'Firewall',
Filegroup = 'FWALL'
to disk = 'D:\DatabaseBak\ISA\Filegroup\Fwall1.bak',
disk ='D:\DatabaseBak\ISA\Filegroup\Fwall2.bak',
disk ='D:\DatabaseBak\ISA\Filegroup\Fwall3.bak'
go

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-06 : 16:19:33

To apply a schedule to your command, go to SQL Server Agent in Enterprise Manager under Management folder. Then jobs. Right click in the right window pane and select new job. Name the job (first screen, select sa as the owner). Add a job step (second tab). Put your command in a step. Add a job schedule (third tab).

Tara

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2004-07-06 : 16:45:09

I'm not sure if I completely understand your question. But the syntax you provided will not create a differential backup it will create a full backup of the FWALL filegroup. If you want a differential backup you will need to specify WITH DIFFERENTIAL ie..

backup database ISA
file = 'Firewall',
Filegroup = 'FWALL'
to disk = 'D:\DatabaseBak\ISA\Filegroup\Fwall1.bak',
disk ='D:\DatabaseBak\ISA\Filegroup\Fwall2.bak',
disk ='D:\DatabaseBak\ISA\Filegroup\Fwall3.bak'
WITH DIFFERENTIAL
go

You can then place this command in a job that will perform this backup on a scheduled basis.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-06 : 17:17:06

Yes WITH DIFFERENTIAL needs to be added as mentioned in my first reply.

Run that command in Query Analyzer with the new option and see if it works. Then schedule it as mentioned in my second reply.

Tara

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-07-06 : 17:49:10

So therefore I will have 2 Jobs applied to this particular filegroup then? One reoccuring weekly FULL backup, Then another Job that runs differential backups scheduled on weekdays?

Also is there anything i need to do with the transaction log since I am using filegroups? Like if a file group fails will I still be able to restore using the transaction log, can you specify a filegroup for the transaction log to restore to or is it a database wide restore? I plan on using multiple file groups with ea group striped and is it ok to have all these backup jobs running? Should I spread out the times they backup etc? Thanks again

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-06 : 18:01:21

Yes you will need two jobs. We don't use differential backups. What we do is backup the transaction log every 15 minutes and do a FULL once per day.

Transaction logs don't really have to do with filegroups. If your database recovery model is not set to SIMPLE (yours is set to FULL based on other threads), then you need to perform regular transaction log backups.

Backup times should be spread out.

Tara

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-07-06 : 20:17:55

Tara can you help me with getting transaction log backed up with a filename that corresponds to date and appends? Right now my log backup is just overwriting and i cant find anything in BOL on how to do this via T-SQL. Be nice if I could create folders for ea day of week for it to write to as well. Hope this makes sense

Keithc MCSE MCSA

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 20:45:51

To answer an earlier bit: There are NO differential backups on offer via the Maintenance Wizzard.

Nothing to stop you making the filename in an @VARIABLE including the date/time. You could mess about with folders for each day of the week, but I'm not sure why you would bother? I use a table to store the time/name of the backup and then I delete files (as part of my backup process) that are "old"

Hopefully you can guess what the various variables do in this example, if not please ask!


SELECT	@Name = @strDB + '( Daily Backup )', 
	@MediaName = @strDB + '_Dump' + CONVERT(varchar, GetDate(), 112), 
	@BackupFile = 	@strRootPath
			+ @strDB 
			+ '_'
			+ CONVERT(varchar, GetDate(), 112)				-- yyyymmdd
			+ '_'
			+ REPLACE(LEFT(CONVERT(varchar, GetDate(), 108), 5), ':', '')	-- hhmm
			+ '_'
			+ CASE @intType
				WHEN 1 THEN 'Full' 
				WHEN 2 THEN 'Diff'
				WHEN 3 THEN 'Trans'
				ELSE '??' + COALESCE(CONVERT(VARCHAR(20), @intType), 'NULL') + '??' END
			+ '.BAK', 
	@Description = CASE @intType
				WHEN 1 THEN 'Normal' 
				WHEN 2 THEN 'Differential'
				WHEN 3 THEN 'Transaction'
				ELSE '??' + COALESCE(CONVERT(VARCHAR(20), @intType), 'NULL') + '??' END
			+ ' Backup at ' + CONVERT(varchar, GetDate(), 113) + '.' 
-- Might be an idea to put the Server name in here too

I use the various bits of the BACKUP commnds Media/Description to add more meat:


BACKUP DATABASE @strDB 
	TO DISK = @BackupFile
WITH	
	DESCRIPTION = @Description,
	MEDIADESCRIPTION = @Description, 
	MEDIANAME = @MediaName,
	NAME = @Name,
	STATS = 10

(I have variations for @intType of Diff/Trans backup)

Keep in mind that if you need to restore you'll need the Full backup, then Differential, then all the Transaction files. And you'll probably be under pressure ... so having something that can list the RESTORE command, based on the table of backups, can be handy. Don't delete the FULL backup until the next one is done (unless you need the disk space, of course) - saves the additional step of having to restore from tape first. (Actually the RESTORE tool in EM is quite good for this, but it takes an age to load the data ... but you can then just select the appropriate FULL / DIFF and TRANS files you want to restore - it will record the backups you make, in MSDB, even though you haven't used the wizzard)

Oh, and by the by, make sure you clear down the history in MSDB regularly, otherwise it becomes a HUGE task once MSDB gets big. The MS-provided SProcs to do this have awful performance problems once MSDB gets big.

Its all covered in Tara's blog ...

Kristen

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-07-06 : 21:17:32

Kristen, I'm not at that level of T-SQL yet I understand parts but not the whole picture, if you wouldn't mind explaining the flow of it a little bit for me i'd appreciate it. The variables are where my difficulty is. I'm not familiar with like this
@Name = @strDB
or this
DESCRIPTION = @Description

I'm looking at the code tring to see what @strDB is set to? Like where it's declared. Thanks for any help

I catch on quick after a long time

Keithc MCSE MCSA

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 21:52:57

> I'm looking at the code tring to see what @strDB is set to? Like where it's declared.

I didn't declare it, so that's why you are confused! I assumed, wrongly, from your number of posts that it would all look ever-so familiar!

So something like:


DECLARE @strDB		nvarchar(255),	-- Name of Database
        @name		nvarchar(255),	-- Name of backup
        @BackupFile	nvarchar(255),	-- Path/Filename
	@MediaName	nvarchar(255),	-- Media Name
	@Description	nvarchar(255)	-- Description

SELECT	@strDB = 'MyDatabaseName'	-- Name of DB to backup
SELECT	@BackupFile = 'D:\MSSQL\BACKUP\' 
		+ @strDB
		+ '_'
		-- yyyymmdd
		+ CONVERT(varchar(8), GetDate(), 112)
		+ '_'
		-- hhmm
		+ REPLACE(LEFT(CONVERT(varchar(10), GetDate(), 108), 5), ':', '')
		+ '.BAK', 
	@Name = @strDB + '( Daily Backup )',
	@Description = 'Backup of database ' 
		+ @strDB
		+ ' from server '
		+ @@servername
		+ ' on '
		+ CONVERT(varchar(24), GetDate(), 113)

-- Debug use only:
SELECT	[@strDB]=@strDB, [@BackupFile]=@BackupFile, 
	[@Name]=@Name, [@Description]=@Description

-- Now perform the backup
BACKUP DATABASE @strDB 
	TO DISK = @BackupFile
WITH	
	DESCRIPTION = @Description,
	NAME = @Name,
	STATS = 10

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 11:58:19

Here's what I use for transaction log backups. Take a look at all the database maintenance routines for an idea of how I do things.

http://weblogs.sqlteam.com/tarad/archive/2004/06/18/1640.aspx

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources