Pages

Monday, December 1, 2014

Backup and Restore Multiple Databases and Cubes

SSAS Cubes
1. List all the cube names in a MS Analysis Server instance.
Open a MDX / DAX query window and run followoing DMVquery.
    SELECT * FROM $system.dbschema_catalogs
    ORDER BY [catalog_name] ASC

2. Backup multiple databases in a MS Analysis Server instance.
Sometimes, database name may not be same as database ID. In that case, DatabaseID needs to be found by going to properties of the database.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>Cube 2</DatabaseID>
       </Object>
       <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 1.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>
C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 2</DatabaseID>
       </Object>
       <File>Cube 2.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
</Batch>

Ref: http://blog.sqltechie.com/2010/01/how-to-backup-multiple-database-using.html

SQLServer Databases
1. Backup all the databases in a SQLServer  instance can be done using following script (you can exclude any database by defining it within the where clause of select statement).

DECLARE @name VARCHAR(50) -- Database name 
DECLARE @path VARCHAR(256) -- Backup files' path
DECLARE @fileName VARCHAR(256) -- Backup files' filename
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\DatabaseMigration-09-06-2014\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cursor1 CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','Report Administration','ReportServer','ReportServerTempDB' )

OPEN cursor1  
FETCH NEXT FROM cursor1 INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM cursor1 INTO @name  
END  
CLOSE cursor1  
DEALLOCATE cursor1


2. Restore database using following command
Back files need to be copied to MS SQL Server\MSSQL.1\MSSQL\data Folder.
RESTORE DATABASE [AnalyticsPortal]
   FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AnalyticsPortal.BAK'
   WITH MOVE 'AnalyticsPortal' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\data\AnalyticsPortal.mdf',
   MOVE 'AnalyticsPortal_log' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AnalyticsPortal_1.LDF'