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"  


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?

1 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

## 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.