locked
listing SQL database for back-up in powershell RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 samVVV Friday, August 18, 2017 1:48 PM
    Friday, August 18, 2017 1:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 FWN Friday, August 18, 2017 1:51 PM
    Friday, August 18, 2017 1:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Please remember to mark the replies as answers if they help...

    Monday, August 21, 2017 5:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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