Oracle Database Commands and Practical Examples: MSSQL Server

Showing posts with label MSSQL Server. Show all posts

Wednesday 25 March 2020

MS SQL Fill backup and validation check

Create Backup database

--Create a test database--

Use master

Create database BackupDatabase

Create table on backup database

use BackupDatabase

--Create table

Create table Products

(ProductID int IDENTITY (1,1) Primary Key,

ProductName varchar (100),

Brand varchar (100))

--Insert data into table

insert into Products values ('Bike','Genesis')

insert into Products values ('Hat','Nike')

insert into Products values ('Shoe','Payless')

insert into Products values ('Phone','Apple')

insert into Products values ('Book','Green')

insert into Products values ('Cup','Large')

--View data

select * from Products

--Take full database backup of backupdatabase

--Verify that the database backup is valid (not that the data within is valid)

declare @backupSetId as int

select @backupSetId = position

from msdb..backupset

where database_name=N'BackupDatabase'

and backup_set_id=(select max(backup_set_id)

from msdb..backupset where database_name=N'BackupDatabase' )

if @backupSetId is null

begin

raiserror(N'Verify failed. Backup information for database ''BackupDatabase'' not found.', 16, 1)

end

RESTORE VERIFYONLY

FROM

DISK = N'c:\fullbackups\BackupDatabase.bak'

WITH FILE = @backupSetId

Use msdb

SELECT logical_name,physical_name,file_number,backup_size,file_type, *

FROM dbo.backupfile

ORDER BY 1 -- Contains one row for each data or log file that is backed up

--SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set

--SELECT * FROM dbo.backupset -- Contains a row for each backup set

--SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup

--SELECT * FROM dbo.backupmediafamily

--Extented backup script wtih options

BACKUP DATABASE [smrmindev]

TO DISK = N'C:\FullBackups\smrmindev.bak'

WITH

--NOFORMAT,

INIT, --<< Specifies that all backup sets should be overwritten

NAME = N'smrmindev-Full Database Backup',

--SKIP,

--NOREWIND,

--NOUNLOAD,

COMPRESSION, --<< Compresses the database

STATS = 10, CHECKSUM --<< Specifies that the backup operation will verify each page for checksum and torn page

--Verifies that the database backup is valid

declare @backupSetId as int

select @backupSetId = position from msdb..backupset

where database_name=N'smrmindev' and backup_set_id=(select max(backup_set_id)

from msdb..backupset

where database_name=N'smrmindev' )

if @backupSetId is null

begin

raiserror(N'Verify failed. Backup information for database ''smrmindev'' not found.', 16, 1)

end

RESTORE VERIFYONLY

FROM DISK = N'C:\FullBackups\smrmindev.bak'

WITH FILE = @backupSetId,

NOUNLOAD,

NOREWIND

Tuesday 24 March 2020

MSSQL - Deattach and attach manually by commands

sp_helpdb smrtest

--Set the database to a single user mode from multiuser mode

USE [master]

ALTER DATABASE [smrtest]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Detach the database using the sprocs

USE [master]
GO
CREATE DATABASE [smrtest1] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest_log.ldf' )
FOR ATTACH --<<use for attach to attach the sales database
GO

MSSQL - Detach and Attach database using GUI

############ ATTACH #######################

1

MSSQL - Change tempdb mdf and ldf file location

sp_helpdb tempdb

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\tempdb_mssql_8.ndf');
GO
USE master;
GO

###########################ALSO ##########################################

We cant change recovery mode from simple to full in temp db

USE [master]
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL
GO

--Sizing the data and log files and autogrowth property

USE [master]

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 20480KB )--<< pre sizing data, auto growth

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'templog', SIZE = 20480KB , FILEGROWTH = 20480KB )--<< pre sizing log, auto growth

Wednesday 11 March 2020

MSSQL SERVER database creation script with specified size and growth

USE master
GO

CREATE DATABASE [Prod]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Prod',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Prod.mdf' , --<< this must be in its own physical drive
SIZE = 5MB , --<< pre size the data file 5000 mb
FILEGROWTH = 1MB ) --<< pre size the log file 100 mb

LOG ON
( NAME = N'Prod_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Prod.ldf' , --<< this must be in its own physical drive
SIZE = 1MB , --<< pre size size of transaction log file 100 mb
FILEGROWTH = 1MB ) --<< pre size size auto growth to increase in increments of 5 mb
GO

Friday 6 March 2020

MS SQL Server create database command by setting file growth size

--Create database command by setting file growth size

CREATE DATABASE [fixed_size]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'auto_fixed_size',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\auto_fixed_size.mdf' ,

SIZE = 1024000KB , --<< initial size of data file 1000mb

FILEGROWTH = 102400KB )

LOG ON

( NAME = N'auto_fixed_size',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\auto_fixed_size.ldf' ,

SIZE = 102400KB ,

FILEGROWTH = 102400KB ) --<< growth by 100mb (PRE SIZED SO THAT THE AUTO GROWTH DOES NOT ACTIVATE)

DBCC LogInfo

MS SQL Server Create database command with auto growth

Use master
go

CREATE DATABASE [auto_growth]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'auto_growth',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\auto_growth.mdf' ,
SIZE = 3072KB , --<< initial size of data file 3mb
FILEGROWTH = 1024KB ) --<< growth by 1mg

LOG ON
( NAME = N'auto_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\auto_growth_log.ldf' ,
SIZE = 1024KB , --<< initial size of log file 1mb
FILEGROWTH = 10%) --<< growth by 10%
GO

DBCC LogInfo;

Restore Adventure works DB backup on MSSQL Server 2017 DB using GUI Method

step 1

Step 2 :

STEP 3:

STEP 4:

STEP 5 :

STEP 6:

Query to check recovery model in MSSQL Server Database

Query to check recovery model in database excluding 'master','model','tempdb','msdb'

select [name], DATABASEPROPERTYEX([name],'recovery')
from sysdatabases
where name not in ('master','model','tempdb','msdb')

Recovery model query to simple below:

--Change the recovery mode:

USE master;

-- Set recovery model to SIMPLE

ALTER DATABASE smrmindev SET RECOVERY SIMPLE;

Recovery model query to full mode below:

-- Set recovery model to FULL

--Change the recovery mode:

USE master;

-- Set recovery model to FULL

ALTER DATABASE smrmindev SET RECOVERY FULL;

-- Set recovery model to FULL

ALTER DATABASE smrtest SET RECOVERY FULL;

--- Check the status of recovery model.

select [name], DATABASEPROPERTYEX([name],'recovery')
from sysdatabases
where name not in ('master','model','tempdb','msdb')