1. First do full backup for target database.
eg:
BACKUP DATABASE [test1] TO DISK = N'F:\F3\dbbackup\test1_201512091623.bak' WITH NOFORMAT, NOINIT, NAME = N'test1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'test1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'test1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''test1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\F3\dbbackup\test1_201512091623.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
2. run following to know how big database log files are:
DBCC SQLPERF (logspace)
3. run DBCC SHRINKFILE to reduce database log file:
use test1;
dbcc shrinkfile (test1_log,128,truncateonly)
4. repeat step 2 to see if log file got reduced. If not good enough, continue
DBCC SQLPERF (logspace)
5. Backup transaction log file of target database (if it is running in full mode)
BACKUP LOG [test1] TO DISK = N'F:\F3\dbbackup\test1_201512091655.trn' WITH NOFORMAT, NOINIT, NAME = N'test1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'test1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'test1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''test1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\F3\dbbackup\test1_201512091655.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
6. repeat step 3
7. repeat step 2 and step 5,6 until you reach your goal.