Moving database files using Offline and Online method

Moving database files using Offline and Online method

Create new database sales
Create database sales

Check current path
sp_helpdb sales
ex:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sales.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sales_log.ldf


Create folder and grant read/write permissions to service account
ex. C:\qadartest1

Modify the file path
use master
go

alter database Sales modify file
(name='Sales',filename='C:\qadartest1\Sales.mdf')
go
alter database Sales modify file
(name='Sales_log',filename='C:\qadartest1\Sales_log.ldf')
go

Take database offline
use master
go

alter database Sales set offline with rollback immediate

Move the files into new folder(s)
Bring online
alter database Sales set online

Check new path
sp_helpdb sales

SQL Server Backups

SQL Server Backups

Full backup
backup database AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with stats=10
restore headeronly from disk='d:\backups1\Advw.bak'
restore filelistonly from disk='d:\backups1\Advw.bak'
restore verifyonly from disk='d:\backups1\Advw.bak'

Differential backups
backup database AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with differential,stats=10
restore headeronly from disk='d:\backups1\Advw.bak'

T.Log backup
backup log AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with stats=10
restore headeronly from disk='d:\backups1\Advw.bak'

To check backup history

SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'AdventureWorks2014' 
ORDER BY database_name, backup_start_date, backup_finish_date

SQL Server Logins, Users and Permissions

SQL Server Logins, Users and Permissions

step1: Creating login
create login qader with password='Hyd@1234'
Windows login
create login [optimize\Karl] from windows
To check SID of qader
sp_helplogins qader
go

step2: Creating user for qader
use AdventureWorks2014
go
create user qader for login qader
To chec user info
sp_helpuser qader

step3: Granting permissions
grant select,insert on Person.Address to qader obj level
with grant option
grant select on schema::Sales to qader   schema
deny select on Sales.CreditCard to qader
grant backup database to qader     db level
revoke insert on Person.Address from qader
To grant column level
grant select on HumanResources.Employee(BusinessEntityID,LoginID,JobTitle) to qader

step4: To check permissions
sp_helprotect To check all permissions
go
sp_helprotect [Person.Address]
go
sp_helprotect [Person.Address],qader
go
sp_helprotect null,qader
Using views
select * from sys.database_permissions
where grantee_principal_id=(select principal_id from sys.database_principals
where name='qader')
To check the schema name
select * from sys.schemas where schema_id=9

SQL Server Error logs

SQL Server Error logs

step1: Reading current log
sp_readerrorlog

step2: Reading archie1 log
sp_readerrorlog 1

step3: Reading agents current log
sp_readerrorlog 0,2  2 indicates agent log 1 for sql log

step4: To filter errorlog for error word
sp_readerrorlog 0,1,'error'

step5: To recycle errorlog
sp_cycle_errorlog

step6: Check current log after recycling
sp_readerrorlog

step7: To recycle sql agent log
use msdb
go
sp_cycle_agent_errorlog

step8: Backup of master database
backup database master to disk='master.bak'

step9: Check the errorlog for backup details
sp_readerrorlog 0,1,'master'

step10: Backup fails..
backup database master1 to disk='master1.bak'

step11: Check errorlog
sp_readerrorlog 0,1,'master1'

step12: To customize event logging we can use trace flags
dbcc traceon(3226,-1) 3226 avoid success entries into errorlog

step13: Take backup of any database
backup database msdb to disk='msdb.bak'

step14: Check msdb backup details. Not recorded
sp_readerrorlog 0,1,'msdb'

step15: Make trace flag off
dbcc tracestatusTo enabled trace flags
dbcc traceoff(3226,-1)

Tempdb Demo

Tempdb Demo

step1: To check tempdb startup
sp_readerrorlog 0,1,'tempdb'

step2: Creating one table in MSDB
use msdb
go
create table emps1(empid int,ename varchar(40))

step3:
select * into #emps1_temp from emps1

step4: sp_help #emps1_temp --Error

step5: Checking in tempdb

use tempdb
go
sp_help #emps1_temp

step6: Take new query

step7: kill 58

step8: Check the same table
use tempdb
go
sp_help #emps1_temp --Error

MS-SQL Post Patching Steps

MS-SQL Post Patching Steps

-Verify installation from summary.txt file

-Connect to the instance and verify the patch information

select  serverproperty('productlevel')
select @@VERSION

-Restart the system

-Check that all dbs has come online
select  name,state_desc from sys.databases

Take backup of all system dbs.
--master, model, msdb, resource
backup database master to disk='master.bak'

-Verify that all dbs are consistant
dbcc checkdb(master)

-Allow the appl team/Testing team to check connectivity from appls.

-Make the instance available to the appls by enabling TCP/IP of the instance.

To check complete information:

SELECT
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductBuildType') AS ProductBuildType,
SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
SERVERPROPERTY('ProductBuild') AS ProductBuild
GO

MS-SQL Basics Commands

MS-SQL Basics Commands

Creating a database
create database study

Database Details
sp_helpdb            --Tot dbs
sp_helpdb studs      --studs db details

Creating a schema
use study
go
create schema Library1
go
select * from sys.schemas   --To disp all schemas

Creating a table:
create table emps(empid int,ename varchar(40),sal money)
sp_help       --list all objects
sp_help emps  --To disp structure of table
create table Library1.Books(bid int,bname varchar(40))

Working with data:
insert emps values(1,'Rakesh',4500),(2,'Rafi',4500)
select * from emps
update emps set sal=9000 where empid=1
dbcc log(0)   --To open the log file
dbcc log(0,3) --To check complete info
delete from emps where empid=2
truncate table emps