Sql-server – Create a file with variable name in power shell Power shell

powershellsql server

I am trying to execute the below power shell script to script out the db in sql server.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 


$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "SRVBLRDBATST98\MSSQLSERVER1"  


$abc=$s.databases


foreach ($tables in $abc)
{
$tables.Script() | out-File e:\pruthvi\db.txt -Append
}

The above power shell will script the out put to db.txt, but i need the output like dbname.txt for each database which will be created everytime for each databases.
any help?

Best Answer

This code should do what you want. To make it more readable and understandable, I expanded out much of the logic. I also added comments to describe what each piece of code is doing.

## Import the SMO assembly from the GAC
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

## Create a Server object for connection to the SQL instance
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "SRVBLRDBATST98\MSSQLSERVER1"

## Loop over the databases on the instance
$server.Databases | ForEach-Object { 
    ## Store the name of the database
    $databaseName = $_.Name

    ## Generate a script for the database
    $databaseScript = $_.Script()

    ## Save the script to a file, overwriting what may have already been there
    $databaseScript | Out-File -FilePath "E:\pruthvi\$($databaseName).sql" -Force
}

The key to this change are the parameters passed to Out-File. First off -FilePath "E:\pruthvi\$($databaseName).sql" is what saves the script to a file named after the database. Secondly, -Force ensures that if the target file already exists, it will be replaced.