Tuesday, July 10, 2012

Microsoft SQL Server 2005/2008:Backup Transaction Logs Script.



Use this script to take backup of transaction logs.

1.Create [Transaction_Logs_backup] Stored Procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE Procedure [dbo].[Transaction_Logs_backup]
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(500) -- path for backup files
DECLARE @fileName VARCHAR(500) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @Sql varchar(500)-- Script to take backup of transaction

SET @path = '\\192.168.0.242\Database Backup\192.168.0.110\Transaction_logs\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),105)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 



BEGIN 
       SET @fileName = @path +'Transaction_log_'+ @name + '_' + @fileDate + '.trn'

       SET @Sql='USE [' + @name + '] BACKUP LOG '+@name+' TO DISK ='''+@fileName +''''
       print 'Backup Started...'+@name       
       print '   '
       EXEC(@Sql)
       print @Sql
       print '   '       
       print 'Backup Completed...'+@name       
       print '   '
       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

2.Execute this procedure.

EXEC [Transaction_Logs_backup]

Check Messages....

Backup Started...1
  
Processed 112 pages for database '1', file '1_log' on file 1.
BACKUP LOG successfully processed 112 pages in 0.071 seconds (12.908 MB/sec).
USE [BTS] BACKUP LOG BTS TO DISK ='C:\1.trn'
  
Backup Completed...1
  
 

Microsoft SQL Server 2005/2008:Shrink Database transaction Logs script



1.Take Database backup.

2.Backup Transaction Logs.

Use this link,Click Here



3.Use this script to shrink transaction log.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure Trunc_Transaction_log

as

begin

--Check Transaction log Usage
DBCC SQLPERF(logspace)




-- step 1. get hold of the entire database names from the database server
CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )

DECLARE DataBaseList CURSOR FOR
SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')


-- step 2. insert all the database name and corresponding log files' names into the temp table
DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX)

OPEN DataBaseList FETCH
NEXT FROM DataBaseList INTO @DataBase
WHILE @@FETCH_STATUS <> -1
BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT '''
+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'
--SELECT @SqlScript
EXEC(@SqlScript)
FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server
DECLARE TransactionLogList CURSOR FOR
SELECT DatabaseName, LogFileName FROM #TransactionLogFiles
DECLARE @LogFile VARCHAR(128)

OPEN TransactionLogList FETCH
NEXT FROM TransactionLogList INTO @DataBase, @LogFile
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @SqlScript = 'USE [' + @DataBase + '] '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '
+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'

print 'Shrinking...'+@LogFile
print '       '

EXEC(@SqlScript)
print '       '
print 'shrank...'+@LogFile
print '       '

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
DROP TABLE #TransactionLogFiles

--Check Transaction log Usage after shrink
DBCC SQLPERF(logspace)


end

Execute this ..

Exec Trunc_Transaction_log

Check Messages...

Shrinking...1_log
      
Cannot shrink log file 2 (1_log) because total number of logical log files cannot be fewer than 2.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...MediaSurvey_log
      
Shrinking...TRO_Log
      

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...TRO_Log
      
Shrinking...NEWRM_log
      

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...NEWRM_log
      
Shrinking...2_log
      

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank..2_log
      
Shrinking...3_log
      

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...3_log