Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Database Copy/move Issue

Posted on 2016-09-25
Medium Priority
Last Modified: 2016-09-27
SQL 2014 Enterprise on Windows 2012 Server
SQL 2008 R2 on Windows 2008 R2 Server

I moved a database from the 2008 server to the 2014 server months ago. The 2014 server is a VM on VMware ESXi Host

The problem is the application loses communications with the VM everyday for some reason

So I decided to move the database back to my 2008 Server which is a Physical Box.

I detached the database and copied the database files over but that failed because you cant got backwards versions of sql

So I found this article

So I followed those instruction very simple

When I went to the 2008 server I opened the SQL Server Management Studio and the did a file open and pointed to the db.sql file which is a script when I tried to open I get this error


I did this same procedure with another database and had no problem

This database size is 1.038.742 KB the other one was only 52,450 KB

I even tried the copy function from the 2014 that did not even  work at all it will not allow me to select the database to copy on the dialog window


Thank you

Question by:Thomas Grassi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
LVL 16

Expert Comment

by:Megan Brooks
ID: 41815141
I have never tried to do this myself, but 1 Gb sounds like an awfully large script to be loading into SSMS. What is the script file size?

I have noticed issues, at times with memory leaks in SSMS. One simple thing to try is to exit and restart SSMS before running the script again, if you haven't done that already.

If this is a script size issue and the script is beyond what SSMS can process, you might still be able to run it using the sqlcmd command line utility. There are examples of how to run a script using sqlcmd here.

The above links point to the SQL Server 2016 version of SQLCMD, which is available for download. The 2014 and 2012 versions are part of the SQL Server distribution. I don't believe 2008 R2 had this utility, but later versions of the utility should be able to connect to it.

Another utility that might be able to run the script is OSQL. It is deprecated, but still available. With this program you pipe the script file in, so it should be able to handle scripts of any size.

Again, I haven't actually tried any of this. I can, however, imagine SSMS choking on a large script file. When you open the file there you are loading it into a text editor. If the SSMS editor is not able to handle the file, a different query utility that simply runs the script without loading it into memory first for editing may work.
LVL 23

Author Comment

by:Thomas Grassi
ID: 41815212
Thanks for the response

I tried using sqlcmd -S server -i db.sql

It never created the db something to do with permissions I think

the size of the script db.sql is 1,063,670.948

Not sure how to get this db over

LVL 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41815216
Can you try this will .Net code, read the file and execute the data from the file in batches.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

LVL 23

Author Comment

by:Thomas Grassi
ID: 41815219

????? read what file?

Can you give example please
LVL 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41815223
The script file you have generated using Generate Script.

Which user are you using. The User should have Db_owner membership.
LVL 23

Author Comment

by:Thomas Grassi
ID: 41815230
I am logged on the server as the domain administrator  account

I opened a command prompt using run as administrator
LVL 16

Accepted Solution

Megan Brooks earned 1000 total points
ID: 41815393
SSMS and command line SQLCMD produced the same error?

If you can script the schema and the data separately, you can build the new database first without having to deal with a humongous script file and then when that's working it may be easier to troubleshoot any data transfers. Instead of a script you can use the Data Transfer wizard (you'll have to figure out the order of loading) or Red Gate SQL Data Compare if you happen to have it available.

I am going to be unavailable for a while but I will check later in the day Tuesday to see if this is still unsolved. I need to try this out for myself to see if I can reproduce it, and I am not able to do that right now.
LVL 23

Author Comment

by:Thomas Grassi
ID: 41817068


I create a separate script for schema and data

The Schema ran using the SQL Studio and created the database with no errors.

Then tried the data script it failed

So I ran it using sqlcmd and it is still running

Looking very promising

Will update when it completes
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 41818195
The data script finally finished had a few errors but the database is now up and running

The application that uses this database is working too.

Thanks everyone for your help

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question