Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Script: Transaction Log Backup

My last post showed a little script that I use for taking full backups of all my databases on an Instance.

This script below will take a transaction log backup of all your database in full recovery mode, again it can be used in conjunction with the SQL agent to schedule it regularly. This is one size fits all to transaction log backups though and this may not fit all environments perfectly. If you have a database that needs a different more specific schedule from transaction log backups on the instance in question then you will need to find a way to work that in.

DECLARE @DBName varchar(255)
DECLARE @DS VARCHAR(50)
DECLARE @Path VARCHAR(255)



DECLARE TLOG_BACKUP CURSOR FOR

select name from sys.databases
where recovery_model = 1 --Only return databases in full recovery
AND name NOT IN ('TEMPDB') -- Exclude TEMPDB
AND state = 0 --Exclude offline databases, they won't backup if they offline anyway
AND Source_database_id is null -- Removes snapshots from the databases returned

OPEN TLOG_BACKUP


FETCH NEXT FROM TLOG_BACKUP
INTO @DBName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the filename values of the transaction log backup files
SET @DS = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '
/', '') + '_'
+ REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), '
:', '')
SET @Path = '
C:\DATA\Backup\'
SET @Path = @path + @DBNAME + '
_'+ 'LOG'+'_' + @DS + '.trn'
--Take the backup

BACKUP LOG @DBNAME
TO DISK = @Path
WITH
FORMAT, INIT, SKIP, NOREWIND,
NOUNLOAD, STATS = 10

FETCH NEXT FROM TLOG_BACKUP
INTO @DBName


END

CLOSE TLOG_BACKUP
DEALLOCATE TLOG_BACKUP
GO

Comments

Posted by amandix on 21 March 2010

Sory, but may you can help me here:

Do you known a method of object tampering within SQL Server 2008?

The goal is to use an attached copy of mssqlsystemresource db and alter some system stored procedures, and then replace this database with the original one. This operation was possible in SQL 2005... and saved me many hours of work.

Posted by Anonymous on 22 March 2010

Pingback from  Dew Drop – March 22, 2010 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.