Share via

MSSQL command on Linux

  • Article

    Won Gold award in  https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.png Jully 2017

 

Suggested Reading

SQL Server Linux Connect and Query SQL

Following some example which helps in your daily activity

Login

Super user logon Sqlcmd is part of the SQL Server command-line tools, which are not installed automatically with SQL Server on Linux.

1. Login in mssql

sqlcmd -S 182.167.12.154 -U sa Password:      --> Enter password

2. Version of MSSQL

1> select @@version

2> go Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64) Apr 13 2017 11:44:40 `` ``Copyright (C) 2017 Microsoft Corporation. All rights reserved.   Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

3. Database creation

CREATE DATABASE [AdventureWorks] ON  PRIMARY

 ``( NAME = N'AdventureWorks_Data', FILENAME = N'/var/opt/mssql/data/AdventureWorks_Data.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )   ``LOG ON `` ``( NAME = N'AdventureWorks_Log', FILENAME = N'/var/opt/mssql/data/AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )

4. Database backup

1> backup database  [AdventureWorks] to disk ='/var/opt/mssql/data/AdventureWorks.bak' with compression

2> go

Note: Make sure that you have full permission on ``/var/opt/mssql/data/.

5. Drop database

DROP DATABASE [AdventureWorks]

Note:There no user connected to database,you can kill connection.

6. Kill SPID

  kill 61 you can kill spid.

7. Restore database

1>  restore database [ADv_test] from disk= '/var/opt/mssql/data/AdventureWorks.bak'

2> go Processed 320 pages for database 'ADv_test', file 'AdventureWorks_Data' on file 1. Processed 3 pages for database 'ADv_test', file 'AdventureWorks_Log' on file 1. RESTORE DATABASE successfully processed 323 pages in 1.119 seconds (2.255 MB/sec).

Note: Make sure that you have full permision on ``var/opt/mssql/data/

8. Login creation

USE [master]

GO CREATE LOGIN [testonly] WITH PASSWORD=N'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO

9. Sysadmin role assign to login.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [testonly]

GO

10. Create table

CREATE TABLE product(id INT, name NVARCHAR(50), quantity INT);
GO

11. Insert  values into the table

INSERT INTO product VALUES (1,'laptop',150);

12. Select from the table

select * from product   --- select all column from product
GO

13. use database use database [ADv_test]  GO

14. SA password change

sp_password NULL, 'Mssql@12345', 'sa'

15. Check space used by the table

sp_spaceused​ product

16. Check open transaction

#dbcc opentran

17. Check spid contain

#dbcc inputbuffer(233)

Reference

Suggested reading