Log Shipping - How you can convert the secondary database into primary

This topic explains the log shipping if the primary database is suspected or corrupted and how you can convert the secondary database into primary database.

To recover a database without restoring (to be done in the master data base)

Execute the RESTORE DATABASE statement, specifying:

• ·   The name of the database to be recovered

• ·   The RECOVERY clause

  /* To recover a database without restoring (to be done in the master database)  

   Execute the RESTORE DATABASE statement, specifying:

   The name of the database to be recovered

   The RECOVERY clause

Example

For example, you can recover the database, AdventureWorks2008R2, as in a restore operation without restoring data

*/

-- Restore database using WITH RECOVERY.

RESTORE DATABASE AdventureWorks2008R2

   WITH RECOVERY

Write T-SQL query with parameters

Example for using parameter in a query

DECLARE @CityParameter  varchar(50)

SET @CityParameter = 'Paris'

SELECT * FROM Person.Address

WHERE City = @CityParameter

Shrink Microsoft SQL Server database log file

Consider the following information when you plan to shrink a database:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

ALTER DATABASE TempDB SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (TempDB_Log, 5)
GO
ALTER DATABASE TempDB SET RECOVERY FULL
GO