Category: Backup and Recovery
-
Resource Database
Resource Database having below properties: 1. Read only DB 2. Contains system objects 3. Does not contain any user objects 4. Physical name of the DB 5. Each SQL Instance having only 1 Resource DB. 6. DB id of Resource DB always 32767 7. DB backup is not possible of Resource DB.
//
-
Database Backup and its types
Hey guys, In this blog I am going to explain you about Database backup and its types. Backup- A backup of the SQL Server data that can be utilized to repair a failure and recover the data. A database, or a few of its files or file groups, might be the level at which a backup…
//
-
Full Database Backup Restore
Working as DBA or developer, restoring a database is the request which we get regularly. Restoring a database is easy task if our back file is healthy. Restore a Full Database Backup Below is script which we can use for restore a database from full backup… ——————————————————————– RESTORE DATABASE DB_Name FROM DISK = ‘Location of DB backup file path’ ——————————————————————– But…
//
-
Differential Database Backup Restore
If we want to restore database with differential backup then first we need to restore associated fill backup with NORECOVERY option.Below is example… ——————————————————————– RESTORE DATABASE AdventureWorks FROM DISK = ‘C:\AdventureWorks.BAK’ WITH NORECOVERY ——————————————————————– Only after that we can apply Differential backup on the database… Below is the example…——————————————————————– RESTORE DATABASE AdventureWorks FROM DISK = ‘C:\AdventureWorks_Diff.BAK’ ——————————————————————–
//
-
Managing Database Transaction Log File
Hey guys, In this blog I am going to explain you about “How to manage Database Transaction Log File” This is the very common task which any DBA does regularly. Reason behind doing Transaction-Log file shrink… 1. Some time because of low disk space on T-log disk.2. Log-backup job issue. Sometime what happen because of log backup…
//
-
High VLF Count in Database Log File
High VLF count can create a performance problem in huge OLTP SQL Server system. Every 10 GB of T-Log file, VLF count should not more than 50 VLFs. For example, if Database transaction log file size is 50 GB then as per recommendation, lets calculate optimum value for this much size of log file… First…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL PostgreSQL SQL Server
Pages in Database
Boot Page Every DB has only one boot page. Boot page stores DB configuration information. It can find in page # 9 of the first DB data file. Bulk Changed Map (BCM) Page BCM page keep track of the extends which has modified by bulked-logged operations. Log backup thread reads this page and get the extends…
//
-
Data Page and Transaction Log Page
Hey guys, In this blog I am going to explain you about Data Page and Transaction Log Page. Data page- In SQL Server, the page serves as the basic unit of data storage. A collection of eight physically connected pages is referred to as an extent. Extents aid in effective page management. In all SQL…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL Performance Tuning PostgreSQL SQL Server
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…
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
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…
//