Solved

SQL Database Copy/move Issue

Posted on 2016-09-25
9
54 Views
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
https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

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

ERROR HRESULTE FAIL HAS BEEN RETURNED FROM A CALL TO A COM COMPONENT

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


Thoughts.

Thank you

Tom
0
Comment
Question by:Thomas Grassi
  • 5
  • 2
  • 2
9 Comments
 
LVL 13

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.
0
 
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

Thoughts
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 41815219
Pawan

????? read what file?

Can you give example please
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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.
0
 
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
0
 
LVL 13

Accepted Solution

by:
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.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 41817068
Megan

Update

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
0
 
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
1

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now