Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

SQL Database Copy/move Issue

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

Thomas Grassi
Thomas Grassi
  • 5
  • 2
  • 2
3 Solutions
Megan BrooksSQL Server ConsultantCommented:
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.
Thomas GrassiSystems AdministratorAuthor Commented:
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

Pawan KumarDatabase ExpertCommented:
Can you try this will .Net code, read the file and execute the data from the file in batches.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Thomas GrassiSystems AdministratorAuthor Commented:

????? read what file?

Can you give example please
Pawan KumarDatabase ExpertCommented:
The script file you have generated using Generate Script.

Which user are you using. The User should have Db_owner membership.
Thomas GrassiSystems AdministratorAuthor Commented:
I am logged on the server as the domain administrator  account

I opened a command prompt using run as administrator
Megan BrooksSQL Server ConsultantCommented:
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.
Thomas GrassiSystems AdministratorAuthor Commented:


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
Thomas GrassiSystems AdministratorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now