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 14

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 28

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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 23

Author Comment

by:Thomas Grassi
ID: 41815219

????? read what file?

Can you give example please
LVL 28

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 14

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 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