Sql server


select * from sys.databases

to check database present in the database.

select * from sys.databases

to check user name present in the databases.
select * from sys.server_principals;


how to check tables of particutar database;

select * from INFORMATION_SCHEMA.TABLES;


How to take backup of database in sql server ?


Permissions Required to Take Database Backup in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

Error Message

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'DatabaseName'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO


Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role can take a full backup of database using either:-

• Using SQL Server Management Studio
• Using TSQL scripts

Let us take a look at both the above mentioned options to perform a full backup of a database in SQL Server.



Take Full Backup of a Database Using SQL Server Management Studio

1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.

2. Expand Databases node and then right click the database which you want to take a full backup and point to Tasks, and then click Back Up… option as shown in the below snippet to open up Back Up Database dialog box.