February 8, 2010 at 6:57 am
I know that a differential backup is the changes of a database from the last full backup taken. This is what is creating a problem for me.
I have 3 jobs that run on a server:
Job 1 is a stored procedure that toggles through databases on the server and does a full backup to a particular location (locationA). This runs every night and we only keep 2 days worth of this full backup at a time.
BEGIN
SET NOCOUNT ON
DECLARE @DB_NAME VARCHAR(200)
DECLARE @DB_LOCATION VARCHAR(200)
DECLARE @DB_DATE VARCHAR(200)
DECLARE @DB_FILE VARCHAR(200)
DECLARE @LOG VARCHAR(255)
DECLARE @COUNT INT
DECLARE @iRowCount int
DECLARE @sDATE varchar(200)
DECLARE @newDate varchar(200)
DECLARE DB_CUR CURSOR FOR
SELECT [NAME] FROM dbo.sysdatabases
WHERE dbid > 4 and [NAME] not like '%SNAPSHOT%' and [NAME] <> 'HISTORY_SWITCH'
ORDER BY dbid
SET @COUNT = (SELECT COUNT(*) FROM dbo.sysdatabases
WHERE dbid > 4 and [NAME] not like '%SNAPSHOT%' and [NAME] <> 'HISTORY_SWITCH')
OPEN DB_CUR
FETCH NEXT FROM DB_CUR INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DB_DATE = CAST(DATEPART(YYYY,getdate()) AS CHAR(4)) + RIGHT(CAST(100+DATEPART(MM,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(DD,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(HH,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(MI,getdate()) AS CHAR(3)),2)
SET @DB_FILE = @DB_NAME + '_backup_' + @DB_DATE + '.bak'
SET @DB_LOCATION = '\\locationA\' + @DB_FILE
SET @LOG = 'BACKUP DATABASE ' + @DB_NAME + ' TO DISK = ' + @DB_LOCATION + ' WITH NOFORMAT, NOINIT, NAME = ' + @DB_FILE + ', SKIP, REWIND, NOUNLOAD, STATS = 10'
INSERT INTO TEMP_BACKUP_LOG(DBName,[Type], Code, Date)
SELECT @DB_NAME as N, 'Full' as T, @LOG as C, getdate() as D
BACKUP DATABASE @DB_NAME TO DISK = @DB_LOCATION WITH NOFORMAT, NOINIT, NAME = @DB_FILE, SKIP, REWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM DB_CUR INTO @DB_NAME
END -- DB_CUR Cursor
CLOSE DB_CUR;
DEALLOCATE DB_CUR;
SET @sDATE = CAST(DATEPART(mm,getdate()) AS VARCHAR(20)) + '/' + CAST(DATEPART(dd,getdate()) AS VARCHAR(20)) + '/' + CAST(DATEPART(yyyy,getdate()) AS VARCHAR(20))
SET @iRowCount = (select count(*) from TEMP_BACKUP_LOG where CAST(DATEPART(mm,date) AS VARCHAR(20)) + '/' + CAST(DATEPART(dd,date) AS VARCHAR(20)) + '/' + CAST(DATEPART(yyyy,date) AS VARCHAR(20)) = @sDATE)
RETURN (@COUNT - @iRowCount)
END
Job 2 is similar to Job 1 only it backs up to a different location (locationB) and this job is run once a week on Sunday.
Job 3 is similar to Job 1 and 2 only it is a differential backup that backs up to locationB and this is run everyday.
Here is my problem. Since I have a full backup that runs everyday, the differential is of this full backup but I need the differential to be of the full backup that is run on Sundays.
Is there a way, if any of doing this?
February 8, 2010 at 7:16 am
You could make every backup other than the sunday backup a COPY ONLY backup, however there's some problem in restoring copy_only backups using the management studio GUI (T-SQL script works fine)
Why do you need to do this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2010 at 7:26 am
These databases are very large and very active and they want local full backups for them to restore from but they also want to keep a history of the databases. I only have space for 2 full backups and to save space for history backup I decided to do differentials. 2 full backups of these databases take about 1.2T. So what is the difference between a copy only backup and a regular? Needing to restore from T-SQL is not a problem.
GilaMonster (2/8/2010)
You could make every backup other than the sunday backup a COPY ONLY backup, however there's some problem in restoring copy_only backups using the management studio GUI (T-SQL script works fine)Why do you need to do this?
February 8, 2010 at 7:35 am
A copy_only doesn't reset the differential base. That's about it.
Depending on how active the DB is, those diffs may not be appreciably smaller than the fulls over a period of a week.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2010 at 7:40 am
Yes well they are testing SQL2008 so we can get some compression on the backups. Thanks for your speedy help with this matter. I believe doing a copy_only is exactly what I am looking for.
GilaMonster (2/8/2010)
A copy_only doesn't reset the differential base. That's about it.Depending on how active the DB is, those diffs may not be appreciably smaller than the fulls over a period of a week.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply