DBA-Philiac

use master go -- take the user database in single_user mode first alter database [db_name] set single_user with rollback immediate ; --Check and Kill user sessions connected to that database --Restoring the database restore database [db_name] with recovery ;   It may fail if no .tuf is there for this database. restore   database  [db_name]  with   recovery, continue_with_error ;  Please do a DBCC CHECKDB after this and see any error is seen. Think twice before put it in Production DB.  --Reverting back to MULTI USER mode alter database [db_name] set multi_user with rollback immediate ; --making the db READ ONLY alter database [db_name] set read_only with no_wait ; T UF file stands for Transaction Undo File in SQL Server In log shipping scenario if we choose “Standby” option for the secondary server then the transaction log backup file would be restored in standby mode. As discussed earlier, this would need a TUF fil

Before taking the backup please check the database size CREATE TABLE #databases ( DATABASE_NAME VARCHAR ( 50 ), DATABASE_SIZE FLOAT , REMARKS VARCHAR ( 100 ) ) INSERT #Databases EXEC ( 'EXEC sp_databases' ); SELECT @@SERVERNAME AS SERVER_NAME ,        DATABASE_NAME ,        SYSMFM . source_file_name_main ,        SYSMFM . physical_name_main ,        SYSMFL . source_file_name_log ,        SYSMFL . physical_name_log ,        DATABASE_SIZE AS '(KB)' ,        ROUND ( DATABASE_SIZE / 1024 , 2 ) AS '(MB)' ,        ROUND (( DATABASE_SIZE / 1024 ) / 1024 , 2 ) AS '(GB)' ,        SYSDB . compatibility_level ,        SYSDB . create_date ,        SYSDB . database_id ,        SYSDB . collation_name ,        SYSDB . recovery_model ,        SYSDB . recovery_model_desc ,        SYSDB . user_access ,        SYSDB . user_access_desc ,        SYSDB . state ,        SYSDB . state_desc   FROM #da