Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?

Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?
sql

Can we automate and schedule Analysis Services Databases Back up and Restore Actions?

Hi, all here,

Would please anyone here give me any advice about wether or not we can automate and schedule the Analysis Services databases backup and restore actions?

Thanks a lot in advance for any guidance and help for that.

With best regards,

You just have to create a XMLA backup command, somethink like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDBId</DatabaseID>
</Object>
<File>c:\MyDB.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Then create a new SQL Server Agent job of type "SQL Server Analysis Services Command" and schedule it as needed.

See:

http://msdn2.microsoft.com/en-us/library/ms186658.aspx

and

http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Hope this helps,

Santi

|||

Hi, Santiago, thank you very much. Got it done.

|||Hi, this is great. But I want to schedule a SQL Agent job that loops through the list of all Analysis services databases and automatically applies the xmla to the current database.

e.g. to do this with normal dbs in TSQL you could loop through all the databases from master.dbo.sysdatabases and store the current db as a parameter.

How can pass parameters to XMLA? and if parameters aren't possible, how can you backup multiple dbs from a single script? e.g. the following doesnt work if you execute both at the same time.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>|||anyone?|||

You can wrap multiple commands inside a Batch. But remember that XMLA is a general purpose scripting language like TSQL. It does not have control flow, branching, etc.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB1</DatabaseID>
</Object>
<File>c:\MyDB1.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup><Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MyDB2</DatabaseID>
</Object>
<File>c:\MyDB2.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
</Batch>

|||Thanks T.K Anand for your reply. The syntax is definitely correct, but when I try running the above batch statement I get the error:

Executed as user: Domain\username. <return xmlns="urnchemas-microsoft-com:xml-analysis"><results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults"><root xmlns="urnchemas-microsoft-com:xml-analysis:empty"><Exception xmlns="urnchemas-microsoft-com:xml-analysis:exception" /><Messages xmlns="urnchemas-microsoft-com:xml-analysis:exception"><Error ErrorCode="3239968805" Description="Backup and restore errors: Neither Backup/Restore nor Synchronize command can be invoked in a user initiated transaction." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" /></Messages></root></results></return>. The step succeeded.

I tried scheduling this as a SQL Server Agent job, but the same error occurs and there is no backup file in the location that I specified?

Can these tables be combined?

I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.

This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.

This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.

Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.

Orders:
Contains the core order information pertaining to all transactions

CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
)

Transactions:
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.

CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
(
[OrderID],
[SequenceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
)I like recursive relationships. Just about every database I build has some element of recursion. But I wouldn't recommend it in this case.

Orders and order transactions are two different types of data. You want to combine them so you can avoid a join under some circumstances. But to check whether a given records represents a transaction you are going to need to use a join anyway, albeit a self-join ("A transaction would either have no parent, or would have to belong to a parent that does not have a parent"). You may save a bit on cacheing, but I doubt it.

Now, if a transaction could, under some business circumstances, represent a transaction, then you would have a good case for recursion. Or if an order could consist of a bundle of smaller orders. And I mean in your business model, not just that you COULD represent it this way in your schema.

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bing
bing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing
|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:

> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> changing
>
>
|||Look at Profiler to see if it does what you want. If you need more, use some of the auditing tools.
(My suggestion...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model related to
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have to
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing
|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bingbing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:
> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> > By critical actions, I mean some operations like dumping database,
> changing
> > data etc.
> >
> > Can SQL server logs catch those actions or do I need to turn on some
> > switches to be able to do so?
> >
> > Thanks in advance for any help.
> >
> > Bing
>
>|||Look at Profiler to see if it does what you want. If you need more, use some of the auditing tools.
(My suggestion...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...
> Thanks, Uri. But I don't see how that full or bulk-logged model related to
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have to
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
>> bing
>> I have to set recovery mode to FULL or Bulk-Logged.
>> For more info please refer to the BOL
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
>> > By critical actions, I mean some operations like dumping database,
>> changing
>> > data etc.
>> >
>> > Can SQL server logs catch those actions or do I need to turn on some
>> > switches to be able to do so?
>> >
>> > Thanks in advance for any help.
>> >
>> > Bing
>>|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:
>> bing
>> I have to set recovery mode to FULL or Bulk-Logged.
>> For more info please refer to the BOL
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
>> By critical actions, I mean some operations like dumping database,
>> changing data etc.
>> Can SQL server logs catch those actions or do I need to turn on some
>> switches to be able to do so?
>> Thanks in advance for any help.
>> Bing

can server logs catch who performs critical actions?

By critical actions, I mean some operations like dumping database, changing
data etc.
Can SQL server logs catch those actions or do I need to turn on some
switches to be able to do so?
Thanks in advance for any help.
Bingbing
I have to set recovery mode to FULL or Bulk-Logged.
For more info please refer to the BOL
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Thanks, Uri. But I don't see how that full or bulk-logged model related to
my question. The database is using FULL model, by the way. What I was
looking for was who accessed which databases when and performed what
operations. I don't see much of that in SQL server logs. Maybe I have to
use some 3rd party tools, e.g. Log Explorer?
Bing
"Uri Dimant" wrote:

> bing
> I have to set recovery mode to FULL or Bulk-Logged.
> For more info please refer to the BOL
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> changing
>
>|||Look at Profiler to see if it does what you want. If you need more, use some
of the auditing tools.
(My suggestion...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:3A0D1A1D-529B-427B-9B68-5B30CA56E0EF@.microsoft.com...[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model related t
o
> my question. The database is using FULL model, by the way. What I was
> looking for was who accessed which databases when and performed what
> operations. I don't see much of that in SQL server logs. Maybe I have t
o
> use some 3rd party tools, e.g. Log Explorer?
> Bing
> "Uri Dimant" wrote:
>|||Check out Log Explorer from Lumigent, there are probably other similar tools
but nothing that comes with SQL Server. You might be able to use Profiler to
determine some of what you want, but it will require some setup and will
only help if it's running when the events you're tracking happen.
Mike Kruchten
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A326C754-600D-4F8F-9EEB-24E7738648A7@.microsoft.com...
> By critical actions, I mean some operations like dumping database,
> changing
> data etc.
> Can SQL server logs catch those actions or do I need to turn on some
> switches to be able to do so?
> Thanks in advance for any help.
> Bing|||Depending on what you need to captuer, you could also create triggers that
are fired under certain circumstances - e.g. when data in a field are edited
or deleted.
Regards
Steen
bing wrote:[vbcol=seagreen]
> Thanks, Uri. But I don't see how that full or bulk-logged model
> related to my question. The database is using FULL model, by the
> way. What I was looking for was who accessed which databases when
> and performed what operations. I don't see much of that in SQL
> server logs. Maybe I have to use some 3rd party tools, e.g. Log
> Explorer?
> Bing
> "Uri Dimant" wrote:
>