1

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.

Improve this question
  • Could you divide your script into small pieces? – McNets Sep 1 '20 at 9:16
  • I have generated the script per object and there are more than 600 files. – iminiki Sep 1 '20 at 10:16
2

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?

Improve this answer
  • I will check the this method. Thanks you. – iminiki Sep 2 '20 at 4:40
1

I suggest you use some other tool to execute the script file. I'm thinking SQLCMD.EXE, for instance.

And if you need to edit the file, use a good editor that can handle large text files (i.e., work witch chunks of the file in memory instead of the whole file). I found this conversation on SO on such editors.

(I'm not surprised that SSMS is choking on the file considering a) it is a 32 bit application and b) the file is so huge. I considering it a dead-end trying to make SSMS execute this file. One can question the error message you get, try to produce a repro and have MS looking into the error message - but that doesn't really help you in the end.)

Improve this answer
0

scripting out to a file the entire 36GB database is not a good idea.

I think the right way to do it is to backup/restore the database.

To backup the database:

BACKUP DATABASE [WideWorldImporters] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImporters-Full.bak' 
WITH STATS = 1
GO

To restore the database:

USE [master]
RESTORE DATABASE [WideWorldImporters] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  STATS = 1
GO

then download the newest version of SQL Server Management Studio (SSMS) here. You should be able to execute the backup/restore using SSMS without crashing.

You can learn more about the backup/restore here.

Improve this answer
  • I want to change my whole database's collation (that means every field and every table); therefore I have to generate the database's script without its current collation. – iminiki Aug 31 '20 at 5:48
  • 1
    why not just use the command ALTER DATABASE COLLATE OR ALTER TABLE ALTER COLUMN COLLATE? btw, the collation was not mentioned on your original question. – Dan Co Aug 31 '20 at 6:04
  • 1
    Because that's a whole other problem which looks like a nightmare. Everyone would suggest creating a new database instead of changing its collation. I didn't think it would be relevant, since my only intention is to open the script file. – iminiki Aug 31 '20 at 6:43
  • 3
    as an alternative, you can generate script per object, save it as script file then using sqlcmd (not SSMS) you can run the files (with your collation) in a loop. – Dan Co Aug 31 '20 at 7:00
  • I'll try it, thank you for the suggestion. – iminiki Aug 31 '20 at 7:42
0

I have generated the script of my whole SQL Server database into a file and it's more than 36 G

Don't include the data in the script. That option is intended for small tables. Instead script only the DDL, and use the Copy Database Wizard, or INSERT ... SELECT queries to copy the data.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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