Sql-server – Error “the system cannot find the file specified” when opening a large SQL Server file

collationscriptingsql serverssms

I have generated the script of my whole SQL Server database into a file and it's more than 36 GB. When I try to open it, I get the following error:

The system cannot find the file specified.

At first I intended to create the script in a new window, but every time my SQL instance got crashed. Also I have all the necessary permissions and I even tried opening it in the server, but I keep getting this error.

The server's RAM is 16 GB.

My initial goal is to change the whole database's collation (including every field and every table) and since it's a time-consuming process, I decided to generate the script of the database in order to create a new one with the right collation. I couldn't create a new window in the "Generate Script" process; therefore I had to create a new file in order to generate the script. The file is 36 GB.

When trying to open the file, I just get the mentioned error and SQL doesn't crash.

Best Answer

If no code page conversions are necessary (i.e. either not changing code pages or changing code pages but no characters being used are different between the code pages; and this only pertains VARCHAR data), then there is an undocumented option that might be much quicker, both in terms of time spent working out the solution, and time spent moving data. In fact, this undocumented approach is incredibly fast because it does not move any data at all. It merely updates the internal collation_id of each string column in the metadata (for system tables that should be updated and all user tables). The only real time spent is in the rebuilding of all affected indexes. But there is no data export and import. And it bypasses all of the restrictions that prevent updating a databases collation (in many, but not all, cases). Plus it changes the system DBs and the instance-level collation.

The major issue is that by changing the collation_id, any existing VARCHAR characters in the range of 128 - 255 might be misinterpreted in the new collation. However, this is a non-issue if the code page isn't changing. Also, there are two areas that are currently not updated by this approach, though I have requested that they be added, so please vote for this suggestion: Update Collation of Partition Functions and User-Defined Table Types via SQLSERVR -Q.

The method I speak of is running sqlservr.exe with the -q switch. You need to stop the SQL Server engine service, and run that command passing in the new collation and a couple of specific trace flags. I have a detailed description of this approach in the following post (please be sure to read all of the details about what this method does and does not do):

Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Related Question