SQL Server Performance Forum – Threads Archive
Shrink log files
In production one of the DBs has the following sizes:Data size 770MB
Log size 9.9GB When I transfer nightly backups to Dev environemt, the developers cannot restore on their local. Then I have to change the DBs recovery mode to Simple in EM and then run the following in QA.
DBCC SHRINKFILE(Repo_Log, TRUNCATEONLY) Then backup it for developers use. Isn’t it possible to backup somehow to prevent this problem?
CanadaDBA
Yes, it is possible to backup somehow to prevent this problem: – you may add a step to take transaction log backup with ‘WITH INIT’ clause, just before full backup starts. USE [dbName]
GO
BACKUP LOG [dbName] TO [TranBackupDevice] WITH INIT , NOUNLOAD , NAME = ‘dbName transactionslog backup’, NOSKIP , NOFORMAT
GO –step (2) BACKUP DATABASE [dbName] TO [FULLBackupDevice] WITH INIT , NOUNLOAD , NAME = N’DBName Full Backup’, NOSKIP , NOFORMAT
GO Deepak Kumar –An eye for an eye and everyone shall be blind
Hey Kumar,
Thanks for the reply. I am going to study about your suggestion but before that can you explain what is the result of the step (1) and (2)? Thanks, CanadaDBA
Step -1 will take transaction log backup and store it on the path given in backupDevice. so its like emptying the TL before full backup.
Step-2 will take full backup as you do. Now the difference will be everytime the job will run, TL will get empty and will be override everytime to avoid increment in backup space.
NOte:- There will be NO increment in database backup duration/timings, even if you are adding TL backup job Because Full database backup : backs up the entire database including the transaction log.
Note: – I have added INIT switch in both jobs and BOL says about it
INIT Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.
Note: – using this backup stretegy you can restore database till last full backup and for recoverying whole database till last transaction happend.. you need to apply latest tail of TL ( if avialable after crash)
Deepak Kumar –An eye for an eye and everyone shall be blind
]]>