How to restore a database by using a script file?


  • Gurus!

    I have received a huge, 1 GB sql script file, and I need to create a database in my SQL server instance by using it. The guys who sent it to me generated it by using the Tasks -> Generate Script option on the original database, and while they generated it, they selected the 'script both schema and data' option. Because of all the data in the database, all the INSERT statements that were generated has caused the script file to balloon up to 1 GB. For some reasons, they are unable to give me the .bak file.

    Needless to say, I can't open up the 1 GB script file in a query window and then execute it. But I need to create a database on my PC using this script file. How do I do it without opening it Gurus? What commands do I run in order to pass the path of the script file as a parameter, and then create a database without opening the file? And by some chance, if there is some syntax error somewhere in the middle of the file, how do I handle it Gurus?

    Please let me know Gurus! Seeking your wisdom on this!

    Novice Kid

    Thursday, February 06, 2014 1:27 PM


All replies

  • Hello,

    You can give a try with the sqlcmd Utility and pass the filename as parameter.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 06, 2014 1:38 PM
  • I tried using it Guru Olaf. Following was the command I ran:

    sqlcmd -S <MyServer\MyInstance> -U <MyUsername> -P <MyPassword> -i <Path of script file>

    But horror of horrors, there is a syntax error at line 109241! :-(

    Is there some way apart from sqlcmd Guru? Or is it the only way?

    And how in the world do I get around that syntax error? My guess is that it is probably due to some single quote or some other special character in the data from the original database.

    Thanks for your input Guru Olaf, do let me know if there is some way out of this syntax issue too!

    Novice Kid

    • Edited by Novice Kid Thursday, February 06, 2014 2:02 PM
    Thursday, February 06, 2014 1:46 PM
  • You can try to split the one large SQL file into several smaller files using a text editor and execute them one by one in order of the split.

    Or ask for a database backup which you could restore on your machine; if they use the same or a lower version of SQL Server as you do.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 06, 2014 1:53 PM
  • Thanks once again Guru Olaf! Let me try splitting the file and then running the commands separately.

    Novice Kid

    Thursday, February 06, 2014 2:00 PM