Copying a project. How?

We recently migrated to SQL2005 and have moved all OLAP databases to the new server. I need to create new OLAP databases. In SQL2000, just a copy would do it, but I am having problem in 2005. I tried to script the database as 'create' or even created an empty one and try to change it by 'alter' from a valid DB, but no luck (XMLA file was modified to have the new names).
Any pointers, suggestion would be appreciated. I know how to change the DataSource and DataView later, just need to know how to copy one!!!!
Also please let me know what tool is used (Management Studio, Visual Studio, etc...)
Thanks in advance!

There is a special project in BIDS where you can reverse engineer a SSAS 2005 database.

If you have a BIDS project with a cube you can choose to save the whole project under a new name.

HTH

Thomas Ivarsson

|||I used BIDS, made a new 'Import Analysis Services 9.0 Database' from the template and imported a working database with everything. <-- Great.
Then made a new DataSource pointing to the right source and changed the DSV in XML to point to it <-- Great
Problem now is that, it is throwing and error that The 'Role' with 'ID' ='suchandsuch' doesn't exist in the collection when I go to build it. I saw this Role in the DataSource when viewed in XML and obviously can change it, but will not compile even when changed to a more previleged role. Same error!
The other OLAP database runs fine.
Any comments, suggestion will be appreciated as always, as I hope this info will help the next person.
noorbakhsh|||Got it. The above will fix the problem except we had a removed user which I had to remove from users in roles manually. Also the database I copied was corrupted so when I re-did it it worked fine.
I hope this helps others!
noorbakhsh|||One more follow-up since the other stuff had errors and there was an easier way:

I used BIDS, made a new

'Import Analysis Services 9.0 Database' from the template with the new databse name and imported

a working database with everything. <-- Great.

Then I just right-clicked on the DataSource/View Designer and 'edit' the connection string pointing to the new DataBase.

Right clicked on the database/properties and changed the deployment parameters

Deploy - Done

I hope this helps!!! - It should :)

noorbakhsh

copying a DB from one server to another

Hi

I have a PC with MSDE2000 and need to copy the data to another PC running SQL2005.

I have registered the MSDE server on my SQL2005 PC and can see the tables etc.

Can this be done automatically, say every day?

Is there a way of mirroring the DB?

Cheers

Eugene

Hello,

http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx. You can find more info from this about copying the database from MSDE to SQL2005.

Thanks

|||

If your SQL Server 2005 machine is running Standard Edition or higher, you can create a package in SQL Server Integration Services to automate moving data. You can schedule running the package using SQL Server Agent.

Database replication (publisher/subscriber) requires higher SKUs (Workgroup and up) for the publisher. SQL Server 2005 Express can be a subscriber though. You can see a comparison of features here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Hope this helps,
Steve

|||

Hi Steve

I Am using 2005 Std, I have also just installed SP1

I was hoping to use the mirroring function but it looks like it only works with sql 2005 DB! The same is true of Log shipping I think?

So is there any samples of how you use this integration service?

I am not that familiar with SQL as a whole and it would be good if there we're simple ways around the problems.

I would of thought there is an easy way to automatically copy a DB to another Server , like wizards....

Any Help Would be Appreciated

Cheers

Eugene