Last day, I was restoring a database script file of size 450 MB (yeh, that's quite big) for some testing purpose. As usual, I tried restoring the database through SQL Server Management Studio. But, to my surprise, the restore was failing with error - "Cannot execute script: Insufficient memory to continue the execution of the program".
The result was the same even though I tried a couple of times through Management Studio. Now, to fix that I had to leave the GUI behind and try restoring from SqlCmd - a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. Here is how to do that:
- Launch Visual Studio Command Prompt (in admin mode)
- Run command
sqlcmd -S <ComputerName>\<InstanceName> -d <DatabaseName> -i <MyScript.sql>
Grab a coffee and wait till the script completes its execution.
Hope this helps.