1

I want to restore sql server db from sql server 2012 to sql server 2008 R2 using SQLCMD.

sqlcmd -S localhost -E -I -d abc -i d:\db\abc.sql >> ran fine till 33399 rows. The abc.sql is 3.4 GB in size.

 Msg 105, Level 15, State 1, Server SERVER1, Line 590
    Unclosed quotation mark after the character string '
    <h1 class="calibre18" id="calibre_pb_1"><span class="blah blah....

I have no idea how to get it to restore. The file is so huge, generated with sqlserver 2012 management studio with option script for server 2008 R2 , schema with data.

I cannot detach/attach or restore directly from abc.bak because of the version compatibility problem. I can't even edit the file manually.

Also, i tried generate the db relationship with the script (schema only), then use SSMS Export data => duplicate identity

Improve this question
6
  • 1
    Why not use the Export/Import feature of Management Studio and just go directly from the 2012 database to the 2008R2 database instead of going to a SQL script first? Commented May 7, 2013 at 14:23
  • but it will drop all the primary keys and relationship. Somehow i never succeed using it to transfer data. Commented May 7, 2013 at 14:29
  • 1
    Ok, so you re-create the relationships too before you export the data. You can have SSMS to generate them for you. Right click on the databse, go to Tasks -> Generate Scripts Just run that to create the tables before you export. Commented May 7, 2013 at 14:38
  • I did that too before writing this question. if you create the datbase relationship before using Export/Import, Export/import won't let you transfer data due to conflict primary indentity Commented May 7, 2013 at 14:42
  • 1
    You must check the box for Identity Insert for it to copy values of Identity columns. Also you will will likely need to figure out the order your self to do the tables. the DTS wizard will not know about the forgen key constraints, so make sure to copy the parent tables first then go back and copy the dependant tables. Commented May 7, 2013 at 14:43

1 Answer 1

Reset to default
1

Simple answer is, if you cannot modify the script, you cannot fix this issue.

Instead of using sqlcmd, its recommended to use "Import and Export Data" application to transfer data form one server to another.

Try this, go to Run, type DTSWizard.exe and press enter to see if Import Export App is installed in you machine.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Just be aware you will need to right click on your source database and use the Tasks -> Generate Scripts feature to get the sql scripts to generate all of the original databases schema so it can be created at the destination. If you let the DTSWizard create the tables the table constraints will not be re-created at the destination.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.