June 25, 2008 at 2:51 pm
Can someone point to me to a Backup Transaction Log 101 instructions? All I need are step by step instructions on how to backup transaction logs.
In addition, I need help reducing the size of the Transaction LogstxtPost_CommentEmoticon(':hehe:');
:hehe:
June 25, 2008 at 3:22 pm
1) read BOL. it has very good info regarding how transaction logs work and how to backup.
2) backup log
BACKUP LOG [yourdb] TO [yourdbLogBUDevice]
WITH NOINIT , -- choose init / noinit (ref BOL)
NOUNLOAD , NAME = 'yourdb_Log',
SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT
3) Full backup the db !
4) shrink logfile (only if realy needed !!)
This should not be done regularly !!
SQL did need the log space to support your db !!
(you may need to repeat this sequence !
--INF: How to Shrink the SQL Server 2005 Transaction Log
-- zie http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sys.database_files
USE your_db -- This is the name of the database for which the log will be shrunk.
go
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'your_Log', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 26, 2008 at 8:37 am
1. A simple example for BACKUP LOG command:
BACKUP LOG DatabaseName
TO DISK = 'BackupFileName.TRN'
2. Use Maintenance Plan schedule the job.
3. Third party tools are available which create compressed backup file.
4. Crosscheck and make sure that backup files are readable.
5. Be familiar with restore process.
June 26, 2008 at 9:32 am
i run Evault backup software for SQL backups and just set a schedule for full and log backups. you can do this with veritas and any other backup software
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply