Everything was like clockwork, until SSMS presented a new challenge concerning a local script file: ‘File is too large to open’.
From time to time, Antares is asked to migrate a database or databases to different servers for a variety of reasons. Recently, the A-Team was approached to migrate an instance of a database to a newer version of SQL Server to take advantage of several new features. Regardless, the plan was a straight forward approach to include:
Problem: Need to restore a database to a new SQL instance; however, when using SQL Server Management Studio we received an error indicating that the ‘File is too large to open.’
Fix: From a command prompt, run the following command
Be sure to update the placeholder values for ‘server’ and ‘yourscriptfile’ based on your needs.
After receiving the described error of the script being too large, one could wonder on why this even happened in the first place. After all, the script was generated using a database task within SQL Server Management Studio. Alas, we can get around this issue by firing up our favorite command prompt and utilizing the SQLCMD utility. The utility allows us to execute T-SQL statements, stored procedures, and in this case our generated script. The difference in using the utility vs SQL Server Management Studio is that the IDE leverages the Microsoft .NET Framework SQLClient whereas SQLCMD uses an ODBC driver. The utility will allow me to run this apparently large file but also help mitigate against a dreaded ‘Cannot Execute Script. Insufficient memory to continue the execution of the program (mscorlib)’. There are other benefits as well but this got the A-Team of our immediate hurdle.