Notepad Won't Read a 929MB Text File

Well, I found out tonight that Notepad won't open a 929MB text file. SQL Server 2005 Management Studio won't, either, which leaves me in a pickle. Here's the story.

A while back, I installed a beta build of SQL Server 2005 on my desktop PC. It converted my SQL Server 2000 databases into SQL Server 2005 databases, but I need to move one of those databases to my laptop, which still has SQL Server 2000 on it.

Unfortunately, you can't back up a database in SQL Server 2005 and restore it in SQL Server 2000. So I decided to get clever. I wrote a console app that reads the contents of the SQL Server 2005 database and generates a SQL script to recreate the database--contents and all--in SQL Server 2000. Because the database has a couple of thousand images in it, the resultant SQL script was quite large--929MB, to be exact. I wanted to see what was in it to make sure my script generator worked, so I tried to open it in Notepad. No dice. Visual Studio 2005 won't open it, either. So I tried to open it in SQL Server 2005 Management Studio. Uh-uh. So now I have a wonderful little script generator and a script that (I think) will create the database I need, but I can't load it to run it. Drat.

If anyone knows of any tricks for moving SQL Server 2005 databases backward to SQL Server 2000, I'd love to hear about them. I haven't tried detaching the MDF in SQL Server 2005 and attaching in SQL Server 2000, but I have a feeling that won't work.

On Oct 25 2005 7:31 PMBy jprosise
With 15 Comments Jeff Prosise

Comments (15)

  1. Alex Black

    I'd try <A HREF="http://www.red-gate.com/">redgate">http://www.red-gate.com/">redgate's</A> SQL Compare and Data Compare tools.. they tend to be much better than enterprise manager at moving data. They are designed for diffing databases (schema and data respectively), but if you diff a blank database and a full database, you can use it to move the database in 2 steps. I expect it would work in your case. It is pretty slick. THey have a free trial. http://www.red-gate.com/

  3. Andrew Robinson

    Jeff, I haven't worked with SQL 2005 for more than a few minutes but we used to use DTS / Export to Access to move large databases in the past. Should be able to export it under 2005 to Access and then import it under 2000.

  4. Craig

    Option 1: Try opening it with emacs. Option 2: Regenerate as a series of files, and also generate a batch file that calls each one in turn.

  5. Jon Galloway

    On the practical side, I'd agree with Craig - regen as a series of smaller files and execute them in series. As far as viewing large files in Notepad, make sure you have Word Wrap turned off before viewing large files (http://blogs.msdn.com/shiv/archive/2005/09/12/464181.aspx). With a file of that size, though, I'd use a viewer that's built for virtual loading of large files. This one looks stupid, but works pretty well: http://www.swiftgear.com/ltfviewer/features.html I've also used simple VBS or console apps to extract sections from large files, since the streamreader objects are great at random access on large files.

  6. David Brabant

    Hi Jeff, UltraEdit could help you here: http://www.ultraedit.com/index.php?name=Content&pa=showpage&pid=10#file But I think using DTS is probably the "right" way of doing the transfer...

  7. Chris Hynes

    I did this a while ago while converting a MySQL database to SQL server (horrible experience BTW -- MySQL data type validation sucks). Anyway, the easiest thing I came up with was to write a tool that just read the sql statement by statement, populated a SqlCommand, and executed it. Using streams, you don't get into a lot of overhead. And you have intimate control over any tweaking you need to do of the data as it goes by (a lot for the MySQL-SQL Server conversion).

  8. Ken

    Have you tried running it in via SQLCMD or for SQL 2000, osql? Is it in a format that is parseable to the query engine? If it isn't there is always JASP. Ton's of freeware and shareware out there that can split up files.

  9. Jeff Prosise

    I like Chris's idea: Convert the tool that reads the database and writes it out to a SQL script into one that reads the database and inserts the records into another database. I'll try DTS Export first.

  10. Mike Swaim

    I'd go with splitting the file into a bunch of smaller files. That's what I've done in the past when confronted with a similar problem.

  11. Jeff Prosise

    Mission accomplished. As Chris suggested, I modified the script generator to query records from the source database and insert them into the destination database. Worked like a charm. Thanks goodness ADO.NET makes it easy to write database code.

  12. Mark Wan

    Have you tried adding a remote server (SQL2k) from your new 2k5 server and do an insert.. select? That would be the fastest. Underneath the seams, it is actually doing an oledb call to your 2k server. This is the fastest way I transport data but it obviously will not work if the 2 servers are not physically connected. :-)

  13. Alex White

    Glad you've got it all sorted. A decent tip for checking something in a text file is to use the old command line utilities. Type | more can at least let you check whether the start of it is as you expect, Find can be useful for checking that certain specific things are in there. Fully featured? No ;-) but useful in an emergency! Cheers

  14. Jeffrey Palermo

    I've had the general problem before of needing to peek inside an enormous text file. The text utilities just couldn't cut it with a huge file, so I wrote a little utility to help out. I call it EZFilePeeker, and you can download it from my downloads page: http://palermo.cc/MyToolDownloads/Default.aspx You can reference a file and get the top n lines from it (you can change the number of lines you want with a simple textbox).

  15. Nobody

    I would just say "Step away from the keyboard" for many reasons. 1. Why would you try and open a gig file with notepad in the first place?!?!?! 2. RTFM. Pretty apparent that SQL2005 will upgrade your data files.

Leave a Comment

Archives

Tags