I have been looking for the powershell commands for getting the backup path of a database in an sql server. I would be providing sever name and database name as input. Could some one help me with the solution so that I can achieve my requirement. Note: I just need the path of the database backup. I need not to do any back up of that database in a path.
Thanks in advance.
Sudhir
So.... couple of things.
A SQL Server instance (sounds like you're asking about SQL Server), has a default backup location, which can be overridden at the time of a backup. If you want to see an instance's default backup location, I'd use something like this:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$i = New-Object 'Microsoft.SqlServer.Management.Smo.Server' '(local)'
$i.Settings.BackupDirectory
I'm using SQL Server Management Objects (SMO) here. I've created an instance object ($i), and I've queried the BackupDirectory property in the Settings collection to get the desired path.
If you don't like SMO, you can also get this information from the registry; see this article for help there.
Adding an answer here (since I can't add comments yet), there is also the option to use the newer SQLServer module in powershell. Just run a query to get the data you need. This is for a local SQL Express instance, update the name as needed.
Import-Module SqlServer
$bkppath = Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('InstanceDefaultBackupPath')" -ServerInstance ".\SQLExpress"
$bkppath.Column1
As an added bonus, if you'd like to delete the oldest backups just run this line, updating the best number of days to keep (AddDays function) (using bits of Pinal code from https://blog.sqlauthority.com/2018/04/02/sql-server-powershell-script-delete-old-backup-files-in-sql-express/ ):
Get-ChildItem $bkppath.Column1 -Recurse | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } | Remove-Item -Recurse
