Steve Jones' 10 Tips for Efficient Disaster Recovery

Steve Jones' 10 Tips for Efficient Disaster Recovery

Renaming the database

in this example I am renaming the test2 database to test3 database

alter database [test2]
set single_user

GO
alter database [test2]
modify name = [test3]

alter database [test3]
set multi_user

How to rebuild or Restore corrupted master database?

There are some times master database needs to rebuild due to corrupt or some other issues. In real world every day your backup jobs will be running and do backups for your application databases along with system databases..

Here are the steps you can follow how you can rebuild the master database.

1) find setup  C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release. and run the following command

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<accounts>

once the command is executed and message will display databases are rebuild.

2) Now restore good backups of master,model,msdb databases in order to run properly according to your sql server original configurations.

Blocking Locks and Deadlocks

Blocking Locks

A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable.

Deadlocks

Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions.

Lock-Avoiding Design Strategies

There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

• Use clustered indexes on high-usage tables.
• Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
• Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions.
• Make sure that UPDATE and DELETE statements use an existing index.
• If you use nested transactions, be sure there are no commit or rollback conflicts.

How do you attach a mdf data file without a log file?

Make sure copy the .mdf data file into a folder where you will attach database. in my example I have copied under C:\data folder and run the following script.

CREATE DATABASE [AdventureWorksDW2012] ON 
( FILENAME = N'C:\Data\AdventureWorksDW2012_Data.mdf' )
 FOR ATTACH_REBUILD_LOG
GO

Return SQLResult set into a HTML format.(Microsoft Sql server)

Please find the following script, which will show the backup status of db's in html format.

                                           
-- 
























=============================================
-- HariPrasad Ere 08/06/2013
-- Send BackUpStatus For each db
-- =============================================

--Create a Temp table to insert last backcompleted records.
USE msdb 
CREATE TABLE #Backuptbl
(
backup_finish_date DATETIME,
database_name      VARCHAR(50)
)
--Insert the previous day backup completed records into #Backuptable 

INSERT INTO #Backuptbl (backup_finish_date,database_name )
SELECT MAX(backup_finish_date) AS BackUp_Finish_Date ,database_name
FROM backupset b inner join
sys.databases d
ON b.database_name = d.name 
WHERE backup_finish_date < GETDATE() 
GROUP BY database_name 
ORDER BY database_name DESC

--Create Email Message

DECLARE @Subject VARCHAR(100)
SET @Subject = ' DataBase BackUp Status' + CONVERT(VARCHAR(10),GETDATE()-1,101)
Declare @Message VARCHAR(MAX)
SET @Message = 'Team, <BR> <BR>' 
SET @Message = @Message + CHAR(13) + CHAR(10) + 'LAST BackUp Finish Date : ' 

SET @Message = @Message + CHAR(13) + CHAR(10) + '<Table border = 1 width = 50%>'
SET @Message = @Message + CHAR(13) + CHAR(10) + 
'<tr><td width  = 25%><font face = Arial align = left ><B>DataBaseName</B></font></td><td width  = 25%><font face = Arial align = left><B>backup_finish_date</B></font></td></tr>'

Select @Message = @Message + CHAR(13) + CHAR(10) +'<td><font face = Arial>' + database_name

+ '</font></td>' +'<td nowrap><font face = Arial>' +  CONVERT(VARCHAR(10),backup_finish_date, 101)+ '</font></td></tr>'

FROM #Backuptbl
--copy the fllowing text message and paste into a notepad, Save the notepad as HTMl. open the saved html to view the results. you can call variable@message from sp_sendmail master db stored procedure.
SELECT @message
--clean up the objects
DROP  TABLE #Backuptbl

Contained Databases

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

Benefits of using Partially Contained Databases

---

There are issues and complications associated with the non-contained databases that can be resolved by using a partially contained database.

Database Movement

when you move database from one instance to another instance, If a login/users are at instance level not at database level and this object is left and it is time consuming to find the user.

The partially contained database can store important information in the database so the database still has the information after it is moved.

to enable the option run the following script in SSMS 2012

USE master

GO

sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1

GO

RECONFIGURE

GO

sp_configure 'show advanced options', 0 

GO

RECONFIGURE

GO

when you create a database add option SET CONTAINMENT = PARTIAL.

EX: ALTER DATABASE MYDB1 SET CONTAINMENT = PARTIAL GO

Do you backup tempdb database?

The answer is no, you cannot drop or backup the temp db. However you can increase or decrees the size.

open ssms run the following script.

backup database [tempdb]
to disk = 'C:\tempdb.bak' with stats =  10

it will return the error message

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.

How do you create a database in sql server?

You can use either SSMS or TSQL. But I prefer TSQL.

Syntax: Please do refer online for full syntax





CREATE DATABASE database_name

[ CONTAINMENT = { NONE | PARTIAL } ]

[ ON

      [ PRIMARY ]
<filespec> [ ,...n ]

      [ ,
<filegroup> [ ,...n ] ]

      [ LOG ON
<filespec> [ ,...n ] ]

]

There are three key things are important while creating a
database.

·        
Databasename

·        
Datafile

·        
Transactionlog

For both data and Transaction log files you need to specify
logical and physical names.

Logical name is used with in the sqlserver and physical name
specifies the actual path and name on the OS level.

Example:

1) Open SSMS run the following script

 Create Database Wineworld



The above code will execute and and create wineworld.mdf
data file and wineworld.ldf log file.

Both files will be created under specified default
directories Of Sql server during installation.



But this is not a good practice to create database. In the
following steps you will know how a database will be created.





1) Create a folder C:\Wineworld\Data for data
C:\Wineworld\Log.

2) Launch SSMS run the following script



USE [master]


GO


/****** Object: Database [Wineworld] Script Date: 8/1/2013
9:16:14 PM ******/


CREATE DATABASE [Wineworld] ON PRIMARY



( NAME = N'Wineworld', FILENAME = N'C:\wineworld\Data\Wineworld.mdf'
, SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )



LOG ON



( NAME = N'Wineworld_log', FILENAME =
N'C:\wineworld\Log\Wineworld_log.ldf' , SIZE = 39936KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)



GO

How many file groups does database contains?

32,767 out of this one is default primary group and 32,766 are user defined file groups.

why you need to install more than one instance of sql server on a single machine.

Development and QA purpose.

Supporting multiple service packs and patches.

How many SQLserver instance can own on a single machine?

you have one default instance and 49 named instances. So totally a machine contains 50 instances.
when you loging into default instance you can provide machine name. For named instances you have to provide machine name and instance name

Ex:

default instance:  <Computer name>
named instance: <Computer name>/named instance or <computer name>,portnumber

How much data SQL Server can handle?

SQL Server 2008R2 and  2012  have maximum capacity of 524 PB (Petabyte) in the Enterprise, BI and Standard edition.
SQL Server Express has a limitation of 10 GB .

CreatingSqlserverwindowsandCredentials

/*

Sql server Security and Management for DBA

Create By HariPrasad Ere

*/

--To view information about logins

/*syntax sp_helplogins <loginname>

this will output loginame,security indetifier(SID),default database and language

*/

sp_helplogins 'sa'

--Returns one row for every server principal that is part of the login token.

USE master
select * from sys.login_token

/*It's time to create login */

/*

create login <login_name> with password .... for compelte synatx please refere msdn books



*/

--Ex 1 the following example will create loginname HariJamaica and with password MrPolo

create login HariJamaica with password = 'MrPolo'

-- Ex 2 the following example will create loginname for windows authentication.

-- the windows should be exist in the active directory.

create login [Hariere-PC\HariSqlwindows] from windows

/*

Now we know how to create accounts for sqlserver using either sqlserver authetication/windows



let's talk about Credentials in sqlserver.



Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server.

This is primarily used to execute code in Assemblies with EXTERNAL_ACCESS permission set.

Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.



Technically A Credential contains authetication information to connect resource outside of sqlsever and it contains information

about windows userid and password.



A single credential can be mapped to multiple sqlserver logins and a sqlserver login cannot mapped to multiple credentials.

*/

-- ex

/*

the following example will create credential HariCred2 using my windows

authentication doman\userid<Hariere-PC\Hariere'> with password lathe

*/

create credential HariCred2 with identity = 'Hariere-PC\Hariere', secret = 'lathe'

/*

the following example wiil create a sqlserver account and map to credentials we created above

*/

create login HariSql with password = 'test', credential = HariCred2