Introduction

In this article we will learn how to monitor the size of data and transaction log files in SQL Server and how to backup log files if they exceed a specified size in PowerShell.

If you do not have any experience at all in PowerShell, this article is for you. If you have some experience with PowerShell, you may find some very interesting tips in this article that you did not know.

We will do the following:

1. Detect all the data files and transactional log files of a specified size.
2. How to display the size of files in MB, GB (by default they are in bytes)
3. Learn how to do a full and transactional log backup in PowerShell using specific cmdlets.
4. Learn how to run any SQL script in PowerShell.
5. Detect databases with Full Recovery Model (because you cannot do a transaction log backup in a database with simple recovery model).
6.  Learn how to backup the transaction log of all the databases with full recovery mode.

Requirements

We need the following requirements:

1. SQL Server 2008 or later. In this article, we are using SQL Server 2016.
2. Some databases installed.

Getting Started

In SSMS, we need to find the path of the data files and log files. To do this, go to the Databases folder and right click on the database you want to use. Then select Properties:

On Files page, go to Path. In that field, you will find the path on disk for the data and log files:

In order to start using Powershell in SSMS, right click any node and select Start PowerShell:

ls

The result will show a list of the data files and transaction log files:

The ls command is used to list directories and files. It is the same as "dir", or you can use get-childitems. These are all synonyms in PowerShell.

By default, the data and log files are in the same folder. However, it is a good practice to separate them on different disks. If you want to see only the main data files and not log files, you need to use this command:

ls *.mdf

You will get a list of the main data files (mdf):

For main data files, you use the mdf extension. For secondary data files, you use the ndf extension. If you check the Length property (size of the file in), you will notice that the datafile size is in bytes. If you want to see the data file name and the size in MB, you have to use the following commands:

ls *.mdf | select name, @{Name="MB";Expression={$_.Length / 1Mb}}

You will be able to see data files and their size in MB:

You can see the size in GB using {$.Lenght / 1 Gb}}. The following command will show all the files with a size greater than 10 MB:

-gt means greater than. You can also use the following comparison operators:

If you want to see the transaction log files with a size greater than 10 MB, you can use the following commands:

ls *.mdf | select name,Length, @{Name="MB";Expression={$_.Length / 1Mb}} | where  {($_.Length / 1Mb) -gt 10}

The Transaction Log files (*.ldf) in the current folder will be displayed:

If you want to export the results to a CSV file, the following commands can be used:

ls *.ldf | select name,Length, @{Name="MB";Expression={$_.Length / 1Mb}} | where  {($_.Length / 1Mb) -gt 10} | Export-Csv c:\sql\test.csv

With Export-csv you can export your results to a csv file:

You can open the file in Notepad or any other program.

When the transaction log file is big, you can backup the transaction log to truncate it. In this new example, we will do this in PowerShell. Before running a backup of the transaction log, you will need to do a full backup. The following example shows how to backup the sqlservercentraldatabase:

Backup-SqlDatabase -ServerInstance "MyServer" -Database "sqlservercentral"

MyServer is the SQL Instance name and sqlservercentral is the name of the database to backup.

Once you have a full backup, you can backup the transaction log with this command:

Backup-SqlDatabase -ServerInstance "MyServer" -Database sqlservercentral -BackupAction Log 

Another way to backup a transaction log is using a SQL Script and executing it in PowerShell. We will generate a script in SSMS. To do this, in SSMS, right click the database to backup and select Tasks>Back Up:

In Backup type, select Transaction Log to backup the transaction log file:

Select Script>Script Action to File to save the backup log to a script file:

Specify the path and name of the script:

To execute a SQL script in PowerShell, run this command:

Invoke-Sqlcmd -InputFile "C:\sql\backuplog.sql" | Out-File -filePath "C:\sql\backupInfo.log" 

Where backuplog.sql is the script to execute and backupInfo.log is the output file that contains errors or any information displayed during the backup.

If you want to check if the backup was created, you can query the backupfile  table in the system MSDB database using SSMS:

USE MSDB
GO
SELECT 
      [logical_name]
      ,[physical_drive]
      ,[backup_size]
  FROM [msdb].[dbo].[backupfile] 

You will be able to see the full and log backups created:

A typical error when you try to backup the transaction log in a database is that the recovery model is Simple:

When the recovery model is simple, a lot of transaction log entries are not retained, and that is why you cannot do a transaction log backup in a database with the simple recovery model.

To see the recovery model of a database, you can right click a database and check the properties in SSMS:

Select the Options page and look for Recovery Model:

We will now check how to see the recovery model in PowerShell in all the databases. First, go to the Databases folder in PowerShell with this command:

cd 'PS SQLSERVER:\SQL\YourInstance\DEFAULT\Databases'

In Databases, run ls, dir, or Get-childitems:

ls

You will be able to see the database information:

If you want to see only databases with the Full recovery model, use these commands:

ls | select name,recoverymodel | where  {($_.Recoverymodel -eq "Full")} 

You will be able to see all the databases with Full Recovery Model:

We will store these results in a variable named $listfullrecovery:

$listfullrecovery=ls | select name,recoverymodel | where  {($_.Recoverymodel -eq "Full")} 

You can check the variable by calling it:

$listfullrecovery

As you can see, the names are stored successfully:

You can lowercase the database names using the function ToLower(). The sentence will show the database names in lowercase:

$listfullrecovery=$listfullrecovery | select @{Name="Name";Expression={$_.name.ToLower()}}

Now, return to the path where the transaction log files are stored:

cd 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data'

The following commands stores the name of all the transaction log files with a size greater than 10 MB in size in a variable named $listlogs:

$listlogs=ls *.ldf | select @{Name="Name";Expression={$_.name.ToLower()}},Length  | where  {($_.Length / 1Mb) -gt 10}

If you check the variable, you will notice that the transaction log file names include the suffix, _log.ldf. If we want to compare with the databases with full recovery model, we need to remove that suffix:

In order to remove the _log.ldf suffix, we can replace the suffix using the following commands:

$listlogs=$listlogs | select @{Name="Name";Expression={$_.name.Replace("_log.ldf","")}}

The $_.name.Replace("_log.ldf","") command removes the string _log.ldf.

Finally, we will backup the transaction log of all the databases with full recovery model:

foreach ($item in $listfullrecovery) {
       if ($listlogs.Name -contains $item.Name) {
       
Backup-SqlDatabase -ServerInstance "myserver" -Database $item.Name -BackupAction Log  
    }      
}

The sentence foreach will check all the databases with full recovery model. If the log file for a database is larger than 10 MB, the script will backup the transactional log of the database.

Conclusion

As you can see, PowerShell is a nice tool to work with physical files and databases. It is a very powerful tool to integrate with SQL Server.

In this article, we learned how to detect files bigger than a specified size and then we learned how to backup the logs of all the databases with the Full Recovery Model.

If you liked this article, I strongly recommend reading our Stairway to PowerShell series. If you did not like it, send us your comments or questions.

References

• About_Comparison_Operators
• Stairway to PowerShell
• Basic of PowerShell Looping