# listing SQL database for back-up in powershell

• ### Question

• Hello,

I need to make a script that makes a back-up of the SQL database from the webserver.

For this, i first need to find that database....

Does someone know whats wrong with these commands?

Powershell just finds the system databases (master,model,msdb tempdb)....

$Server = "test" Function Show-DatabaseList ([string]$server)
{
$srv = New-Object('Microsoft.SqlServer.Management.Smo.Server')$server
$srv.databases | Select Name } Show-DatabaseList Friday, August 18, 2017 1:05 PM ### All replies • Hi Sam, you may want to use dbatools for that: Get-DbaDatabase -SqlInstance "foo\bar" It can also perform backups and automated restore-tests :) (cause having backups is only half of the deal - they also need to be able to restore). Cheers, Fred There's no place like 127.0.0.1 Friday, August 18, 2017 1:22 PM • Thanks for the answer Fred, but i would like to make a powershell script that we can easily run on other servers without having to install software. • Edited by Friday, August 18, 2017 1:48 PM Friday, August 18, 2017 1:41 PM • Well ... it's a free PowerShell module that can be installed by Install-Module, but ... suit yourself. You can look up how they implement it though, even if you don't want to go that far. Edit: Oh yeah: You may need to run connect first:$srv.ConnectionContext.Connect()

There's no place like 127.0.0.1

• Edited by Friday, August 18, 2017 1:51 PM
Friday, August 18, 2017 1:50 PM
• The following finds all "mounted" databases.

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server('.\sqlexpress')$srv.databases | Select Name

Note that the class does not need to be quoted and that the parens are on the arguments.

In WMF 4 and alter we can do this:

$srv = [Microsoft.SqlServer.Management.Smo.Server]::new('.\sqlexpress')$srv.databases | Select Name

\_(ツ)_/

Friday, August 18, 2017 2:26 PM
• Try this option. It's a bit bigger but will work without additional modules...

$SQLInstance = '.'$connectionString = "Data Source=$SQLInstance; Integrated Security=SSPI;"$sqlCommand = "SELECT [name] FROM sys.databases WHERE database_id > 4"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter$command
$dataset = New-Object System.Data.DataSet$adapter.Fill($dataSet) | Out-Null$connection.Close()
$Databases =$dataSet.Tables.Name

\$Databases

Best regards,
Pavel

Monday, August 21, 2017 5:33 PM
• Hi Sam,

just got an idea, that seems so obvious I don't know how I missed it:

Make sure the user running the script actually has the permissions to do what you want.
Seeing only the system default is often a symptom of lacking permissions.

Cheers,
Fred

There's no place like 127.0.0.1

Tuesday, August 22, 2017 7:33 AM