メインコンテンツまでスキップ

Execute large SQL File with PowerShell

· 約2分
James Yip
James Yip

Last week I got engaged to perform a simple task to migrate a SQL database from a server to another. This is suppose be a simple task but there are many constraints:

  • Not able to use the Copy Database wizard in SSMS as the server is configured to connect via an alias and it does not have the matching SPN.
  • SSMS is the only tool available to do the work, not even SQLCMD is available.
  • No admin rights on the workstation

After evaluating different options, I decided to use the most traditional approach: Generate SQL Script with data and execute against the new database.

Generating SQL scripts using SQL Server Management Studio (SSMS) is easy (with the latest version). You can do that with the Generate Database Script wizard.

The challenge comes after the script is generated. The script file is not possible to open/execute with SSMS as it is 5GB in size.

After some Google and digging here and there, I came up with the following approach:

  1. Use PowerShell to break the files into 10,000 lines a file
$i=0; Get-Content ./export.sql -ReadCount 50000 | %{$i++; $_ | Out-File "c:\sql\export_${i}.sql" -Encoding UTF8}
  1. Then install SqlServer PowerShell module. Note that we set the scope to CurrentUser as we have no admin rights on the workstations.
Install-Module SqlServer -Scope CurrentUser
Import-Modeul SqlServer
  1. Run the following PowerShell script to load each SQL file against the new DB with Invoke-Sqlcmd
Get-ChildItem "C:\sql" -Filter *.sql |
    Foreach-Object {
        Write-Host $_.FullName
        Invoke-Sqlcmd -ServerInstance <server> -Database <db> -TrustServerCertificate -InputFile $_.FullName -DisableVariables
    }

Turns out Invoke-Sqlcmd is a replacement for sqlcmd but it's 100% PowerShell based modules so we can execute the scripts against the new SQL database without need to use SSMS. But note that we put -DisableVariables as part of the parameters for Invoke-SqlCmd. The reason for this is, by default, Invoke-SqlCmd will treat ${variable} as variable replacement and the SQL file I got has this text pattern in one of the DB columns. Some SQL statements failed to execute some as column value include that text pattern.