Create a file with variable name in power shell Power shell

Question

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?

Answer 1

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.