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

COPY_ONLY Restore Problems

I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
it to my development machine (only 225mb). I then tried to restore the
database (to a newly named database) from a device (disk), located the
backup file, checked contents in the Specify Backup window (it all seemed to
be there), and then added the file. When I get back to the Restore Database
window, there is NOTHING in the "Select the backup sets to restore:"
window!! So, I can't go any further, cannot go to the Options tab ("You must
select a restore source") and cannot restore the database.
Does anyone know what is going on?
Thanks for any help.1) Manually craft a RESTORE statement and try that from SSMS directly. See
if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see
what's up.
2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
makes a difference.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
>it to my development machine (only 225mb). I then tried to restore the
>database (to a newly named database) from a device (disk), located the
>backup file, checked contents in the Specify Backup window (it all seemed
>to be there), and then added the file. When I get back to the Restore
>Database window, there is NOTHING in the "Select the backup sets to
>restore:" window!! So, I can't go any further, cannot go to the Options tab
>("You must select a restore source") and cannot restore the database.
> Does anyone know what is going on?
> Thanks for any help.
>|||Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs)
and now I know that it cannot do COPY_ONLY restores (UNdocumented).
I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI
also worked for a non-COPY_ONLY backup).
Thanks for pointing me in the right direction (away from the GUI ;)
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
> 1) Manually craft a RESTORE statement and try that from SSMS directly.
> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY
> to see what's up.
> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
> makes a difference.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>moved it to my development machine (only 225mb). I then tried to restore
>>the database (to a newly named database) from a device (disk), located the
>>backup file, checked contents in the Specify Backup window (it all seemed
>>to be there), and then added the file. When I get back to the Restore
>>Database window, there is NOTHING in the "Select the backup sets to
>>restore:" window!! So, I can't go any further, cannot go to the Options
>>tab ("You must select a restore source") and cannot restore the database.
>> Does anyone know what is going on?
>> Thanks for any help.
>|||> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).
The RESTORE command doesn't differentiate between COPY_ONLY backups and regular backups. In fact,
there's nothing in the backup which indicates it is taken using COPY_ONLY. COPY_ONLY only affects
the database of which you do the backup (not resetting the BCM page if db backup or not emptying the
log if log backup). So my guess is that you didn't specify the correct options in the GUI (like MOVE
and/or REPLACE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Miller" <nospam@.nospam.com> wrote in message news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).
> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI also worked for a
> non-COPY_ONLY backup).
> Thanks for pointing me in the right direction (away from the GUI ;)
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly. See if it works. Also try
>> RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved it to my development
>>machine (only 225mb). I then tried to restore the database (to a newly named database) from a
>>device (disk), located the backup file, checked contents in the Specify Backup window (it all
>>seemed to be there), and then added the file. When I get back to the Restore Database window,
>>there is NOTHING in the "Select the backup sets to restore:" window!! So, I can't go any further,
>>cannot go to the Options tab ("You must select a restore source") and cannot restore the
>>database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>|||> So my guess is that you didn't specify the correct options in the GUI
> (like MOVE and/or REPLACE).
I didn't have a chance to specify options because every time I went to the
Option tab I got "Select the backup sets to restore:" because there was
nothing to select once I added the device(file).
Here is my backup SQL. Would the INIT have anything to do with this problem?
BACKUP DATABASE MyDatabase
TO DISK = N'E:\Backup\COPY_ONLY_MyDatabase_DAILY.BAK'
WITH INIT, COPY_ONLY
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.microsoft.com...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
> The RESTORE command doesn't differentiate between COPY_ONLY backups and
> regular backups. In fact, there's nothing in the backup which indicates it
> is taken using COPY_ONLY. COPY_ONLY only affects the database of which you
> do the backup (not resetting the BCM page if db backup or not emptying the
> log if log backup). So my guess is that you didn't specify the correct
> options in the GUI (like MOVE and/or REPLACE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
>> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the
>> GUI also worked for a non-COPY_ONLY backup).
>> Thanks for pointing me in the right direction (away from the GUI ;)
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly.
>> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or
>> FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
>> makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>moved it to my development machine (only 225mb). I then tried to restore
>>the database (to a newly named database) from a device (disk), located
>>the backup file, checked contents in the Specify Backup window (it all
>>seemed to be there), and then added the file. When I get back to the
>>Restore Database window, there is NOTHING in the "Select the backup sets
>>to restore:" window!! So, I can't go any further, cannot go to the
>>Options tab ("You must select a restore source") and cannot restore the
>>database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>>
>|||NOINIT is not the problem. Let me try it and see if I get the same behaviour in the GUI as you
describe:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
Right-click Databases folder, Restore Database, Type in "pubs" for database name, select "from
device", press "..."
Backup media: File
File name: C:\pubs.bak, OK
OK
... and indeed, there is nothing listed!
OK, lets do the same except I don't specify COPY_ONLY... And now the backup is listed! So, my
apologies. I was incorrect. I'm surprised that the backup somehow indicated it was done using
COPY_ONLY. Let me try something else:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT, COPY_ONLY
RESTORE HEADERONLY FROM DISK = N'C:\pubs.bak'
Yes, RESTORE HEADERONLY does indicate whether the backup was done using COPY_ONLY. I see a
difference in the "flags" column as well as the "IsCopyOnly" column. And the restore dialog only
show the first backup. Let me now try the other way around:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT
Now the restore dialog only show the second backup in the backup file (position 2). I get the same
result if I type in some other database name to restore into (a non-existing database).
So, the restore dialog does indeed refuse to list backups done using COPY_ONLY. So here's another
reason to type the RESTORE command instead of relying on how the GUI developer believe the restore
should be done... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Miller" <nospam@.nospam.com> wrote in message news:OgO3CSy9HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> So my guess is that you didn't specify the correct options in the GUI (like MOVE and/or REPLACE).
> I didn't have a chance to specify options because every time I went to the Option tab I got
> "Select the backup sets to restore:" because there was nothing to select once I added the
> device(file).
> Here is my backup SQL. Would the INIT have anything to do with this problem?
> BACKUP DATABASE MyDatabase
> TO DISK = N'E:\Backup\COPY_ONLY_MyDatabase_DAILY.BAK'
> WITH INIT, COPY_ONLY
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.microsoft.com...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
>> it cannot do COPY_ONLY restores (UNdocumented).
>> The RESTORE command doesn't differentiate between COPY_ONLY backups and regular backups. In fact,
>> there's nothing in the backup which indicates it is taken using COPY_ONLY. COPY_ONLY only affects
>> the database of which you do the backup (not resetting the BCM page if db backup or not emptying
>> the log if log backup). So my guess is that you didn't specify the correct options in the GUI
>> (like MOVE and/or REPLACE).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Don Miller" <nospam@.nospam.com> wrote in message news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
>> it cannot do COPY_ONLY restores (UNdocumented).
>> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI also worked for a
>> non-COPY_ONLY backup).
>> Thanks for pointing me in the right direction (away from the GUI ;)
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly. See if it works. Also
>> try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved it to my
>>development machine (only 225mb). I then tried to restore the database (to a newly named
>>database) from a device (disk), located the backup file, checked contents in the Specify Backup
>>window (it all seemed to be there), and then added the file. When I get back to the Restore
>>Database window, there is NOTHING in the "Select the backup sets to restore:" window!! So, I
>>can't go any further, cannot go to the Options tab ("You must select a restore source") and
>>cannot restore the database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>>
>>
>