Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1

    Unanswered: Restoring backup of SQL2000 database to SQL2012

    I have a client who has an application running on SQL2000 on a Server2003 system.
    They sent me a backup of the database as a .sql file.
    I copied the file onto a new system with Server2012 and SQL2012 installed as 180 day evaluations using all the default values.
    When I try to run the .sql file, I get the following message:
    "System.OutofMemoryException"
    The old system had 4gb of memory, the new one has 32gb.
    The .sql file is 3.5gb.
    Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So the file with the SQL extension is a 3.5 Gb stream of SQL statements (probably a mix of CREATE and INSERT statements)? If that is the case, then I will bet that there are no "batches" provided. The GO statement isn't actually executable, but it is used to segregate parts of the SQL file into batches to be submitted to the server. No SQL Server batch can be larger than 65,536 TDS network packets which usually means 256 Mb.

    You probably just need to use AWK or something like it to parse the file at SQL statement boundaries somewhere near the 128 Mb intervals and insert a GO statement.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I searched microsoft's website, and found that with 64 bit versions of sql, the min and max memory has to be specified. I am assuming, maybe, that the default is less than 2 or 3 gb.
    But, I haven't found yet, where to set that value. I did find a program that can display the amount of memory used, which seems to be something of an oxymoron.
    Pat, after reading your response, should I assume that if this is their standard backup procedure, that it doesn't work?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Producing a file of SQL statements is common with some SQL engines, and some third party tools, but it is NOT recommended, produced, or supported by Microsoft. That's why I started the previous post with a question to be sure that is what you have.

    Normally SQL Server produces a special flavor of export of the data and log files, typically with a BAK file extension. This BAK file contains everything needed to restore the database to a "known good state" as of the moment that the backup completed. This is produced by the SQL Server BACKUP statement and re-loaded by the SQL Server RESTORE statement.

    The problem is that RESTORE command in SQL Server will support one or more earlier versions of BACKUPs, but it won't support all of them. The gap from SQL 2000 to SQL 2005 to SQL 2008 to SQL 2008 R2 to SQL 2012 is too wide, so you can't directly restore a SQL 2000 backup onto a SQL 2012 server. This is probably what caused your client to use some third party tool to produce the SQL file instead of simply doing a BACKUP.

    Most clients would provide you with a BAK formmated file from SQL 2000. You can restore that directly to either SQL 2005 or SQL 2008. You would then produce a new backup from the intermediate server, which you could restore directly into your SQL 2012 instance.

    The edit is trivial to add the "GO" statements before an INSERT every 100 Mb or so. Once you solve that problem, you may run into schema dependency issues, where the script attempts to load a child table before it loads a parent table with foreign key relationships. The other possible problem lies in not knowing exactly how the SQL file is formatted, but I'm sure that you can work that out in short order.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have seen that sort of problem with a system here. The out of memory exception is actually at the Management Studio side. I am not sure what the largest script the management studio editor can handle. If you can break up the script, you should be able to run it, albeit in multiple parts. Have you tried running the script via SQLCMD?

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Making progress. When I run 'sqlcmd -i backup.sql' I get a syntax error:
    At line 21602 near command 'Go'
    Now I have to figure out how to edit a 3.5gb file.

  7. #7
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Well the file contains binary data, so editing it is out.
    The MSSQL version is 2014, not 2012 by the way.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I'd say pass it back to the client. See if they can get the DB to at least 2005 or better SQL 2008, then send you a proper backup. Alternatively, do you have access to a SQL 2005 or SQL 2008 server?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by kitaman View Post
    Now I have to figure out how to edit a 3.5gb file.
    Finally an easy question!

    UltraEdit from IDM Computing.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Sent a letter back to the client as to how the file was created. In the meantime, the file begins with "TAPE...........................M.i.c.r.o.s.o. f.t. .S.Q.L. .S.e.r.v.e.r......SPAD" ; is this a label record or signature for a file created using backup?

  11. #11
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    mumble,mumble. Who would name a file created with the SQL2000 backup utility backup.sql.
    I have now restored the entire database to my own old SQL2000 system.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't mumble... I'd probably be shouting from the rooftop!

    Glad to hear that you're making progress. Now you can use SSIS to transfer the data in a single shot, or backup/restore to either SQL 2005 or SQL 2008, then backup/restore on to SQL 2012.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I managed to install SQL2008 Express and the Server Management Studio.
    Restored the SQL2000 backup successfully, and then created a SQL2008 backup which I transferred to the new machine.
    When I tried to restore the backup to a new database, I get the following message:
    Code:
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    
    Restore of database 'ace2005' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
    
    ------------------------------
    ADDITIONAL INFORMATION:
    
    System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)
    
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476
    
    ------------------------------
    BUTTONS:
    
    OK
    ------------------------------
    The link goes nowhere.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use the WITH REPLACE option if you are using Transact-SQL. If you are using the GUI, the REPLACE option is on the second tab of the Restore dialog.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Thanks. The "with replace" option has moved again. It's now on the "Options" tab. I also took off the backup log option.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •