Is there any issue running a full db backup and tlog backup together?

Question

Is there any issue running a full db backup and tlog backup together?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 10, 2015 at 11:36 am

#322898

Hello Experts
Willing to know if there can be any impact if a full database backup and tlog backup on a single database runs together? Does it break the log chain or create any hazards?
Typically, I want to write a script to run tlog backups on a set of databases when the full backup job runs on all the databases as say for "A" database the tlog file grows very large because after "A" db is being backed up there are 10 other databases which are backed up and during the next 35- 40 mins, the log file of db "A" grows very large.
Just seeking your advise to know if that can cause any trouble?
Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

No.

The log won't truncate until the full backup finishes, that's the only difference to running the log backup any other time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

SQL_Hunt

SSC-Dedicated

Points: 33518

December 10, 2015 at 12:36 pm

#1845328

Thnx

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Hello Gila,

Is this not a valid command to run?

Below the code I wrote to run tlog bkp when the full bkp job runs..."was testing in my local place".

EXECUTE [servername].[master]..xp_cmdshell 'BACKUP Log [AdventureWorks_2005] TO DISK = "C:\BACKUPS\BKP\Tlog_bkps\AdventureWorks_2005_20151210.bak" with compression;', NO_OUTPUT

/*

CREATE TABLE [dbo].[checkjobstep](

[id] [int] IDENTITY(1,1) NOT NULL,

[Status] [varchar](1000) NULL,

[datetime_details] [datetime] NULL

) ON [PRIMARY]

GO

*/

SET NOCOUNT ON

WHILE 1=1

BEGIN

IF EXISTS(SELECT 1

FROM msdb.dbo.sysjobs J

JOIN msdb.dbo.sysjobactivity A

ON A.job_id=J.job_id

WHERE J.name=N'Full BKP (AdventureWorks_2005)'

AND A.run_requested_date IS NOT NULL

AND A.stop_execution_date IS NULL

)

BEGIN

PRINT 'The Job : "Backup Full BKP (AdventureWorks_2005)" is STILL running'

Insert Into db_admin.dbo.checkjobstep values ('TLog_Bkp_Running', getdate())

--PRINT CHAR(13)

PRINT '#######################################################'

SELECT 'Starting A new TLog backup...'

-- Backup Log to .bak File

DECLARE @fileName VARCHAR(90);

DECLARE @db_name VARCHAR(20);

DECLARE @fileDate VARCHAR(20);

DECLARE @cmd VARCHAR(4000);

Declare @cmmd nvarchar(max);

DECLARE @servername sysname = 'servername';

SET @fileName = 'C:\BACKUPS\BKP\Tlog_bkps\'; -- change to the relevant path

SET @db_name = 'AdventureWorks_2005'; -- change to the relevant database name

SET @fileDate = CONVERT(VARCHAR(20), GETDATE(),112);

SET @fileName = @fileName + @db_name + '_' + RTRIM(@fileDate) + '.bak';

SET @cmd = 'BACKUP Log ' + quotename(@db_name) +' TO DISK = "' + @fileName + '" with compression;'

SELECT @cmd

SET @cmmd = 'EXECUTE [' + @servername +']' + '.[master]..xp_cmdshell ''' + @cmd + '''' +', NO_OUTPUT';

SELECT @cmmd

EXEC(@cmmd)

WAITFOR DELAY '00:00:01.000';

CONTINUE

END

ELSE

PRINT 'The job : "Backup Full BKP (AdventureWorks_2005)" is NOT running.'

INSERT INTO db_admin.dbo.checkjobstep values ('TLog_Bkp_NOT-RUNNING', getdate())

--PRINT CHAR(13)

PRINT '*****************************************************************'

BREAK

END

SET NOCOUNT OFF

GO

-- truncate table db_admin.dbo.checkjobstep

select * from db_admin.dbo.checkjobstep

Order by datetime_details desc

SET NOCOUNT OFF

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

I'm running the script via a job which will run by the time the full bkp job will run and will run in every 30 secs to take more frequent tlog bkps until the job (full bkp) is completed.

Thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Um...

SQL-DBA-01 (12/10/2015)
will run in every 30 secs to take more frequent tlog bkps until the job (full bkp) is completed.

The log won't truncate until the full backup finishes, that's the only difference to running the log backup any other time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Hi Gila

I modified the script i.e. after the specific full bkjp is completed, my script is taking the tlog backups. I infact did a restore test and it works flawlessly.

Thanks indeed.

Thanks.