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