7/15/2015

SQL Server T-SQL Transaction Backup Script

I've never been a full time DBA.  But sometimes, working as Business Intelligence consultant, there's a need to maintain the database.

So today I had to backup each database, first Full, then Transactional, and reduce the log file size.

So the first thing to do is to make sure your database is in Full recovery mode.

If not, run a query:


ALTER DATABASE YourDatabaseName SET RECOVERY FULL;


It's good practice to perform a Full database prior, actually required I think.  So I just used SSMS wizard to perform the standard backup each time.

Then I used this script to perform the Transactional backup.  The path defaults to the default SQL Server instance path.  This is a good option so you don't have to worry about permission access denied errors.

You have to set the database name in a few places, everywhere it says 'YourDatabaseName' replace with your db name.  It then determines the Log file name, and gets it ID, to be used further downstream.

The key is to run this exact script 2x.  The first time does not reduce the log file size.  But the 2nd time does.  I ran this script on about 8 databases today.  With the largest database log file size 250gb.  Quite large.



Use [YourDatabaseName]
go
--SELECT * FROM sys.database_file
-----------------------------------
DECLARE @DatabaseName NVARCHAR(100)
DECLARE @DatabaseLogFilename NVARCHAR(100)
DECLARE @path NVARCHAR(255)
DECLARE @File_ID int
-----------------------------------

SET @DatabaseName = db_name() 
SELECT @DatabaseLogFilename = [name] FROM sys.database_files where [Name] like '%log%'
-----------------------------------
select recovery_model_desc from sys.databases where name = @DatabaseName
-----------------------------------
SELECT @File_ID = file_id FROM sys.database_files where [name] = @DatabaseLogFilename
SELECT @File_ID as intFile_ID
SELECT @DatabaseName as DatabaseName
SELECT @DatabaseLogFilename as DatabaseLogFilename
-----------------------------------
SET @path= N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'
   + @Databasename + '_'
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';
-----------------------------------
SELECT @path as DestinationPath
-----------------------------------
BACKUP LOG @DatabaseName TO DISK = @path WITH INIT, COMPRESSION;
-----------------------------------
----SELECT * FROM mds..SYSFILES
DBCC SHRINKFILE (@File_ID, 100);
DBCC LOGINFO;
GO

Yes, I'm aware that ShrinkFile is evil.

Anyway, good luck using the script.  Let me know if you find issues or have suggestions~!
Thanks,
JB

No comments:

Post a Comment

Bloom Consulting Since Year 2000