Whenever I restore an AS Database, the DB files are created in a new folder by name DBName_[1-n] wherethe number is incremented by 1 after every restore. I am currently looking for a script to copy the files[or this ASDBName_[n]] dynamically to another server.
Is there a way to find the file path of the ASDatabase through DMVs/ AMO or any other manner?
Regards, Sasi.
The numbering is used by AS to handle transactions: Each write operation creates a new copy with a new number, while the old version can still be used for read access. If, finally, the write operation - be it a restore or a processing or a structural change - succeeds, AS switches all users to the new version, and can then delete the old version in the background. If anything goes wrong during the write operation, the new version can just be deleted by AS without affecting anybody using the previous version. This can happen on database level, and as well at sub-object level (if you e. g. process only a dimension, or add a measure to a measure group).
This also means that in order to be sure you copy the database, you have to detach it - which makes sure that it is in a consistent state, and not a half written rest stays around. Then you could copy it to a new server, and attach it there. And, as long as the database is detached, there should be only one version, so you could just take the one and only folder of name "DBName.<n>.db".
I do not think there is a documented possibility to find the exact name. At least, Microsoft does not document one at http://technet.microsoft.com/en-us/library/cc280670.aspx. They just state "Use any operating system mechanism or your standard method for moving files to move the database folder to the new location."