SQL Database Copy/move Issue

Posted on 2016-09-25
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 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41815216
Can you try this will .Net code, read the file and execute the data from the file in batches.
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

LVL 23

Author Comment

by:Thomas Grassi
ID: 41815219

????? read what file?

Can you give example please
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 250 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

628 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