SQl Server : Backups

There are three types of backups:

  1. Full backup
  2. Differential backup
  3. Transaction log backup

Full backup

A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.

Differential Backup

A “Differential” backup is a backup of any extent that has changed since the last “Full” backup was created.

Each time a new differential backup created it will contain every extent changed since the last full backup (NOT since the last differential backup).

When the database needs to be restored to the most recent time the user only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.

Transaction log backup

The transaction log is a serial record of all the transactions that have been performed against the database since the last transaction log back up.

With transaction log backups, you can recover the database to a specific point in time.
(see http://www.mssqltips.com/sqlservertutorial/119/sql-server-point-in-time-restore/)

Database should be set to “Full” or “Bulk-logged” recovery model for “Transaction Log” backups.

If the database is set to the “Bulk-logged” recovery model and a bulk operation was issued, then the entire transaction log needs to be restored.

A transaction log backup allows the user to back up the active part of the transaction log.  So after a “Full” or “Differential” backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

Reference:
http://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-backups/
http://www.todo-backup.com/backup-resource/sql-backup-software/sql-backup-types.htm
http://msdn.microsoft.com/en-us/library/ms187048.aspx

SQL Server : Bringing a database online from restoring or standby modes

If the database is in restoring state run the command below to bring the database online:

RESTORE DATABASE <Database Name> WITH RECOVERY


If the database is in read-only (standby) mode you can bring the database online in two steps

Step 01: Disconnect all the connections to the database
USE master
ALTER DATABASE <Database Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <Database Name> SET ONLINE

Step 02: Bring the database online
RESTORE DATABASE <Database Name> WITH RECOVERY

SQL Server : Restore Database using multiple backup files

Here is an example script to restore a database from multiple backup files and the destination database has several data files.


USE [master]
RESTORE DATABASE [TestDB]
FROM  DISK = N'G:\Temp\TestDB_20141031_033500_00.bak',
DISK = N'G:\Temp\TestDB_20141031_033500_01.bak',
DISK = N'G:\Temp\TestDB_20141031_033500_02.bak'
WITH  FILE = 1,
MOVE N'TestDB_Data' TO N'E:\SQLData\TestDB_Data.mdf',
MOVE N'TestDB_1_Data' TO N'E:\SQLData\TestDB_1_Data.ndf',
MOVE N'TestDB_2_Data' TO N'E:\SQLData\TestDB_2_Data.ndf',
MOVE N'TestDB_3_Data' TO N'E:\SQLData\TestDB_3_Data.ndf',
MOVE N'TestDB_Log' TO N'H:\SQLLog\TestDB_Log.ldf',
REPLACE, RECOVERY

SQL Server : Logshipping Errors

Error 1:

Logshipping backup job fails at the primary server with the error message below:


Message
2014-10-31 14:36:18.44    *** Error: Backup failed for Server ‘<Server Name>’. (Microsoft.SqlServer.SmoExtended) ***
2014-10-31 14:36:18.45    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-10-31 14:36:18.45    *** Error: BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2014-10-31 14:36:18.50    —– END OF TRANSACTION LOG BACKUP   —–

Solution

This message is thrown because the database is never backed up fully before. Do a full backup of the primary database to solve this issue.

Error 2:

Restore database job at the secondary server failed with the error message below:

Error: The restore operation cannot proceed because the secondary database ‘<Database Name>’ is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping)

Solution :

Restore the secondary database with the full backup of the primary database with NORECOVERY and REPLACE option. An example is given below:
USE [master]
RESTORE DATABASE [TestLST] FROM  DISK = N'G:\Temp\TestLS.bak' WITH  FILE = 1,
MOVE N'TestLS' TO N'E:\SQLData\TestLS.mdf',
MOVE N'TestLS_log' TO N'H:\SQLLog\TestLS_log.ldf',
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

Error 3:

After some disk issues at the production server the logshipping continually failed. So I tried a full back up the primary database and restored the secondary database with this full  backup and started the log shipping again. And the logshipping backup of the primary database failed with the error message below:

Message
2014-10-31 13:34:00.33    *** Error: Backup failed for Server ‘<Server Name>’. (Microsoft.SqlServer.SmoExtended) ***
2014-10-31 13:34:00.33    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-10-31 13:34:00.33    *** Error: BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
Processed 8 pages for database ‘<Database Name>’, file ‘<Database Name>_Data’ on file 1.
Processed 2013584 pages for database ‘<Database Name>’, file ‘<Database Name>_1_Data’ on file 1.
Processed 1380536 pages for database ‘<Database Name>’, file ‘<Database Name>_2_Data’ on file 1.
Processed 1671736 pages for database ‘<Database Name>’, file ‘<Database Name>_3_Data’ on file 1.
90 percent processed.(.Net SqlClient Data Provider) ***
2014-10-31 13:34:00.37    —– END OF TRANSACTION LOG BACKUP   —–
Exit Status: 1 (Error)

Solution:

One of the solutions is to configure the log shipping all over again so the logshipping job will initialize the secondary database.

The other solution is to follow the steps below: (Ref: http://www.sqlskills.com/blogs/paul/how-can-a-log-backup-fail-but-a-full-backup-succeed/)

  1. Stop all user activity in the primary database
  2. Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up)
  3. Switch to the FULL recovery model
  4. Take a full database backup (thus starting a new log backup chain)
  5. Start the logshipping job

Error 4

Logshipping primary database backup job failed with the following error:

Message
2014-11-03 14:53:02.80    *** Error: Backup failed for Server ‘<Server>’. (Microsoft.SqlServer.SmoExtended) ***
2014-11-03 14:53:02.85    *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2014-11-03 14:53:02.85    *** Error: Cannot open backup device ‘\\<Server>\LogShippingBackups\DatabaseName\DatabaseName_20141103145302.trn’. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Solution

SQL Server agent does not have appropriate permission to access the shared folder. Granting access solves this problem.

SQL Server : Option Clause in Views

I had a task to create MasterCalendar view which will return all the dates within a range. Here is the simplified query that needs to be included the view :

MasterCalendar_2

If you run the above create view statement, sql server will throw the following error:

Incorrect syntax near the keyword ‘option’.

It throws error because views do not allow option clauses. So the workaround would be to create the view without the option clause and when you call the view include the option clause as below:

MasterCalendar_3

PS:
Here is the code from the screen shot:
create view vw_MasterCalendar
as
with cte(N) as
(
select    cast('20120101' as datetime) as N
union all
select    cte.N + 1
from    cte
where    cte.N < cast('20130101' as datetime)
)
select    N as [Date]
from cte
go
select * from vw_MasterCalendar option (maxrecursion 1000)

SQL Server : Move database files to another location

There may be situations where you need to move database files from one drive to another. You can move database files of a non-system database as follows:

  1. Right click on the database and select Tasks –> Take Offline
  2. Once the database is offline, detach the database by right click on the database, select Tasks –> Detach
  3. Copy the data and log files to the new location.
  4. Right click on the Databases folder and select Tasks –> Attach
  5. In the Attach Databases window click on Add button and browse and select the data file from the new location and click OK

Follow the steps below to move TempDB to a new directory:

  1. Run the query below with the new location

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TempDB\templog.ldf');
    GO
  2. Restart the SQL Server
  3. SQL Server will then create tempDB files in the new location
  4. Delete the old files

You can check the location of the database files with the query below:


SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

SQL Server : Deadlocks

Deadlocks can be difficult to investigate and identify the causes for it as it does not happen often and can be difficult to reproduce it. Here are some ways to investigate the deadlock issues.

  1. You can run the SQL profiler with deadlock template but it is resource heavy and not a good idea to run on the production environments as it can cause the server to slow down. And you wouldn’t know when the next deadlock will happen.
  2. You can enable the xml_deadlock_report event in extended events. This will capture the information about the deadlocks. Then you can run the query below to get the information in XML format.


select xed.value('@timestamp', 'datetime') as Creation_Date
, xed.query('.') as Extend_Event
from
(
select cast([target_data] as xml) as Target_Data
from sys.dm_xe_session_targets as xt
join sys.dm_xe_sessions as xs on xs.address = xt.event_session_address
where xs.name = N'system_health'
and xt.target_name = N'ring_buffer'
) as XML_Data
cross apply Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') as XEventData(xed)
order by Creation_Date desc

  1. You can turn the deadlock tracing on by running the following in query analyzer:

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

Then you can view the trace in SQL Server Agent –> Error Logs

SQL Server Error : The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Two of my colleagues had the following error recently and I helped them resolve it. So I thought of writing a blog on how to prevent this error with a simple example.

The error is :

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The following code will throw this exception:



--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1)    -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
insert error(error)
select @error

if @@trancount > 0
rollback
end catch


The exception is thrown because it is trying to write to a table within the catch block before rolling back the transaction. The solution is to move the insert statement after the rollback. And it is really important to log error messages after the rollback bacause the logged messages also will be rolled back with the subsequent rollback tran statement. The following code will not throw the exception:



--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1) -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
if @@trancount > 0
rollback
insert error(error)
select @error

end catch

SQL Server : Search Arguments (SARG)

Always try to make the queries sargable to optimize the query. If the query is not sargable the query optimizer has to scan all the rows in the table even if that column has an index.
Here are some examples of non-sargable queries and how they can be fixed.
The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:
 
SELECT ... FROM ...
WHERE Year(myDate) = 2008
 
The SQL optimizer cant use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'
 
Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())