How to create a database in sql server with database name and path as input variables

How to create a database in sql server with database name and path as input variables

=>Below stored procedure example illustrates how to create a new database.

=> and adds database name and path as an input variable.

Create proc createTestDatabase(@databasename as varchar(120),@drivename as varchar(500))

as

Begin

Declare  @stmt as nvarchar(max);

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @databasename))

BEGIN

EXEC('DROP DATABASE ' + @databasename);

END

Set @stmt='

CREATE DATABASE ' + @databasename +  

    ' ON (NAME = N''' + @databasename + '''' +

', filename = N''' + @drivename  + '\' + @databasename + '.mdf' + '''' + ', SIZE = 1024MB, FILEGROWTH = 256MB)' +

' LOG ON (NAME = N''' + @databasename +  '_log' + '''' +

 ', FILENAME = N''' + @drivename + '\' + @databasename + '_log.ldf' + '''' + ', SIZE = 512MB, FILEGROWTH = 125MB)'

Select @stmt 

Execute (@stmt);

End

How to execute the above stored proc:

Declare @databasename as varchar(150);

Declare @drivepath as varchar(500);

Set @databasename  = 'testDatabase11';

Set @drivepath= 'D:\mcj';

Exec createTestDatabase @databasename, @drivepath;

How do you set database ONLINE and OFFLINE in sql server?

SQL Server database can be made OFFLINE by running the below SQL statement. 

If there are any critical or important transactions running then it's recommended to let them complete first and then run the below queries down.

USE [master];

GO

--Disconnect all existing sessions.

ALTER DATABASE SampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--Change database into OFFLINE mode.

ALTER DATABASE SampleDB SET OFFLINE

Now your database has moved to offline mode.

=>If incase set single user doesn't work, use the below deadlock priority high command

SET DEADLOCK_PRIORITY HIGH

GO

ALTER DATABASE SampleDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

How do we bring back our database online again?

Once we are done with updating the physical file names, the next step is to bring the database ONLINE.

Run the below SQL statement to bring the target database ONLINE. 

ALTER DATABASE SampleDB SET ONLINE

Go

ALTER DATABASE SampleDB SET MULTI_USER

Go 

SQL Server- Query to restore database with its file groups and log files

 -- > create a sample database

USE master

GO

 

CREATE DATABASE SampleDB

ON PRIMARY

  (NAME = SampleDB_file1,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_1.mdf',

          SIZE = 100MB,         

          FILEGROWTH = 10%),

 FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

  ( NAME = SampleDB_MemoryOptimized,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_MemoryOptimized')

 LOG ON

  ( NAME = SampleDB_log_file1,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_1.ldf',

          SIZE = 100MB,         

          FILEGROWTH = 10%)

GO

SQL Server Database master keys and certificates for database encryption

 

SQL Server – Database master key and Certificates

Database Master Key and Password Protected Certificates

Things to remember about master Keys

-->Each sql server instance can have only 1 service master key and each database can have only 1 database master key

-- > we cannot rename master key as we want to...

--> this will be listed under master database

Syntax to check if the master key exists or not, if Not then create master key: 

IF (select Count(*) from master.sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0

BEGIN

CREATE MASTER KEY  ENCRYPTION BY PASSWORD = 'test1234pwd'

END

Note: you need to be sysadmin or login with sa credentials to execute these queries

Once you created the master key you can view them under the security section of master database.

Why do we need master keys:

Certificates created with the CREATE CERTIFICATE statement are protected by the database master key. To be able to execute that statement, the database master key has to exist. If the database master key does not exist or if it cannot be opened, the CREATE CERTIFICATE statement will fail.

Alternatively, if you do not want the private key to be protected by the database master key, you can provide a password using the ENCRYPTION BY PASSWORD clause:

 ENCRYPTION BY PASSWORD = '**********'   WITH SUBJECT = ' SQLserverpwd112233';

SQL Server... Add column, rename column or update column data types in sql server

 

Add a new column into the table 


ALTER TABLE Table_Name

ADD column_name datatype;



Modify a table- drop column name:

ALTER TABLE table_name
DROP COLUMN column_name;

How to rename a column in SQL Server:

EXEC sp_RENAME 'TestTabl1.OldColumnName', 'NewColumnName', 'COLUMN'

Query should get successfully get executed. and also if the table is already populated
you would get a warning message like:

Caution: Changing any part of an object name could break scripts and 
stored procedures.

SQL Server : SQL scripts help - How to select all record from 1st table which is not present in 2nd table

How to select all record from 1st table which is not present in 2nd table:

Query:

 SELECT ID FROM tblEmployee WHERE ID NOT IN  (SELECT ID FROM tblPerson)

or 

SELECT name FROM table2 

WHERE NOT EXISTS 

    (SELECT * FROM table1 WHERE table1.name = table2.name)

To display all the contents of 2^nd table which are not in 1^st table without using NOT condition

Query:

1stly create 2 new sample tables and inserted some sample rows.

create table t1

( col1 int,

col2 int)

create table t2

( col1 int,

col2 int)

insert into t1 values(1,1)

insert into t1 values(2,2)

insert into t2 values(1,1)

insert into t2 values(2,2)

insert into t2 values(3,3)

insert into t2 values(4,4)

---to display table 2 entries which are not in table 1

--below bothe the queries worked for me

SELECT t2.col1

FROM t2

LEFT JOIN  t1 ON t2.col1 = t1.col1

WHERE t1.col1 IS NULL

SELECT * FROM T2

EXCEPT

SELECT a.* FROM T1 a

JOIN T2 b ON a.col1 = b.col1

=================================================================’