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

1 Reply

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?