Tuesday, April 23, 2013

SQL Server Backup and Recovery Mode Information

The bellow queries help you to get information about backup and recovery mode in your server.
--When the last time  my databases are backed up
SELECT d.name, MAX(b.backup_finish_date) AS Last_Backup_Finish_Date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b
ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id NOT IN(2,3)
GROUP BY d.name

--The physical place for backed up database
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily

--Database Recovery Mode
SELECT d.name, d.recovery_model, d.recovery_model_desc
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'L'
WHERE d.recovery_model IN (1, 2) AND b.type IS NULL AND d.database_id NOT IN (2, 3)

written by: Elmozamil Elamir

Monday, April 22, 2013

Naming Convention Saves your Time

Naming Convention:
As a database administrator, database developer and software developer or any technical person you always works in project that may consist of many participants and even if you the only one in the project you may leave the company or after one year or more you asked to change or provide some information from you project. It really difficult to remember which stored procedure (job,  class) in your project doing this task and of course waste your time to remember which stored procedure.
So name convention helps you to save your time and other project participant can understand you code and from the name of the store procedure (class, job) they understand what it does.
Once you decide you have a project first agree on the name of the stored procedures (class, job) of course there are standard name convention but I talk about the meaning of the name of your store procedure. For example if you have a store procedure that insert a new employee what is the name of it? You may name it insertEmployee, EmployeeInsert, NewEmployee, NEInsert, and many of other available names but the better name that name that reflect the purpose of the stored procedure if you name it Insert_Employee every one see the name understand that this procedure will insert an employee but if you name it NE_Insert it difficult to know that N is abbreviation of New and E is for Employee. It is better to think carefully about this it will save your time.

Written by: Elmozamil Elamir

Thursday, March 28, 2013

SQL Server Backup and Restore

SQL Server Backup and Restore
Backup is the most important process to protect your database from unintentional/intentional users behavior – user may delete all data from a table accidentally or update it – if one of your disk driver fails, and sometimes you need a version of your operational data on test server, etc.
Recovery Modes
SQL Server provide three recovery mode, any one will provide a recovery method against failures, to decide which recovery model you should use first you have to understand your environment and your needs for recoverability of your database e.g. if you use full recovery mode you need more desk space than if you use Bulk-logged or simple recover mode. And also if you need point in time recovery simple and bulk-logged recovery won’t help.
Types of backup you may run against your database will be determined by the recovery mode you need e.g. if your database in simple recovery mode you can’t perform transaction log backup.
·         Full Recovery mode:
In full recovery mode all transactions that run against database will be recorded in the transaction log file (.ldf) and the transaction log won’t be truncated even if the transaction has been committed nor full backup has been performed.
Full recovery mode provide restore to the point of failure, if there is a failure in your database the SQL server when restart against it will redo all committed transaction and undo all uncommitted transactions.
Full recovery mode support point-in-time recovery.
·         Bulk-logged Recovery mode:
In bulk logged most bulk operation, index creation will be minimally logged, this will help to minimize size of the log file.
This type of recovery frequently use when there is a full recovery mode and you have bulk operation and you want to minimize the log operation you will switch form full recovery to bulk logged. This operation will increase performance and minimize the log file size because of the minimum log operation.
Bulk-logged doesn’t support point-in-time recovery.
·         Simple Recover Mode:
Simple recovery mode write all active transactions to transaction log file (.ldf) and after the transaction committed it will remove the transaction for transaction log file this will minimize the administrative effort for transaction log file and the log file will be small any won’t have many growth.
Simple recovery mode doesn’t support point-in-time recovery.
Which recovery mode should I use?
There is no correct or wrong answer for this question. All you can say it depends.
If you have a lot of concurrent user using your database and your database is very critical and you need point-in-time recovery there is no solution other than full recovery mode.
If you have limited resources and the point-in-time recovery isn’t important for you go ahead with simple recovery mode.
Backup Types
·         Full Backup
Full backup contains all data till the time the full backup was performed.
Every time you run full backup your whole database will be backed up.
Full backup is considered the base for all other backup types.
·         Differential Backup
Differential backup (also called incremental backup) contains all data since the last full backup.
e.g if you have a full backup on Friday (last full backup) and you perform differential backup on Saturday your full backup contains whole database till Friday but your differential backup contains all data between Friday and Saturday.
And if you run another differential backup on Sunday this backup contains data between Friday and Sunday.
When you perform full backup against your database SQL Server put mark to indicate which data has been backed up and your differential backup will backup for this mark till the time the differential backup has been performed.
·         Copy-only backup
Copy-only backup similar to full backup except it doesn’t mark backed up data.
If you perform full backup on Friday, copy-only backup on Saturday, and differential backup on Sunday your differential backup will contain data between Friday till Sunday.
·         Transaction Log Backup
Transaction log backup is a process of backing up the transaction log which contains log of all transactions run against the database you can truncate the file at the end of the backup or not, but I believe it is better to backup transaction log with truncate (it is the default option) this will help you to maintain your transaction log file size and also you can get small size of log backup file and this also affect your restore time and the point to restore. For instance if you schedule a backup of transaction log file each five minutes this mean you can lose not more than five minutes in case of failure in your system.
The database should be in a full recovery model or bulk-logged recovery mode.
Database restore
It always depends on your database and the acceptable time of data loses and time to recovery. The small time interval backup means you need more space to store these files e.g. if you do a daily full backup (e.g. 100 GB) and differential backup (2 GB) each four hours and hourly transaction log backup (1 GB) this means you need more space than if you have a weekly full backup and daily differential backup and hourly transaction log backup and you have data lost of one hour in each cases but the time to restore is different the second configuration need more time to restore than the first one.
To restore each database first you should have a full backup of database the full backup is the base backup to restore your database.
If your database in full recovery mode you should first backup the tail log (run a transaction log backup) and then restore your full backup, last differential backup, all transaction log backups since the last differential backup and finally the tail log backup.
For example if you have
Backup Type
Start Time of backup
11:00 PM
Each for hour
1:30 AM
Transaction log

If your database fails on 10:59 AM on Sunday to restore your database:
·         First you should backup the tail of the log.
·         Second restore the full backup taken on Saturday at 11:00 PM with no recovery option.
·         Third restore the last differential backup (it will be the backup taken at 9:00 AM on Sunday) with no recovery option.
·         Forth restore the transaction log since the last differential backup (transaction log taken after 9:00 AM on Sunday) it will be the transaction log taken at 9:30 and 10:30 and restore it with no recovery option.
·         Fifth restore the tail of the log (look at First point) and restore it with recovery.
Notice we only lose about 29 minute of data the data between the last transaction log backup and the failure and if our database fails at 11:29 we also can only restore to 10:30 because this is the last transaction log backup and we lose about an hour of data.
The with no recovery option is used to put the database in the restoring state and it will prevent the database from being accessed by the user and the you will restore all of your file and after that make the database available to the user and recover it.

Please refer to Microsoft online book and see more details about how to do this and you can google to fine more scenarios available.

Written by Elmozamil Elamir

Sunday, February 17, 2013

SQL Server Batch Update

Always we should take care of database server performance but sometimes you need to perform a huge update on your table/tables this will affect performance for concurrent users and they will complain about it.
For example if you have a table contains more than billion row and you want to update a column of this data and your database is accessible 24 hours 7 days a week it is difficult to run this code in simple update statement because a lot of data will be fetch to your memory and a lot of user data will be kicked off out of memory.
So the solution is to use batch update by divide your data in small batches, e.g divide it to update 1000 row at a time and stop for a moment and the update the next 1000 and so on, this will let other users to use share the server with you.
Sample code:
-- SQL update in batches of 1000
WHILE (1 > 1)
    UPDATE TOP ( 1000 ) S
    SET    Price = Price * 1.08    
    IF @@ROWCOUNT = 0
    -- 2 second delay
    WAITFOR DELAY '00:00:02'
* the above code is updated version of a code that available online.
Written by: Elmozamil Elamir Hamid