SQL DB Backup and Restoration Process

1. SQL DB back up and restoration process

SQL DB Backup steps

Step 1: Open SQL server Management studio

         Select the Database  Tasks --> Backup

Step 2: select the backup destination add and browse the destination location

Step 3: Enter the database name with the extension .bak

Step 4: Select options and select compress the backup and click OK to take backup

After taking back up,go to the LIVE sever and check for DAILY BACKUP(from maintance tools-rc-vies history)Check the location by dc on daily back up..browse the location and check…

From live server take test server(\\ip).give(\c$).then create one folder

SQL DB restoration steps

Note: if you restore the live database in to the test server, you have to create a new database with the name of “AXCONFIG” and select transaction database and select new query and execute the below query to take the existing application parameters backup to AXCONFIG database

Step1 - Backup AX DB on test or dev server

Step2 - Create database with name AXCONFIG on test or dev server

Step 3 - Run below script on AX DB on test or dev server (Before restoring db)

Select * into AXCONFIG.dbo.AIFCHANNEL                        from dbo.AIFCHANNEL

Select * into AXCONFIG.dbo.AIFWEBSITES                        from dbo.AIFWEBSITES

Select * into AXCONFIG.dbo.BATCH                                from dbo.BATCH

Select * into AXCONFIG.dbo.BATCHGROUP                        from dbo.BATCHGROUP

Select * into AXCONFIG.dbo.BATCHJOB                                from dbo.BATCHJOB

Select * into AXCONFIG.dbo.BATCHSERVERCONFIG                from dbo.BATCHSERVERCONFIG

Select * into AXCONFIG.dbo.BATCHSERVERGROUP                from dbo.BATCHSERVERGROUP

Select * into AXCONFIG.dbo.BICONFIGURATION                        from dbo.BICONFIGURATION

Select * into AXCONFIG.dbo.BIAnalysisServer                        from dbo.BIAnalysisServer

Select * into AXCONFIG.dbo.EPGLOBALPARAMETERS          from dbo.EPGLOBALPARAMETERS

Select * into AXCONFIG.dbo.EPWEBSITEPARAMETERS        from dbo.EPWEBSITEPARAMETERS

Select * into AXCONFIG.dbo.PROJSERVERSETTINGS                from dbo.PROJSERVERSETTINGS

Select * into AXCONFIG.dbo.SRSSERVERS                        from dbo.SRSSERVERS

Select * into AXCONFIG.dbo.SYSBCPROXYUSERACCOUNT       from dbo.SYSBCPROXYUSERACCOUNT

Select * into AXCONFIG.dbo.SYSCLUSTERCONFIG                from dbo.SYSCLUSTERCONFIG

Select * into AXCONFIG.dbo.SYSEMAILPARAMETERS          from dbo.SYSEMAILPARAMETERS

Select * into AXCONFIG.dbo.SYSEMAILSMTPPASSWORD    from dbo.SYSEMAILSMTPPASSWORD

Select * into AXCONFIG.dbo.SYSFILESTOREPARAMETERS    from dbo.SYSFILESTOREPARAMETERS

Select * into AXCONFIG.dbo.SYSGLOBALCONFIGURATION        from dbo.SYSGLOBALCONFIGURATION

Select * into AXCONFIG.dbo.SYSSERVERCONFIG                        from dbo.SYSSERVERCONFIG

Select * into AXCONFIG.dbo.SYSWORKFLOWPARAMETERS      from dbo.SYSWORKFLOWPARAMETERS

Database Restoration Process

Step 1: Stop the AOS and Management Reporter services(2 SERVICES) before restoring    

Step 2: Open SQL server Management studio

Select the Database  Tasks Restore Database on the top of TestDB

 

Step 3: Select device and browse the location to add the database backup file

(Add the name with live)

Step 4: Select the destination database to test to overwrite the existing DB(select the current database name)

Step 5: Select files and rename the .mdf and .ldf files with the original destination DB name

 

Step 6: Go to options and select overwrite the existing database check box and click OK to restore

(AFTER RESTORING DB)- Run below script on AX transaction DB on Test or Dev DB to truncate the application parameters from restored DB (IN TEST SERVER)

Truncate table  dbo.AIFCHANNEL 

Truncate Table dbo.AIFWEBSITES

Truncate Table dbo.BATCH

Truncate Table dbo.BATCHGROUP

Truncate Table dbo.BATCHJOB

Truncate Table dbo.BATCHSERVERCONFIG

Truncate Table dbo.BATCHSERVERGROUP

Truncate Table dbo.BICONFIGURATION

Truncate Table dbo.BIAnalysisServer

Truncate Table dbo.EPGLOBALPARAMETERS

Truncate Table dbo.EPWEBSITEPARAMETERS

Truncate Table dbo.PROJSERVERSETTINGS

Truncate Table dbo.SRSSERVERS

Truncate Table dbo.SYSBCPROXYUSERACCOUNT

Truncate Table dbo.SYSCLUSTERCONFIG

Truncate Table dbo.SYSEMAILPARAMETERS

Truncate Table dbo.SYSEMAILSMTPPASSWORD

Truncate Table dbo.SYSFILESTOREPARAMETERS

Truncate Table dbo.SYSGLOBALCONFIGURATION

Truncate Table dbo.SYSSERVERCONFIG

Truncate Table dbo.SYSWORKFLOWPARAMETERS

Truncate Table dbo.SYSSERVERSESSIONS

Truncate Table dbo.SYSCLIENTSESSIONS

Step 5 - Run below Query on AX transaction DB on Test or Dev DB to restore the old application parameters from AXCONFIG Database(IN TEST SERVER)

Insert into dbo.AIFCHANNEL                          select * from AXCONFIG.dbo.AIFCHANNEL                         

Insert into dbo.AIFWEBSITES                         select * from AXCONFIG.dbo.AIFWEBSITES                         

Insert into dbo.BATCH                                 select * from AXCONFIG.dbo.BATCH                                         Insert into dbo.BATCHGROUP                         select * from AXCONFIG.dbo.BATCHGROUP                                

Insert into dbo.BATCHJOB                         select * from AXCONFIG.dbo.BATCHJOB                                

Insert into dbo.BATCHSERVERCONFIG                 select * from

AXCONFIG.dbo.BATCHSERVERCONFIG                

Insert into dbo.BATCHSERVERGROUP                 select * from

AXCONFIG.dbo.BATCHSERVERGROUP                

Insert into dbo.BICONFIGURATION                 select * from AXCONFIG.dbo.BICONFIGURATION

Insert into dbo.BIAnalysisServer                               select * from AXCONFIG.dbo.BIAnalysisServer

Insert into dbo.EPGLOBALPARAMETERS                 select * from

AXCONFIG.dbo.EPGLOBALPARAMETERS                

Insert into dbo.EPWEBSITEPARAMETERS         select * from AXCONFIG.dbo.EPWEBSITEPARAMETERS        

Insert into dbo.PROJSERVERSETTINGS                 select * from

AXCONFIG.dbo.PROJSERVERSETTINGS                

Insert into dbo.SRSSERVERS                         select * from AXCONFIG.dbo.SRSSERVERS                                

Insert into dbo.SYSBCPROXYUSERACCOUNT         select * from

AXCONFIG.dbo.SYSBCPROXYUSERACCOUNT        

Insert into dbo.SYSCLUSTERCONFIG                 select * from AXCONFIG.dbo.SYSCLUSTERCONFIG                

Insert into dbo.SYSEMAILPARAMETERS                 select * from

AXCONFIG.dbo.SYSEMAILPARAMETERS                

Insert into dbo.SYSEMAILSMTPPASSWORD         select * from

AXCONFIG.dbo.SYSEMAILSMTPPASSWORD        

Insert into dbo.SYSFILESTOREPARAMETERS         select * from

AXCONFIG.dbo.SYSFILESTOREPARAMETERS        

Insert into dbo.SYSGLOBALCONFIGURATION         select * from

AXCONFIG.dbo.SYSGLOBALCONFIGURATION        

Insert into dbo.SYSSERVERCONFIG                 select * from AXCONFIG.dbo.SYSSERVERCONFIG                

Insert into dbo.SYSWORKFLOWPARAMETERS        select * from

AXCONFIG.dbo.SYSWORKFLOWPARAMETERS

RESTART THE SERVICES—MICROSOFT AOS SERVICES,MANAGEMENT SERVICES(2 SERVICES)