MS SQL Server script for automatic restore

MS SQL Server script for automatic restore

1. Ensure the backup process is completed
   (by checking the existence of semaphore file to be created at the end of a backup job)
   
2. Reset the restore status
   (i.e. remove the semaphore file which is watched by other dependent process)
   
3. Switch the database to single user mode
   alter database XYZ set single_user with rollback immediate
   (to drop all the connection which may lock the database and prevent the execution of restore process)
   
   Alternatively, we can simply drop the database: (update on 2011-10-14)
   drop database XYZ
   (update on 2012-12-26: Drop database cannot be done if a user logon to the DB.  I still need to set the database to single user mode in order to disconnect all the user and locking)
   
4. Restore the database.
   RESTORE DATABASE XYZ FROM DISK = N'\\Server-A\d$\RestoreData\XYZBACKUP'
       WITH REPLACE, RECOVERY
           MOVE 'XYZ_Data' TO 'Y:\XYZ\XYZ_Data.mdf',
           MOVE 'XYZ_Data_1' TO 'Y:\XYZ\XYZ_Data_1.ndf',
           MOVE 'XYZ_Log' TO 'Y:\XYZ\XYZ_Log.ldf',
           MOVE 'XYZ_Log_1' TO 'Y:\XYZ\XYZ_Log_1.ldf'
   
5. Recreate DB user
   (If the database login is not destroyed, there is no need to recreate the login.  Please skip to recreate database user.)
   use master
   exec sp_droplogin XYZADMIN
   exec sp_addlogin  XYZADMIN, 'AStrongPassword' , XYZ
   
   (Recreate the linkage between SQL Server login and database user)
   use XYZ
   exec sp_dropuser XYZADMIN
   exec sp_adduser XYZADMIN, XYZADMIN, SSE_ROLE
   
6. Set the database to multi-user mode and simple recovery mode for better performance (in case the DB is for ETL purpose)
   alter database XYZ set multi_user
   (If the database is for data warehouse house only, we may set it to simple recovery mode to improve the speed.)
   alter database XYZ SET recovery simple
   
7. Create a semaphore file to indicate the restore is completed.  Therefore, any dependent job will know that the restore is completed and will go ahead.