Importing database from MS SQL Server 2003 to MS SQL Server 2008

Question

I have defined this query

[query public genCascadePath(aCSharpResourceModel : CSharpResourceModel) : Sequence(Sequence(CSharpResourceModel)) =
    aCSharpResourceModel.hasRelatedCSharpRMManager->collect(rmm | rmm.hasRelatedCSharpRModel->asSequence()->prepend(aCSharpResourceModel))
/]

What I actually do is:

1. get a sequence from the "aCSharpModel"

2. for each element in this sequence, I define it as a sequence, and prepend another element (the rmm.hasRelatedCSharpRModel inside the collect is irrelevant, it's just a 1 to 1 relationship)

So I need something like this:

{element} -> {{element , element1},{element, element2}}

But when I try to access each of the inner sequences in the result from a template, ex:

[for (path :Sequence(CSharpResourceModel) | aCSharpResourceModelManager.hasRelatedCSharpRModel.genCascadePath())  separator(' -> \n')]
[/for]

I get an error :

org.eclipse.acceleo.engine.AcceleoEvaluationException: Invalid type for iteration at line 18 in Module TODODbResourceRepositoryCSharpFile for block for (genCascadePath(aCSharpResourceModelManager.hasRelatedCSharpRModel)) separator(' -> '). org.eclipse.emf.ecore.impl.DynamicEObjectImpl was not an instanceof Sequence(CSharpResourceModel).

If I try to do the same for loop, but with the iterator as type CSharpResourceModel instead of Sequence(CSharpResourceModel), it doesn't produce an error, but it treats my query's result as a single sequence, like a flattened one.

The thing is that I need to have the behavior I described at the start.

How can I achieve that?

Thank you.

Solution

First of all there is no such thing as SQL Server 2003.

You'll want to take a full backup of the source database and restore it to the new SQL Server, probably using the WITH MOVE syntax to put the files into the correct drive letter location.

Don't use the copy data or copy database wizards. They are useless.

RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE,
MOVE '001_Data' TO 'D:\Path\To\File\001.mdf',
MOVE '001_Log' TO 'E:\Path\To\File\001_log.ldf'

You'll need to figure out what the logical file names are from dbo.sysfiles on the old server and replace 001_Data and 001_Log with the correct logical file names.

Comments

[mailq]

Sorry this is out of the scope of this site. Backup your data on the one server and restore it on the other. This is how Microsoft wants it and documented it in the Online and Offline documentation.

[Max]

I did try that (see "Restore" paragraph). The exact error message I'm getting when doing so is The backup set holds a backup of a database other than the existing '{DBNAME}' database although the name of both databases match.

[Max]

Upon restoring the backup I was getting the The backup set holds a backup of a database other than the existing '{DBNAME}' database error. I tried to use the Overwrite the existing database (WITH REPLACE) option which made no difference. When I try to restore the backup with a query (RESTORE DATABASE 001 FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE, 001 being the actual db name) I get a syntax error on the database name. Could you explain in more details how you would perform the restore with the WITH MOVE please?

[user2864202]

You probably need to wrap the database in square brackets in order to get past the error. I expect you will also need to add the "with move" syntax, but one thing at a time right? RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE

[Ryan Ferretti]

I agree that you'll probably need the command to be "RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE" as SQL probably isn't going to like the database name starting with a number.

[Ryan Ferretti]

I've added syntax to my answer with how to use the WITH MOVE syntax.

[odyd]

Oh god, thank you very much. They should make it clear that collect flattens the result in the documentation though... Nevertheless, you solved my problem

[Alberto Martinez]

That's not true, you can restore a full backup from an old version on a database created in version 2008 (I have done it with databases as old as SQL Server 2000). Restoring a full backup overwrites the database completely, no data or configuration is mixed (the only setting that can be preserved is the location of database files).

[DeveloperChris]

From what I could see it depended on how the database was created. reading other advice I first created the database in 2008. I then tried to restore over it with the replace command from a DB backed up from an old SQL 2000 instance. It failed. I then deleted the database and used the process above. it imported fine. Once I had done that I could restore with replace from the old 2000 no worries. So I believe that if you create the DB in 2008 its not compatible.

[Alberto Martinez]

When you restore from a backup of another server/database on top on an existing database, even if the other server use the same version of SQL server, you have to select the correct location of the data/log files (usually it maintains the path and file names of the original server/database) and check the option of overwrite the current database, otherwise the restore operation will fail. If you restore from backup without creating first the database you don't have to do that.

[DeveloperChris]

That may be the missing step. I will try it out when I get a chance. Thank you