Thanks in Advance,
Ram
You can always execute sp_helpfile in a database to find the files for that database.
Or select * from sys.database_files
-Sue
Thanks in Advance,
Ram
You can always execute sp_helpfile in a database to find the files for that database.
Or select * from sys.database_files
-Sue
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>
Executed as user: Domain\username. <return xmlns="urn
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?
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>
Executed as user: Domain\username. <return xmlns="urn
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
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>
Executed as user: Domain\username. <return xmlns="urn
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?
Yes,no,yes :-)|||can you do hotbackups with the standard edition? what about incremental
backups?
or do you have to purchase the enterprise edition?
"Trev@.Work" <no.email@.please> wrote in message
news:400c0f09$0$4907$afc38c87@.news.easynet.co.uk.. .
> Ryan wrote:
> > Can SQL server handle High Availability or does it have to go down
> > occasionally for a cold backup? Does it support incremental backups?
> Yes,no,yes :-)|||"Ryan" <rgaffuri@.cox.net> wrote in message
news:a0VOb.4224$_H5.115@.lakeread06...
> can you do hotbackups with the standard edition? what about incremental
> backups?
> or do you have to purchase the enterprise edition?
> "Trev@.Work" <no.email@.please> wrote in message
> news:400c0f09$0$4907$afc38c87@.news.easynet.co.uk.. .
> > Ryan wrote:
> > > Can SQL server handle High Availability or does it have to go down
> > > occasionally for a cold backup? Does it support incremental backups?
> > Yes,no,yes :-)
All backups in MSSQL are 'hot' in the sense that you do not have to stop the
MSSQL service, and you do not have to disconnect users from the database.
This is true in all versions of MSSQL, and all backup types (full, log,
filegroup, differential) are also available in all versions. Other high
availability options (replication, clustering, log shipping) do depend on
the version in terms of what functionality is available.
Simon|||On Mon, 19 Jan 2004 13:06:02 -0500 in comp.databases.ms-sqlserver,
"Ryan" <rgaffuri@.cox.net> wrote:
>can you do hotbackups with the standard edition? what about incremental
>backups?
What part of "yes" don't you understand? :-)
You can do both.
--
A)bort, R)etry, I)nfluence with large hammer.
