How to Change recovery model to simple and Backup Database, Restore Database (Dev & QA)



  SIMPLE RECOVERY MODEL:
  • The "Simple" recovery model is the most basic recovery model for SQL Server.  
  • Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file(mdf) the space that was used in the transaction log file is now re-usable by new transactions.
  •   Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the full backup or the latest differential backup that was completed.  
  • Also, since the space in the transaction log can be reused, the transaction log will not grow forever as in the Full recovery model.
How to set the simple recovery model using T-SQL.
ALTER DATABASE dbName SET RECOVERY recoveryOption
GO
Example: 
change saily database to "Simple" recovery model
ALTER DATABASE Saily SET RECOVERY SIMPLE
GO
How to set using SQL Server Management Studio
  • Right click on database name and select Properties 
  • Go to the Options page 
  • Under Recovery model select "Simple" 
  • Click "OK" to save

http://www.mssqltips.com/tutorialimages/2_Reco1.jpg

How to Backup database when the Recovery model is simple:
Type of backups you can run when the data is in the Simple recovery model:
  • Full backups 
  • Differential backups 
  • File and/or Filegroup backups 
  • Partial backups 
  • Copy-Only backups 


TOP 10 DO’S and DONT’S of DBA:


DO’S:
1)Do watch servers closely and be alerted when processes are slowing down. When you see a problem, do react as quickly as possible.
2)Do track usage by end users. You can't test end user usage in the test environment, so keep close tabs on them after deployment.
3)Do focus on top-level business needs, rather than furthering your own technical aptitude.
4)Do beware of Deadlocks and timeouts.
5)Do learn how to read a Query Execution Plan.
6)Do employ key management practices, such as automated monitoring. That way, relying on specific manual processes to fix problems is not necessary. 
7)Do Maintain your Expertise.
8)Do get in the right frame of mind.You don't need to focus your efforts on writing code to solve every problem. If you do, "there are hundreds of things you they will need to write,". Instead, focus on implementing and administering management products.
9)Do implement good rollout processes. Once testing is complete, make sure the production center matches the test environment.
10)Do normalize your tables.

DONT’S:

1)Don't rush in applying changes to the database without reviewing/testing first.
2)Don't Waste Time Re-Organizing Your Databases
2)Don't Use temporary tables.
3)Don't Leave Your Database Open To Attack
4)Don't implement something that has never been tested.
5)Don't rely on gui tools to accomplish any task. Great DBAs can endure the SQL queries to get the answer done.
6)Don't create an irreversible change on the server. 
7)Don’t wait until you have to restore to validate your backup. Validate your backups once backups are taken.
8)Don't Use Common Extensions For Your Database File Names.
9)Don't Forget to Document Everything
10)Do Not Use tools You are Not Licensed For.


RECOVERY MODELS




The basic use of the recovery model is that, it allows SQL server to control the transaction log for a database and helps recover the data in log file during the time of any disaster or a crash.
Recovery models are of three types:
  1. Simple Recovery Model
  2. Full Recovery Model
  3. Bulk-Logged Recovery Model


Setting the Recovery Model:

You can set the recovery model for a database using the GUI as well as T-SQL statement.


GUI Method: 




T-SQL Method:


ALTER DATABASE dbName SET RECOVERY recoveryOption
GO


Simple Recovery Model:
The Simple Recovery Model is used, when the data is not critical and is enough to restore the data till the last full backup or a differential backup. Hence, point in time restore is not possible with Simple Recovery Model as there are no log backups.
Why are log backups not possible?
According to the transaction log architecture of Simple Recovery Model, whenever any transaction (DML operations like INSERT, UPDATE or DELETE) takes place, it is written from log manager to log file. The log file is divided into several VLFs depending on the size of the log file. The transactions take place in the VLFs itself. After the transactions are written or if the log file gets filled, the SQL Server comes back to the first VLF and starts checking for the committed and uncommitted transactions in a sequential manner. If there are any committed transactions in the VLFs, they are written to the data file (.mdf) by the checkpoint and the transactions are auto truncated. Since, there are no logs; the log backup is not possible. If the Auto growth is set on to unlimited, the transactions (uncommitted) keep on growing till the drive is full.


Full Recovery Model:
Full Recovery model is used, if you require a point in time recovery. We are able to recover to the latest point of time with the help of log backups. Transactions are fully logged in.
According to the transaction log architecture of the Full Recovery Model, unlike the Simple Recovery Model the transactions don’t get auto truncated after they are committed but log truncation occurs only after the log backup is taken. The committed transactions are hardened by the checkpoint process.


Bulk-Logged Recovery Model:
The Bulk-Logged Recovery Model is rarely used. It is used whenever a bulk transaction (INSERT, UPDATE) is being performed on the database. Generally, in the Bulk-logged recovery model, the transactions are minimally logged in.  That is, rather than writing each and every transaction like in the Full Recovery Model, it stores only the summary of the transactions.
The main idea behind using the Bulk-Logged Recovery Model rather than Full Recovery Model is to control the log space. For Example, if a bulk insert operation of 100000 records is done, the transaction log will become very huge that the space would not be sufficient and may also crash. Hence, we change the recovery model to Bulk-Logged so that all the transactions are treated as a single transaction and the log file space is saved. In the event of crash during the bulk transaction, point in time recovery would not be possible since, it contains only the summary information.
The Bulk transactions are issued by BCP or using the BULK keyword before the DML operation. Even though, we change the recovery model to Bulk-Logged and issue a transaction without the BULK command, it is  not taken as a bulk transaction.

PAGES AND EXTENTS ARCHITECTURE

              For Any sql server pages and extents are very important because those are the basic units of the data storage.  
             Before going into the topic we must know about where is this pages concept in a database. So basically we use pages for the data storage. Data storage is a database, which is a collection of tables. The data in the database are stored in primary data files with an extension (.mdf). Secondary data files, identified with a (.ndf) extension, are used to allow the data of a single database to be spread across more than one file. Log files are identified with the (.ldf) extension.

PAGES:
The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
  • In SQL Server, the page size is 8 KB. 
  • Each page begins with a 96-byte header that is used to store system information about the page. 
  • The row off set is of 36byte 2 bytes for each row.
  • This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.










  • Data rows are put on the page serially, starting immediately after the header. 
  • A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains Varchar or Varbinary data, the data in those columns are moved to a new page.
  • A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. 
  • The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.



Different types of Pages in sql server:
In general there are 9 types of pages are there.
They are:
  1. Data Page
  2. Index Page
  3. Text/image page
  4. GAM/SGAM page
  5. IAM page
  6. BCM page
  7. DCM page
  8. Page free space
  9. Boot Page
Now let checks what page will do what work.

  • Data Page: 
This holds data records. In these data records it stores table data. Each row you add to a table is stored on a page, and depending on the size of the data in the row, the row can be stored either on a page with other rows, or on its own page or pages. Data rows with all data, except text, ntext, image, nvarchar(max), Varchar(max), Varbinary(max), and xml data, when text in row is set to ON.

  • Index Page:
Index page is a page which gives the index entries and it points to the next page when the data in a page exceeds 8kb.

  • Text/Image Page:
Text/Image page gives the information of large object data types: Text, ntext, image, nvarchar (max), Varchar (max), Varbinary (max), xml data and also the  
Variable length columns when the data row exceeds 8 KB: Varchar, nvarchar, Varbinary, and sql_variant 


  • GAM/SGAM page: 
Global Allocation Map and Shared Global Allocation Map are pages which give the information about whether the extents are allocated or not allocated. The major difference between the GAM and SGAM is GAM looks is used to trace the allocation of an extent whereas the SGAM is used to trace the allocation of shared extent.




  • IAM page: 
Index allocation map page is to track which pages have been allocated to which allocation unit. Each IAM page covers an area of 64000 extents in a file, a so-called GAM-interval. If multiple IAM pages are needed for a single allocation unit, the form a double linked list, the IAM chain.

  • BCM page: 
Bulk change map page gives the Information about extents modified by bulk operations. Whenever there is a change in any extent by bulk operations then this BCM sets its bit 0 to 1 so that whenever we are using bulk recovery model BCM helps sql to know about the changed extents. Once after the full backup these extents again change their bits 1 to 0. 

  • DCM page: 
Differential change map page gives the Information about extents modified after a full backup. Whenever we take a differential backup after a full back up the DCM looks for the extents that are changed after a full backup and sets the extents bits 0 to 1. By this Differential backup can take the backup of those extents only. Once after the full backup these extents again change their bits 1 to 0.

  • Page free space: 
PFS gives the free space present in a page.

  • Boot Page: 
Boot page stores the information of the page.

Do you want to see what pages are used for your table in which you've stored your record? Run the following DBCC command:
DBCC PAGE ({'dbname' | dbid}, filenum, pagenum [, printopt= {0|1|2|3} ])
The printopt parameter has the following meanings:
  • 0 - print just the page header
  • 1 - Page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)
  • 2 - Page header plus whole page hex dump
  • 3 - Page header plus detailed per-row interpretation

The per-row interpretation work for all page types, including allocation bitmaps.
By default, the output is sent to the error log. If you want the output to come back to your current connection, turn on trace flag 3604.




EXTENTS:

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. SQL Server has two types of extents:

  • Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object. All pages from a dedicated extent must be allocated to the same IAM chain

  • Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object. These mixed pages are allocated from mixed extents that are not allocated to any particular IAM chain. 







    (FIG: Uniform and Mixed Extents)


How to create a database and how to change the database owner to ‘sa’?

  Task-1:     HOW TO CREATE THE DATABASE:
There are various methods to create a database:
Method-1:  With the help of GUI:
  • In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  • Right-click Databases, and then click New Database.

  • In New Database, enter a database name.

  • To create the database by accepting all default values, click OK.

Otherwise , continue with the following optional steps.
  • To change the owner name, click (…) to select another owner.

  • To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value.

  • To change the collation of the database, select the Options page, and then select a collation from the list.

  • To change the recovery model, select the Options page and select a recovery model from the list.

  • To change database options, select the Options page, and then modify the database options. 

  • To add a new filegroup, click the Filegroups page. Click Add and then enter the values for the filegroup.

  • To add an extended property to the database, select the Extended Properties page. 

  • In the Name column, enter a name for the extended property.

  • In the Value column, enter the extended property text. For example, one or more statements that describe the database.

  • To create the database, click OK.


Method-2:  Using Script:
CREATE DATABASE [DB_name] ON  PRIMARY 
( NAME = N' DB_name ', FILENAME = N'E:\SQL\Data\DB_name.mdf' , SIZE = ‘size’ , FILEGROWTH = ‘filegrowth’ )
 LOG ON 
( NAME = N'DB_name_log', FILENAME = N'F:\SQL\Log\DB_name_log.ldf' , SIZE = ‘size’ , FILEGROWTH = ‘filegrowth’ )
GO
For example:
CREATE DATABASE [saily] ON  PRIMARY 
( NAME = N'saily', FILENAME = N'E:\SQL\Data\saily.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB )
 LOG ON 
( NAME = N'saily_log', FILENAME = N'F:\SQL\Log\saily_log.ldf' , SIZE = 38912KB , FILEGROWTH = 25600KB )   GO

Task-2:  HOW TO CHANGE DATABASE OWNER TO SA   :

METHOD-1: 
  • Run the following query and the database owner will be changed to SA.

  ALTER AUTHORIZATION ON DATABASE::[MyDatabase] to sa;

Example: ALTER AUTHORIZATION ON DATABASE::fred to sa;

METHOD-2:
·       Run the following query and the database owner will be changed to SA


USE [Db_name]
EXEC sp_changedbowner 'sa'

Example:       USE saily
         EXEC sp_changedbowner 'sa'

METHOD-3:
  • We can change the database owner through GUI.

  • You can actually change it in SQL Server Management Studio under Database / Properties / Files

  • Right click database ->click properties ->files ->Owner, and change the owner to SA.


METHOD-4:
  • To change all the databases present in the server to sa, follow the following steps:

Step 1:
Execute the following script:
 declare @name varchar(100)
declare @cmd nvarchar(200)
begin
declare db_cur cursor
for
select name from sys.databases where name not in('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open db_cur
fetch next from db_cur into @name
while @@FETCH_STATUS=0
begin
select @cmd=N'ALTER AUTHORIZATION ON DATABASE::'+@name
set @cmd =@cmd+' to sa'
 print @cmd
fetch next from db_cur into @name
end
close db_cur
deallocate db_cur
end

Step 2:
copy the script from messages 

Step 3 :
Execute the script. Then you find that all the databases owner is changed to sa.

To check the owner name of the database use the following methods:
Method-(i):
Right click database ->click properties ->files ->Owner.

             Method-(ii):
   You can also check the owner name using the below query after you modified.
select suser_sname(owner_sid) from sys.databases where name = 'databasename'

IMPORTANT NOTE:
The databases whose ownership can’t be changed:
1) Master
2) Model
3) Tempdb
By default their owner will be ‘sa’
If you try to change the owner the following error message comes:
“Cannot change the owner of the master, model, tempdb or distribution database.”
We can change the owner of msdb in system databases.