Transaction log backup using powershell

%3CLINGO-SUB%20id%3D%22lingo-sub-1348617%22%20slang%3D%22en-US%22%3ETransaction%20log%20backup%20using%20powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1348617%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20looking%20at%20using%20PowerShell%20for%20backups%20of%20one%20of%20our%20SQL%20Servers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGot%20it%20working%20fine%20but%20I%20notice%20something%20when%20backing%20up%20the%20transactions%20logs%20(database%20in%20Full%20recovery%20mode).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExtreme%20example%3A%20If%20the%20transaction%20log%20file%20itself%20is%20say%202GB%20but%20only%2010MB%20is%20being%20used%2C%20a%20Maintenance%20Schedule%20backup%20will%20create%20a%20backup%20file%20only%2010MB%20in%20size.%20A%20powershell%20backup%20will%20create%20a%20500MB%20backup%20with%20compression%20on%20(2GB%20with%20compression%20off).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20for%20powershell%20command%20to%20only%20back%20up%20the%20used%20log%20space%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ejc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1799030%22%20slang%3D%22en-US%22%3ERe%3A%20Transaction%20log%20backup%20using%20powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1799030%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539470%22%20target%3D%22_blank%22%3E%40whatwaht%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EToday%2C%20here%20I%20am%20to%20provide%20you%20the%20best%20solution%20that%20easily%20backup%20your%20Transaction%20Log%20files.%20We%20know%20that%20it%20is%20difficult%20to%20back%20up%20the%20Transaction%20log%20manually.%20The%20tool%20named%20%3CA%20href%3D%22https%3A%2F%2Fwww.arysontechnologies.com%2Fsql-log-analyzer%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%3CSTRONG%3ESQL%20Log%20Analyzer%3C%2FSTRONG%3E%3C%2FA%3E%20can%20automatically%20do%20your%20log%20backup%20in%20less%20time.%20This%20tool%20easily%20repairs%20your%20LDF%20file%20and%20extracts%20the%20transaction%20log%20from%20it.%26nbsp%3B%20You%20can%20use%20this%20tool%20to%20perform%20successful%20recovery%20of%20the%20corrupt%20transaction%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802343%22%20slang%3D%22en-US%22%3ERe%3A%20Transaction%20log%20backup%20using%20powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802343%22%20slang%3D%22en-US%22%3E%3CP%3EPowerShell%20%2F%20SMO%20%2FSqlPs%20does%20nothing%20else%20then%20sending%20a%20plain%20T-SQL%20script%20to%20SQL%20Server%2C%20there%20is%20no%20difference%20in%20which%20way%20you%20perform%20a%20backup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20and%20when%20did%20you%20get%20the%20used%20size%3B%20before%20or%20after%20backup%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I was looking at using PowerShell for backups of one of our SQL Servers.

 

Got it working fine but I notice something when backing up the transactions logs (database in Full recovery mode).

 

Extreme example: If the transaction log file itself is say 2GB but only 10MB is being used, a Maintenance Schedule backup will create a backup file only 10MB in size. A powershell backup will create a 500MB backup with compression on (2GB with compression off).

 

Is there a way for powershell command to only back up the used log space?

 

thanks

 

jc

2 Replies

Hi @whatwaht 

Today, here I am to provide you the best solution that easily backup your Transaction Log files. We know that it is difficult to back up the Transaction log manually. The tool named SQL Log Analyzer can automatically do your log backup in less time. This tool easily repairs your LDF file and extracts the transaction log from it.  You can use this tool to perform successful recovery of the corrupt transaction file.

PowerShell / SMO /SqlPs does nothing else then sending a plain T-SQL script to SQL Server, there is no difference in which way you perform a backup.

 

How and when did you get the used size; before or after backup?