Backup log files when they exceed a specified size in
PowerShell.
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:
Once in the PowerShell command window,
you can list all the files using ls, dir, or Get-Child-Items:
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:
Operator
|
Description
|
-eq
|
equal
|
- ne
|
not equal
|
- lt
|
less than
|
- ge
|
greater or equal
|
- like
|
wildcard operator
|
- not like
|
the opposite of like
|
- contains
|
to verify if an element exist inside
of an object
|
-notcontains
|
The opposite of contains
|
-match
|
Matches a string using regular
expressions
|
-notmatch
|
The opposite of match
|
-replace
|
Changes specified elements with new
values
|
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:
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:
$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","")}}
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.
Where do you go from here ?
Start with this series: http://www.sqlservercentral.com/stairway/91327/
Listed
below are resources, divided into various types, which will give you a start to
your journey and includes both links to books as well as online material. You
will quickly get to know the people that are talking about PowerShell and SQL
Server so you can keep up with them for future ideas Scripting in General
The resources below are general resources that will help you become more familiar with scripting in general and will have scripts that you can try out or learn more.- SQL Server Central - http://www.sqlservercentral.com/Search/?q=powershell&s=10&t=afbs&sort=relevance
- Hey Scripting Guy! http://blogs.technet.com/b/heyscriptingguy/
- Powershell.org - http://powershell.org/wp/
- Learn Scripting - https://technet.microsoft.com/en-us/scriptcenter/dd742419
There
are more resources out there, but these sites will help you get a good feel for
scripting in general and may give you some ideas of how to better tailor
PowerShell scripting for your environments.
Tool Building
Tool building will be a very important skill for you to acquire. It is one thing to have scripts that you have built and use, but it is quite another to build your arsenal of scripts into a tool that is more portable and accessible with a single command. Some would just call these tools ‘Modules', but whether they are modules or a collection of functions or cmdlets that you have created, it will be an asset to you wherever you go.Some tools that are very useful for every day PowerShell with SQL Server are listed below:
- SQLPSX - http://sqlpsx.codeplex.com/
- PSCX - http://pscx.codeplex.com/
- Out-DataTable on the TechNet script gallery
- Write-DataTable on the TechNet script gallery
- Invoke-Sqlcmd2 on the TechNet script gallery
- Tools on PowerShellDBA.com – http://powershelldba.com
- Mike Fal blog on SQL Server Central - http://www.sqlservercentral.com/Authors/Articles/Mike__Fal/1408632
- Don Jones - http://powershell.com/cs/blogs/donjones/default.aspx
- PowerShell Team blog - http://blogs.msdn.com/b/powershell/
- Bruce Payette - http://powershell.org/wp/tag/bruce-payette/
Books
If you're interested in books, then take a look at:
·
https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2014-powershell-v5-cookbook
Reference Sites
One thing you can never get enough of is references. This section will give you reference material to refer to for help in anything you are doing. Remember that one of the best references is Get-Help and Get-Command. These cmdlets will help you get the most out of the modules and scripts you have installed on your machine. Also in the list below are the script repositories that you will be able to use regularly and some codeplex projects that will be useful in your future with PowerShell.Help Sites – Forums
Below are links (and this is not an exhaustive list) to forums and sites that let you register and ask questions of the experts and get some real help. All forums are not created equal, but these are some that you can rely on and I am sure there are many more that I have not visited.
·
TechNet Scripting Guys forum –
https://social.technet.microsoft.com/Forums/windowsserver/en-US/home?forum=ITCG
·
List of Forums for PowerShell
– http://learn-powershell.net/2013/04/27/what-forums-can-i-go-to-for-powershell-help/
Another
list – http://learn-powershell.net/powershell-forum-directory/
PowerShell is a very powerful tool to
integrate with SQL Server.
No comments:
Post a Comment
if you have any doubts, please tell me