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 this be done??

Hi,
Can anyone give me some advice on how I can accomplish the following.

I have a table that has a value like the following "2010302NOV01222004"

The above value is made of of 3 distinct values
They are:
Employee Code - 2010302
Course Code - Nov012
Quarter and Year: 22004

In another table I have a set of values that relate to the middle part of the above value (Course Code), i would like to return the course name that relates to the course code from the other table.

I have been able to extract the coursecode using the following code but can't see how to pass vthe value to the courseType table to return my CourseName value.

<code>
SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12)
</Code
I would need to do this from a stored procedure.

Regards..
Peter.

You can join your Leave table to the CourseType table in order to pull the CourseName for every Training field value using something like the following:

SELECT Leave.Training, CourseType.CourseCode, CourseType.CourseName
FROM Leave JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

I'm not sure what you're trying to accomplish with the stored proc, so I won't go into details on using parameters, etc...

|||Jason,
Thanks for the reply, what i am trying to accopmlish is:

1. I have a table that lists all the leave employees take, this includes any training. I need all records returned from the leave table and those that have entries in the training filed of the leave table require the courseName to be returned from the Course type Table.

The training is listed in the leave table as described previously and I need to extract the training CourseID from that field, as you saw "2010302NOV01222004" is in the training field in the leave table.

I need to extract "NOV012" from that filed and get the coursename (Novell iChain 2.2) returned from the Course Type table, if the field is null then ignore it.

Hope this expalins better what i am trying to accomplish.

Regards..
Peter.|||

Try executing the following to see if it doesn't give you exactly what you asked for:

SELECT Leave.*, CourseType.CourseCode, CourseType.CourseName
FROM Leave LEFT JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

|||Jason,
Thanks that has hit the nail on the head.. Exactly what i needed...

Regards..
Peter

Can not open user default database.

I deattached my default DB and crashed on reattaching it. I now get the above error. I am using MS SQL 200
Any suggestions or advice
Thank
ScottHi,
Can you please check whether the database is accessible.
1. Login as SA in query analyzer
2. execute the command
use dbname (replace database name with actual)
3. If you can open the database then no issues, otherwise send the error
you are getting after executing the command.
Thanks
Hari
MCDBA
"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
> I deattached my default DB and crashed on reattaching it. I now get the
above error. I am using MS SQL 2000
> Any suggestions or advice?
> Thanks
> Scott|||I can not login to query analyzer.
Unable to connect to server ...
Server: Msg 4064, Level 16 State 1
Cannot open user default database. Login failed.
This is similiar and related to the error I am getting in enterprise manager.
A connection could not be established to ...
Reason: Cannot open user default database. login failed..
...
I was coping over a DB file and when I started up the manage I had this error.
I forgot the log file. I restored to my original version of that DB file. I
then detached that db and tried to reattach the new version of the db (from a
different PC) which failed and logged me out of the server. I figured that was
because that DB was my default DB.
Thanks for your help.
Scott
"Hari" <hari_prasad_k@.hotmail.com> wrote:
>Hi,
>Can you please check whether the database is accessible.
>1. Login as SA in query analyzer
>2. execute the command
> use dbname (replace database name with actual)
>3. If you can open the database then no issues, otherwise send the error
>you are getting after executing the command.
>Thanks
>Hari
>MCDBA
>"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
>news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
>> I deattached my default DB and crashed on reattaching it. I now get the
>above error. I am using MS SQL 2000
>> Any suggestions or advice?
>> Thanks
>> Scott
>|||I do not recommend changing the default database for sysadmins of the very reason you see now. The
default database for the user you are logging in as doesn't exist (for some reason), which mean that
you cannot login! Actually ISQL.EXE and I think OSQL.EXE will let you in, so use any of these tools
to login and then use sp_defaultdb to change the default database. Or, login as another login which
work and let that login change the default database for you (that login need to be sysadmin).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Sheen" <sasheen@.oanet.com> wrote in message news:OCbFYSu6DHA.3008@.TK2MSFTNGP09.phx.gbl...
> I can not login to query analyzer.
> Unable to connect to server ...
> Server: Msg 4064, Level 16 State 1
> Cannot open user default database. Login failed.
> This is similiar and related to the error I am getting in enterprise manager.
> A connection could not be established to ...
> Reason: Cannot open user default database. login failed..
> ...
> I was coping over a DB file and when I started up the manage I had this error.
> I forgot the log file. I restored to my original version of that DB file. I
> then detached that db and tried to reattach the new version of the db (from a
> different PC) which failed and logged me out of the server. I figured that was
> because that DB was my default DB.
> Thanks for your help.
> Scott
> "Hari" <hari_prasad_k@.hotmail.com> wrote:
> >Hi,
> >
> >Can you please check whether the database is accessible.
> >
> >1. Login as SA in query analyzer
> >2. execute the command
> >
> > use dbname (replace database name with actual)
> >
> >3. If you can open the database then no issues, otherwise send the error
> >you are getting after executing the command.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
> >news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
> >> I deattached my default DB and crashed on reattaching it. I now get the
> >above error. I am using MS SQL 2000
> >>
> >> Any suggestions or advice?
> >> Thanks
> >> Scott
> >
> >
>|||Scott
Try give permissions to the def database by using
sp_grantdbaccess [@.loginame =] 'login'
[,[@.name_in_db =] 'name_in_db' [OUTPUT]]
OR
Drop the login and by using sp_addlogin create a new one with default
database access.
For more details please refer to BOL
"Scott Sheen" <sasheen@.oanet.com> wrote in message
news:OCbFYSu6DHA.3008@.TK2MSFTNGP09.phx.gbl...
> I can not login to query analyzer.
> Unable to connect to server ...
> Server: Msg 4064, Level 16 State 1
> Cannot open user default database. Login failed.
> This is similiar and related to the error I am getting in enterprise
manager.
> A connection could not be established to ...
> Reason: Cannot open user default database. login failed..
> ...
> I was coping over a DB file and when I started up the manage I had this
error.
> I forgot the log file. I restored to my original version of that DB file.
I
> then detached that db and tried to reattach the new version of the db
(from a
> different PC) which failed and logged me out of the server. I figured
that was
> because that DB was my default DB.
> Thanks for your help.
> Scott
> "Hari" <hari_prasad_k@.hotmail.com> wrote:
> >Hi,
> >
> >Can you please check whether the database is accessible.
> >
> >1. Login as SA in query analyzer
> >2. execute the command
> >
> > use dbname (replace database name with actual)
> >
> >3. If you can open the database then no issues, otherwise send the error
> >you are getting after executing the command.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
> >news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
> >> I deattached my default DB and crashed on reattaching it. I now get
the
> >above error. I am using MS SQL 2000
> >>
> >> Any suggestions or advice?
> >> Thanks
> >> Scott
> >
> >
>|||ok, none of these suggestions are working, as I can not log on to isql or osql
either.
I can not seem to recreate a new server and DB either as I get the same error.
Anymore suggestions?
Best Regards,
Scott S.
"Uri Dimant" <urid@.iscar.co.il> wrote:
>Scott
>Try give permissions to the def database by using
>sp_grantdbaccess [@.loginame =] 'login'
> [,[@.name_in_db =] 'name_in_db' [OUTPUT]]
>OR
> Drop the login and by using sp_addlogin create a new one with default
>database access.
>For more details please refer to BOL
>
>"Scott Sheen" <sasheen@.oanet.com> wrote in message
>news:OCbFYSu6DHA.3008@.TK2MSFTNGP09.phx.gbl...
>> I can not login to query analyzer.
>> Unable to connect to server ...
>> Server: Msg 4064, Level 16 State 1
>> Cannot open user default database. Login failed.
>> This is similiar and related to the error I am getting in enterprise
>manager.
>> A connection could not be established to ...
>> Reason: Cannot open user default database. login failed..
>> ...
>> I was coping over a DB file and when I started up the manage I had this
>error.
>> I forgot the log file. I restored to my original version of that DB file.
>I
>> then detached that db and tried to reattach the new version of the db
>(from a
>> different PC) which failed and logged me out of the server. I figured
>that was
>> because that DB was my default DB.
>> Thanks for your help.
>> Scott
>> "Hari" <hari_prasad_k@.hotmail.com> wrote:
>> >Hi,
>> >
>> >Can you please check whether the database is accessible.
>> >
>> >1. Login as SA in query analyzer
>> >2. execute the command
>> >
>> > use dbname (replace database name with actual)
>> >
>> >3. If you can open the database then no issues, otherwise send the error
>> >you are getting after executing the command.
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
>> >news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
>> >> I deattached my default DB and crashed on reattaching it. I now get
>the
>> >above error. I am using MS SQL 2000
>> >>
>> >> Any suggestions or advice?
>> >> Thanks
>> >> Scott
>> >
>> >
>|||Some partial good news.
isql -E
will get me into iSQL. sp_defaultdb ' ...', 'master' will change the default
DB.
However, in my confusion late night I removed all the dbs under the server to
start over.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>I do not recommend changing the default database for sysadmins of the very
>reason you see now. The
>default database for the user you are logging in as doesn't exist (for some
>reason), which mean that
>you cannot login! Actually ISQL.EXE and I think OSQL.EXE will let you in, so
>use any of these tools
>to login and then use sp_defaultdb to change the default database. Or, login
>as another login which
>work and let that login change the default database for you (that login need
>to be sysadmin).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Scott Sheen" <sasheen@.oanet.com> wrote in message
>news:OCbFYSu6DHA.3008@.TK2MSFTNGP09.phx.gbl...
>> I can not login to query analyzer.
>> Unable to connect to server ...
>> Server: Msg 4064, Level 16 State 1
>> Cannot open user default database. Login failed.
>> This is similiar and related to the error I am getting in enterprise manager.
>> A connection could not be established to ...
>> Reason: Cannot open user default database. login failed..
>> ...
>> I was coping over a DB file and when I started up the manage I had this
>>error.
>> I forgot the log file. I restored to my original version of that DB file. I
>> then detached that db and tried to reattach the new version of the db (from a
>> different PC) which failed and logged me out of the server. I figured that
>>was
>> because that DB was my default DB.
>> Thanks for your help.
>> Scott
>> "Hari" <hari_prasad_k@.hotmail.com> wrote:
>> >Hi,
>> >
>> >Can you please check whether the database is accessible.
>> >
>> >1. Login as SA in query analyzer
>> >2. execute the command
>> >
>> > use dbname (replace database name with actual)
>> >
>> >3. If you can open the database then no issues, otherwise send the error
>> >you are getting after executing the command.
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >"Scott Sheen" <anonymous@.discussions.microsoft.com> wrote in message
>> >news:DA63BEEE-BF15-411E-89D1-654B42263897@.microsoft.com...
>> >> I deattached my default DB and crashed on reattaching it. I now get the
>> >above error. I am using MS SQL 2000
>> >>
>> >> Any suggestions or advice?
>> >> Thanks
>> >> Scott
>> >
>> >
>|||I fixed the problem. I started isql via isql -E. That logged me on. I then
created a new DB, made that the default and all was good.
Scott
>I deattached my default DB and crashed on reattaching it. I now get the above
>error. I am using MS SQL 2000
>Any suggestions or advice?
>Thanks
>Scott