Diffrence between Full Backup vs Differential Backup vs Transaction Log Backup

Full vs Differential vs Transaction Log Backup:

SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.

Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.

Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.

Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.

You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.

Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.

The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.

If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.

It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.

Differential backup:

differential backup offers a middle ground by backing up all the files that have changed since the last full backup. That is where it gets its name: it backs up everything that's different since the last full backup.

In the image below you can see an example on how a differential backup would look like for a backup job that runs four times:

• • 

Restoring a differential backup is a faster process than restoring an incremental backup because only two backup container files are needed: the latest full backup and the latest differential.

Backup4all uses the information it has recorded in its catalog file (.bkc) to determine whether each file has changed since the last full backup.

Use differential backup if you have a reasonable amount of time to perform backups. The upside is that only two backup container files are needed to perform a complete restore. The downside is if you run multiple differential backups after your full backup, you're probably including some files in each differential backup that were already included in earlier differential backups, but haven't been recently modified.

Advantages:

1. Restore is faster than restoring from incremental backup
2. Backing up is faster than a full backup
3. The storage space requirements are lower than for full backup

Disadvantages:

1. Restore is slower than restoring from full backup
2. Backing up is slower than incremental backup
3. The storage space requirements are higher than for incremental backup

To resolve Deadlock other than Nolock hints

Please do not use NOLOCKs blindly. NOLOCKS results in dirty reads which can become a problem for your application. NOLOCKS are suggested when the tables thay are used against are seldom changed. If your tables have frequent transactions on them, NOLOCKS are not recommended.

To help minimize deadlocks, you can use any of the below suggesstion-

1. Access objects in the same order.
2. Avoid user interaction in transactions.
3. Keep transactions short and in one batch.
4. Use a lower isolation level.
5. Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. OR. Use snapshot isolation.
6. Use bound connections.

Check this link for further details in achieveing this.

Different Types of LOCK in SQL Server

Different Types of LOCK in SQL Server

Ref:
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

Locking Hints and Examples are as follows. The usage of them is same same but effect is different.

ROWLOCK
Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:
SELECT OrderIDFROM Orders WITH (ROWLOCK)WHERE OrderID BETWEEN 100AND 2000

UPDATE Products WITH (NOLOCK)SET ProductCat = 'Machine'WHERE ProductSubCat = 'Mac'

After Server Restarted Tempdb Added NDF file Would Exists or Not?

After Server Restarted Tempdb Added NDF file Would Exists or Not:

Suppose you have Tempdb 1 MDF ,1 LDF, 1 NDF  then  If you added 2 NDF file for performance Improvement Purpose then After Server Restarted would those Added NDF file Exist or not.

After added Server Restarted as See below





but it is still exists 2 Added NDF file as see below

How to Move TempDB to New Drive in SQL Server?

How to Move TempDB to New Drive in SQL Server

Introduction

This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server. However, for the changes to come into effect you must restart SQL Server Service.

Overview of Steps to move TempDB data and log files to new location are:-

1. Identify the location of TempDB Data and Log Files
2. Change the location of TempDB Data and Log files using ALTER DATABASE
3. Stop and Restart SQL Server Service
4. Verify the File Change
5. Delete old tempdb.mdf and templog.ldf files

Identify the location of TempDB Data and Log Files

In the New Query window of SQL Server Management Studio, execute the below mentioned script to identify the location of TempDB data and log file.

Use master
GO

SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Once you have identified the location of TempDB files then the next step will be to create the respective folders on the new drive where you would like to store the TempDB data and log file. However, you need to make sure that the new location where the TempDB files are stored is accessible by SQL Server. i.e., you need to ensure that the Account under which SQL Server Service is running has read and write permissions on the folder where the files are stored.

Change the location of TempDB Data and Log files using ALTER DATABASE

Execute the below ALTER DATABASE command to change the location of TempDB Data and Log file in SQL Server.

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev,
FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog,
FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

Once the above script has executed successfully you will receive a message to restart SQL Server Service for the changes to come into effect.

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Stop and Restart SQL Server Service

Stop and restart the instance of SQL Server for the changes to come into effect.

Verify the File Change

Execute the below TSQL to verify whether TempDB Data and Log files are residing in the new location.

Use master
GO

SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE
database_id = DB_ID(N'tempdb');
GO

Delete old tempdb.mdf and templog.ldf files

Final step will be to delete the tempdb.mdf & templog.ldf files from the original location.

Important Note: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

Conclusion

This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server.

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/#ixzz3ck1UK2B8
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Solve The transaction log for database Database is full and solve log_space_reuse_wait as “Replication"

The transaction log for database BizTalkMsgBoxDb is full - Steps to fix this error
Introduction

In this article we will take a look at the steps which you need to follow when you receive “The transaction log for database ‘BizTalkMsgBoxDb’ is full” error.

Error Message:

The transaction log for database ‘BizTalkMsgBoxDb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (.Net SqlClient Data Provider)

First thing would be to confirm the log space used by the BizTalkMsgBoxDb database. You can find this information by executing the below script.

DBCC SQLPERF(LOGSPACE)
GO

In the above snippet you could see that the log file of BizTalkMsgBoxDb database is 100% full.

How to fix Transaction Log for database BizTalkMsgBoxDB is full error message

To resolve this issue execute the below mentioned script which changes BizTalkMsgBoxDb database to Simple Recovery Model, Shrinks the Transactional Log file to 1 MB in Size and then Set the Recovery Model back to Full. Learn More about Different Recovery Models in SQL Server.

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO

Use BiztalkMsgBoxDb
GO
DBCC SHRINKFILE (BiztalkMsgBoxDb_log, 1);
GO

ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO

How to Verify Current Size and Space Used by the BizTalkMsgBoxDb Database Transaction Log File

To verify the current size and space used by the transactional log file of BizTalkMsgBoxDb database. One can either run SP_HELPDB command or DBCC SQLPERF(LOGSPACE).

/* Method One */

Use Master
GO
SP_HELPDB BiztalkMsgBoxDb
GO

/* Method Two */

DBCC SQLPERF(LOGSPACE)
GO

SQL SERVER – Huge Transaction Log in Snapshot Replication!

In a situation where the log space for the database is not getting reused and it is increasing in size. As usual, I asked to check sys.databases catalog view and we found that the log_space_reuse_wait as “Replication”.

The client has confirmed that they have replication configured on the database and it was SNAPSHOT replication.

It was not making sense to me why snapshot replication would keep a hold on LDF file. I asked to run DBCC OPENTRAN command and found below

Replicated Transaction Information:

Oldest distributed LSN     : (0:0:0)

Oldest non-distributed LSN : (82:2496:1)

Again, this was not making sense as snapshot doesn’t use transaction log to generate snapshot files. I went a step further and did below mathematics.

82 decimal = 52 hex
2496 decimal = 9C0 hex
1 decimal = 1 hex

So LSN in the output is 00000052:000009c0:0001

Select [Transaction ID] from fn_dblog(NULL, NULL)
where [Current LSN] = '00000052:000009c0:0001'

Found 0000:0000052c so we looked into log again to see the transaction information.

Select * from fn_dblog(NULL, NULL)
where [Transaction ID] = '0000:0000052c'

It was some ALTER TABLE command. Found below MSDN article

How to: Replicate Schema Changes

We executed below to find details about publication. The name is “snap”

sp_helppublication 'snap'

To fix the issue, I ran below

exec sp_repldone null, null, 0,0,1

NOTE: Above command is safe in snapshot replication because it marks _all_ transactions as distributed. This would cause problems in another type of replications.

Once above command is run, we need to run the snapshot agent again and then go to a regular procedure to shrink the T-Log file. Now, to prevent such incident in the future, I have asked to execute the following T-SQL batch to disable schema changes replication:

-- Sample Query
DECLARE @publication AS sysname
SET @publication = N'snap'

-- Turn off DDL replication for the publication.
USE Test
EXEC sp_changepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0
GO

This was a learning for me that ALTER can cause issue with snapshot replication.

How to Enable Database Users in SQL Server?

How to Enable Database Users in SQL Server:

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Enable-Database-Users-in-SQL-Server/#ixzz3cjshe4Wa 

Introduction

During a recent Production Database refresh we encountered a strange problem where all the Windows Groups/Users along with SQL Server Users within the Database were disabled.

When you connect to SQL Server Using System Administrator (SA) permissions and expand User Database -> Security -> Users you would find all of the Windows Groups/Users and SQL Server Users being disabled. A disabled user will have a small red down arrow icon against the user. However, you cannot enable or disable the database users using SQL Server Management Studio. 

You will receive the below mentioned error message when trying to connect to the database.

Error Message

The database AdventureWorks2012 is not accessible.

This issue generally happens when CONNECT permission to the user is disabled for the database.

Follow the steps mentioned below to identify and fix this issue.

Step1: Query to list all users within the database

/*
    Query to list all users within the database

    S = SQL_USER
    U = WINDOWS_USER
    G = WINDOWS_GROUP
*/

Use AdventureWorks
GO

SELECT
    principal_id AS [Principal ID]
    ,name AS [Database User Name]
    ,type AS [User Type]
    ,type_desc AS [User Type Description]
FROM sys.database_principals
    WHERE TYPE IN ('G','U','S')
        ORDER BY type ASC
GO

Step 2: Find all users within the database which are disabled.

You can find all the users within the database which are disabled either by using SSMS or using TSQL.

The below query will find all the Windows Groups/users and SQL Users which are in disabled state within the user database.

/*
Find all users within the database which are disabled.

    hasdbaccess = 0 Means User has no access
    hasdbaccess = 1 Means User has access
*/

USE AdventureWorks
GO

SELECT
    SU.NAME
    ,DP.principal_id
    ,dp.type
    ,dp.type_desc
    ,su.hasdbaccess
FROM sys.database_principals DP
INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
    WHERE DP.TYPE IN ('G','U','S')
AND SU.hasdbaccess = 0
    ORDER BY DP.TYPE ASC
GO

 

A disabled user will have a red arrow point downwards as seen in the below snippet.

 

Step 3: Generate Script to enable all Users who are disabled within the user database

/*
Generate Script to Grant Connect to Database Users

U = WINDOWS_USER
G = WINDOWS_GROUP

*/

Use AdventureWorks
GO

SELECT 'GRANT CONNECT TO [' + SU.name + '];' FROM sys.database_principals DP
INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
WHERE DP.TYPE IN ('G','U')
AND SU.hasdbaccess = 1
GO

Sample Output

GRANT CONNECT TO [Domain\User1];
GRANT CONNECT TO [Domain\AdminGroup];

Open a New Query window and run the above script under the context of the database to GRANT CONNECT to user and to resolve the issue.

Explain about Database Backup Compression Feature In SQL Server 2008

Database Backup Compression Feature In SQL Server 2008:

Ref:
http://www.mytechmantra.com/LearnSQLServer/Backup_Compression_Feature_In_SQL_Server_2008_P1.html

Introduction

Database Backup Compression was one of the most awaited features by Database Administrators in SQL Server 2008. Currently, this feature is only available in SQL Server 2008 Enterprise and Developer Editions. Databases which are backed up using database compression feature can be restored on all SQL Server 2008 editions. By default, this feature is turned off at the SQL Server Instance level.

Configuring Database Backup Compression Feature

Database Administrators can configure database backup compression feature of SQL Server 2008 at SQL Server instance level in the below mentioned two ways.

• Configure Database Backup Compression Feature Using SQL Server Management Studio
• Configure Database Backup Compression Feature Using TSQL 

You need to be a member of serveradmin or sysadmin fixed server role to configure database backup compression at SQL Server Instance Level.

Configure Database Backup Compression Feature Using SQL Server Management Studio (SSMS)

1. 1. Once you are connected to SQL Server 2008 Instance, right click the server and select properties.
   
   2. In the Server Properties Page, click on Database Settings node which is available on the left panel.
   
   3. On the right side panel, under Backup and restore you will be able to see a check box named compress backup.
   
   
   • When compress backup checkbox is unchecked then it means database backup compression is not enabled at SQL Server Instance level.
   • When compress backup checkbox is checked then it means database backup compression is enabled at SQL Server Instance level.
   
   

Configure Database Backup Compression Feature Using TSQL

You can also configure database backup compression feature at SQL Server Instance level using the below mentioned TSQL.

USE MASTER
GO

EXEC sp_configure 'backup compression default', '1'
GO

RECONFIGURE WITH OVERRIDE
GO

In this article we will first compare the time it takes to perform a native backup (uncompressed) and the time it takes to perform a compressed backups of AdventureWorks database.

Finally, we will compare the time it takes to restore a native backup (uncompressed) and compressed backups of AdventureWorks database.

The size of AdventureWorks database which is available with SQL Server 2008 is 178.75 MB. 

Native or Uncompressed Database Backup

Use the below query to perform a native or uncompressed backup of AdventureWorks database. 

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = N'C:\BackupCompression\AdventureWorks_Native.Bak'
WITH INIT, STATS = 25
GO

Database Name	Time Taken in Seconds	Database Backup Size (MB)
AdventureWorks	35.444	168.021

Compressed Database Backup

Use the below query to perform a compressed backup of AdventureWorks database.

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = N'C:\BackupCompression\AdventureWorks_Compressed.Bak'
WITH COMPRESSION, INIT, STATS = 25
GO

Database Name	Time Taken in Seconds	Database Backup Size (MB)
AdventureWorks	16.328	40.259

Disk Space Used By Databases

 

Disk Space Required for SQL Server Compressed Backups

In SQL Server 2008 / 2008 R2 you could have noticed that once SQL Server starts backing up your database using Database Backup Compression Feature it will allocates approximately 1/3 Disk Space for database backup file. The backup file can be either a Full, Differential or a Transactional log backup file.

For example, let us assume that the actual size of the database is 3 GB then when you perform a full backup of the database using Database Compression Feature, SQL Server will internally allocate 1 GB of disk space initially for the database backup file, but finally you may end up having a backup file which will be less than 1 GB.

However, you can override this behavior of SQL Server by enabling Trace Flag 3042 as mentioned in the following Microsoft Knowledge Base article Space requirements for backup devices in SQL Server.

Once you enable Trace Flag 3042, SQL Server will not extend the target backup device to a pre calculated size i.e. 1/3 of the actual database size. However, SQL Server will allow the backup file grow incrementally as like how native backup works.

/* Enable Trace Flag 3042 */
DBCC TRACEON (3042,-1)
GO
/* Enabled Trace Flags */
DBCC TRACESTATUS
GO

Important: As per Microsoft Knowledge Base article Space requirements for backup devices in SQL Server if you enable trace flag 3042 you may see a negative performance effect on the database compressed backup operation especially when the database is backed up to a remote network location.

Restoring Native or Uncompressed Database Backup

Use the below query to restore AdventureWorks database from a native backup.

USE master 
GO

RESTORE DATABASE AdventureWorks_Native  
FROM DISK = N'C:\BackupCompression\AdventureWorks_Native.Bak' 
WITH 
MOVE N'AdventureWorks_Data' TO N'C:\BackupCompression\AdventureWorks_Native.mdf',       
MOVE N'AdventureWorks_Log' TO N'C:\BackupCompression\AdventureWorks_Native.ldf',      
RECOVERY,   
STATS = 10
GO

Database Name	Time Taken in Seconds
AdventureWorks_Native	79.419

Restoring Compressed Database Backup

Use the below query to restore AdventureWorks database from a compressed backup.

USE master 
GO

RESTORE DATABASE AdventureWorks_Compressed  
FROM DISK = N'C:\BackupCompression\AdventureWorks_Compressed.Bak' 
WITH 
MOVE N'AdventureWorks_Data' TO N'C:\BackupCompression\AdventureWorks_Compressed.mdf',       
MOVE N'AdventureWorks_Log' TO N'C:\BackupCompression\AdventureWorks_Compressed.ldf',      
RECOVERY,   
STATS = 10
GO

Database Name	Time Taken in Seconds
AdventureWorks_Compressed	60.701

Conclusion

In this article you have seen how to considerably reduce the duration of both backup and restore operations by utilizing database backup compression feature of SQL Server 2008.

Read more: http://www.mytechmantra.com/LearnSQLServer/Backup_Compression_Feature_In_SQL_Server_2008_P3.html#ixzz3cf0qBucb