Clota Blog

Category: Backup and Recovery

  • Ghost Records

    When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…

  • Important SQL Server Scripts…

    Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1     @DBName AS [DBName]    ,[LogDate]    ,CASE       WHEN…

  • Database restore on destination and fix orphan user

    In the article, we are going to demo a backup and restore process from source DB server to destination DB Server. In the demo we are performing below high-level tasks… 1. Create a login and grant access to a DB on our source server.  2. Take a DB backup on the source server. 3. Restore…

  • Step by Step implementing Log Shipping

    Log Shipping enables you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.  ADVANTAGES OF LOG SHIPPING-  DISADVANTAGES OF LOG SHIPPING-  TYPES OF LOG SHIPPING –…

  • Point-in-time recovery in SQL Server

     Hey guys, In this blog we will discussed about point-in-time recovery in SQL Server and how to perform it and also we discussed on full, differential and transaction log backup. 1.       Full backup – A Full backup is a complete backup of a database. The full backup contains all the  data in a database and…

  • Step by Step Implementation of Instant File Initialization

     Hello guys, In this blog, I will discuss about step by step implementation of “Instant File Initialization” in SQL Server STEP- 1: You must first open the instance in SSMS, then do the following query: Fig-1 This is the result of executing a query: Fig-2 You must update Y to N in the Instant _File Initialization…

  • Detach and Attach in SQL Server

    Hello there, everyone! I hope everything is going well for you. Today, I’ll show you about Detach and Attach Detach SQL Server Database- A SQL Server database is made up of at least two files: a data file (mdf) and a log file (ldf), though there may be additional data files in some circumstances. Since…

  • DB Backup and Restore History

     –DB Backup HistorySELECT CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE msdb..backupset.type WHEN ‘D’ THEN ‘Database’ WHEN ‘L’ THEN ‘Log’ WHEN ‘I’ THEN ‘Diff’END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 30) and msdb.dbo.backupset.database_name like ‘DB_Name’  order by 3 desc —DB Restore History  SELECT    [rh].[destination_database_name],     [rh].[restore_date],     [b].[backup_start_date],     [b].[backup_finish_date],     [b].[type],     [b].[first_lsn],     [b].[last_lsn],     [b].[checkpoint_lsn],     [b].[database_backup_lsn] FROM [msdb].[dbo].[restorehistory] AS [rh] JOIN [msdb].[dbo].[backupset] AS [b] ON [rh].[backup_set_id] = [b].[backup_set_id] WHERE [rh].[destination_database_name] = ‘DB_Name’ ORDER BY [rh].[restore_date] DESC; ====================================================================================================================== —DB Backup  –Full backup of DB_Name BACKUP DATABASE [DB_Name] TO   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_1.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_2.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_3.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_4.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_5.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_6.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_7.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_8.bak’,   DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_9.bak’,   DISK…