Posts

Showing posts from July 10, 2012

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

Image
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 [&

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

Image
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 #Trans