SQL – TecAdmin

How To Restore SQL Server Database

Rahul — Tue, 22 Jun 2021 15:27:16 +0000

Microsoft SQL Server (MSSQL) is a relational database management system used on Windows servers. The latest versions are also available for the Linux platform. It’s a good practice to backup databases regularly, especially in production environments. So in case of any failure, we can restore a database from backup files. The SQL server provides options to back up and restore the full database or transactions logs.

In this how-to guide, we will learn to restore the SQL Server database using T-SQL statements and SQL Server Management Studio.

How to Restore SQL Server Database

We can restore a SQL Server database from a backup file either using the SQL queries or SQL Server Management Studio (SSMS). Use one of the below options to restore a SQL Server database from a backup file.

1. Restore SQL Database with T-SQL

Use the RESTORE DATABASE query to restore a SQL server databse from a backup file.

For example, You have a database backup file created with BACKUP commant at C:\backups\Test_db.bak . Then execute the following T-SQL statement to restore backup Test_db database from file.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak';

In most cases above command failed to restore the database and you need to go with the next query.

2. Restore SQL Database (WITH REPLACE)

Include the WITH REPLACE option to overwrite any existing data. The WITH REPLACE tells the SQL Server to discard any active contents in the transaction log and complete the restore.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak'
WITH REPLACE;

3. Restore SQL Database (WITH MOVE)

It might be the destination server database has placed files in a different location than the origin backup server. In that case, you need to define MDF and LDF file locations.

First identity the logical name of both files of the database. To find the logical name, right-click on the database, click properties and select the Files tab. Here you can find the logical names.

Use the below query with the correct logical names, file locations, and backup files.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak'
WITH REPLACE,
MOVE 'Test_db' TO 'D:\MSSQL\Data\Test_db.mdf',
MOVE 'Test_db_log' TO 'D:\MSSQL\Log\Test_db_log.ldf';

4. Restore SQL Server Database Using SSMS

The SQL Server Management Studio (SSMS) is an awesome graphical tool for managing databases on SQL Server.

Right click on database >> Tasks >> Restore >> Database
Select Device and click on three dots (…) in front of that
Select backup file and click Ok
Go to Files tab
If the files location is differnt than source. Select checkbox “Relocate all files to folder”
Select the MDF and LDF files directory, This will update files path as well
Now, go to Options tab
Select checkbox Overwrite the existing database (WITH REPLACE)
Uncheck the box Take tail-log backup before restore
Click OK to complete database restore in SQL server

Here are the useful screenshots of the database restoration in SQL Server with SQL Server Management Studio (SSMS).

Under the General tab, selecting a database backup file to restore.

Under the files tab, If required, select the relocate check box and enter MDF and LDF folder.

In the Options tab, select the WITH replace option. Also, uncheck the tail-log checkbox.

Finally, completed the database restoration.

Conclusion

In this tutorial, you have learned to restore the database from a backup file in SQL Server.

The post How To Restore SQL Server Database appeared first on TecAdmin.

How to Use LIKE Statement in MongoDB

Rahul — Sat, 26 Sep 2020 17:49:20 +0000

MongoDB is an powerful Nosql database server. MongoDB uses JSON-like documents with optional schemas to store data.

Its always a critical task for a develop to organize data. As it plays most important role in the application performance. In Mongodb, you can use queries similar to SQL LIKE statement to fetch data.

For the examples used in this tutorial, we uses some dummy data as below. You can also create a database and execute below commands to insert dummy data.

db.colors.insert({ "id": 100, "color": "Pink"})
db.colors.insert({ "id": 101, "color": "Purple"})
db.colors.insert({ "id": 102, "color": "Black"})
db.colors.insert({ "id": 103, "color": "Blue"})

Using .find()

The Mongodb find() command is used to search documents from a collection. This function provides flexible options to search documents.

The default find() function retrieve all the documents in a collection. It also allows you to query a collection of documents, by passing a few simple parameters, and return a cursor.

A simple example of the .find() method look like below:

> db.colors.find()

{ "_id" : ObjectId("5f697e4ccc528930cde49f53"), "id" : 100, "color" : "Pink" }
{ "_id" : ObjectId("5f697e4fcc528930cde49f54"), "id" : 101, "color" : "Purple" }
{ "_id" : ObjectId("5f697e52cc528930cde49f55"), "id" : 102, "color" : "Black" }
{ "_id" : ObjectId("5f697e55cc528930cde49f56"), "id" : 103, "color" : "Blue" }

The above returns all the documents in a collection. But this is very uncommon on production requirements. You always required some filtered results from a database.

For example, fetch all documents contains “color: Pink”. Execute query like:

>  db.colors.find({color: "Pink"})

Using .find() as SQL LIKE Statement

You can use regular expression for searching documents in monogdb. This will be similar to LIKE statements in SQL queries.

Search String Anywhere – To search all document where color name have “Pink” anywhere in string. The second statement searches for all documents where color have "Bl" in there name.
```
### SQL Statement 
select * from colors where color LIKE "%Pink%"

### Mongodb Statement 
db.colors.find(color: "/Pink/")
```
Search String Start With – This will match all the string start with P characters. The carrot “^” symbol is used for start with.
```
### SQL Statement 
select * from colors where color LIKE "P%"

### Mongodb Statement 
db.colors.find(color: "/^P/")
```
Search String End With – The dollar “$” symbol is used to match string ends with specific characters. The below example matches all strings ends with “k” character.
```
### SQL Statement 
select * from colors where color LIKE "%k"

### Mongodb Statement 
db.colors.find(color: "/k$/")
```
Search String In Any Case – The default find method search with case-sensitive. You can instruct find command to match characters in any case with “i” option as used in below example.
```
### SQL Statement 
select * from colors where color LIKE BINARY "pink"

### Mongodb Statement 
db.colors.find(color: "/pink/i")
```

Conclusion

In this tutorial, you have learned to to search database similar to SQL LIKE statements in Mongodb.

The post How to Use LIKE Statement in MongoDB appeared first on TecAdmin.

MySQL case-sensitive Search with LIKE

Rahul — Fri, 11 Sep 2020 17:51:37 +0000

The LIKE statement is used for searching records with partial strings in MySQL. By default the query with LIKE matches case-insensitive recores. Means query will match both records in lowercase or uppercase.

For example, Search all records un colors table where name is start with “Gr”.

mysql> SELECT name FROM colors WHERE name LIKE 'Gr%';

You can see the above query matches records with any cases.

But, sometimes you need to select case-sensitive data only. In that case, You need to cast the values as binary.

To do this add BINARY option with like statment and view the results:

mysql>  SELECT name FROM colors WHERE name LIKE BINARY 'Gr%';

You can see the result contains only those records, which matches extactly with case-sensitive. When we use BINARY, then mysql compare data byte-by-byte. Without BINARY it compares data character-by-character.

The post MySQL case-sensitive Search with LIKE appeared first on TecAdmin.

How To Install MySQL 5.7 on Fedora 31/30

Rahul — Tue, 02 Oct 2018 08:33:03 +0000

MySQL is a relational database management system. This tutorial will help you to install MySQL 5.7 on Fedora Linux systems.

You can also follow these instructions to install MySQL 8 or phpMyAdmin on your Fedora system.

Step 1 – prerequisites

Login to your Fedora system and open a terminal. Now upgrade the current packages to the latest version by running the following command.

sudo dnf update

Step 2 – Configure Yum Repository

The MySQL team provides a Yum repository for the installation of the MySQL community server on Fedora systems. First, download the GnuPG key to your local system.

cd /etc/pki/rpm-gpg
sudo wget https://repo.mysql.com/RPM-GPG-KEY-mysql

After that create a repository file as:

vim /etc/yum.repos.d/mysql-community.repo

File: /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/fc/$releasever/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Save file and close.

Step 3 – Install MySQL Server

After that run the following command to install the mysql-community-server package on your systems. This will also install all other required dependencies.

sudo dnf install mysql-community-server

After the installation of MySQL server on Fedora system. Enable the MySQL service to start on system boot. Also, start service using the below commands:

sudo systemctl enable mysqld.service
sudo systemctl start mysqld.service

Okay, now follow the post-installation instructions.

Step 4 – MySQL Post Installation Setup

The MySQL community server has been installed on your system. The installer will also generate a random password and set it for the root user. You can find the root user password in log files.

grep 'A temporary password is generated' /var/log/mysqld.log | tail -1

2018-10-16T11:49:31.216147Z 1 [Note] A temporary password is generated for root@localhost: ,yJrusM58kW7

Now run the following command to apply security on the MySQL server. Simply execute the below command and follow the security wizard.

sudo mysql_secure_installation

Follow the onscreen instructions. Change your root account password and Press Y for all other operations to apply improved security.

Change the password for root? – Press y and change root password
Remove anonymous users? Press y
Disallow root login remotely? Press y
Remove test database and access to it? (Press y
Reload privilege tables now? Press y

Step 5 – Connect MySQL Terminal

All done. You can now connect to the MySQL server using the command line. The following command will connect MySQL running on localhost as the root user. This will prompt for root account password. You can also install LAMP stack on Fedora and configure PHPMyAdmin on fedora to access databases graphically.

mysql -u root -p

Conclusion

This tutorial helped you to install MySQL 5.7 on Fedora 31/30 and earlier version. To install MySQL on Fedora 34/33/32, the official repository provides MySQL 8.0 for installation. Visit another tutorial to install MySQL on Fedora 33/32 etc.

The post How To Install MySQL 5.7 on Fedora 31/30 appeared first on TecAdmin.

How to Install MariaDB 10.4 on CentOS/RHEL 7/6

Rahul — Mon, 02 Oct 2017 16:37:49 +0000

MariaDB 10.4 stable version has been released. It is an enhanced, drop-in replacement for MySQL. MariaDB can be an better choice for choice for database professionals looking for a robust, scalable, and reliable SQL server. MariaDB has a number of updated features over MySQL. Use below links to read features comparison between MariaDB and MySQL. This article will help you to install MariaDB 10.4 in CentOS, RHEL 7/6 and Fedora Fedora 30/29/28 systems using yum.

Step 1 – Add MariaDB Yum Repository

First add MariaDB yum repository in our system. Create a new repo file /etc/yum.repos.d/mariadb.repo in your system and add below code as per your operating system and architecture.

For CentOS/RHEL – 7

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For CentOS/RHEL – 6

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For Fedora – 30/29/28

Please change the version (red highlighted) in the below setting as per the version of Fedora you used. Click here to view all available repositories.

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/fedora30-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2 – Install MariaDB Server

Let’s use the following command to install MariaDB 10.4 in your system. This will also install other dependencies automatically.

#### For CentOS/RHEL 7/6
sudo yum install MariaDB-server MariaDB-client

#### For Fedora
sudo  dnf install MariaDB-server MariaDB-client

After installing MariaDB in your system start it’s service using the following command.

## CentOS/RHEL 7 
sudo systemctl start mysql.service

## CentOS/Redhat 6 
sudo service mysql start

Step 3 – Secure MariaDB Install

You also need to secure your MariaDB installation using passwords and do some other changes. To do this run secure installation script from the command line.

sudo /usr/bin/mysql_secure_installation

The secure installation script will ask for user input as some points, follow the installation as per below output showing, All user inputs are highlighted with red color.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] y
 ... Success!

Disallow root login remotely? [Y/n] y
 ... Success!

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...
Thanks for using MariaDB!

Step 4 – Working with MariaDB

After installing and completing the configuration, connect to a MariaDB server using the following command.

sudo mysql -u root -p

Also try to create a new database, user and assign privileges to a database.

## CREATE DATABASE
MariaDB [(none)]> CREATE DATABASE mydb;
 
## CREATE USER ACCOUNT
MariaDB [(none)]> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'secret';
 
## GRANT PERMISSIONS ON DATABASE
MariaDB [(none)]> GRANT ALL ON mydb.* TO 'dbuser'@'localhost';
 
##  RELOAD PRIVILEGES
MariaDB [(none)]> FLUSH PRIVILEGES;

You may also required install phpMyAdmin to manage MariaDB using web interface, which provides easy way to work.

The post How to Install MariaDB 10.4 on CentOS/RHEL 7/6 appeared first on TecAdmin.

How to Install PostgreSQL on Ubuntu 18.04 & 16.04 LTS

Rahul — Sat, 09 Jan 2016 04:56:33 +0000

The PostgreSQL Development Team has announced the latest version PostgreSQL 12. PostgreSQL is an open-source object-relational database system. It is one of the leading database servers used for production servers. PostgreSQL allows us to execute stored procedures in various programming languages, like C/C++, Python, Java, Perl, Ruby, and its own PL/pgSQL, which is similar to Oracle’s PL/SQL. This tutorial will help you with installing the PostgreSQL database server on your Ubuntu 18.04 LTS, Ubuntu 16.04 LTS, and 14.04 LTS systems.

Recommended => Install pgAdmin4 on Ubuntu

Step 1 – Enable PostgreSQL Apt Repository

PostgreSQL packages are also available in default Ubuntu repository. So you need to add PostgreSQL apt repository to your system suggested on official PostgreSQL website using following command.

Start with the import of the GPG key for PostgreSQL packages.

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Now add the repository to your system.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Step 2 – Install PostgreSQL on Ubuntu

Now as we have added PostgreSQL official repository in our system, First we need to update the repository list. After that install Latest PostgreSQL Server in our Ubuntu system using the following commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Multiple other dependencies will also be installed. PostgreSQL 12 is the latest available version during the last update of this tutorial.

Step 3 – Create User for PostgreSQL

By default, PostgresQL creates a user ‘postgres’ with the role ‘postgres’. It also creates a system account with the same name ‘postgres’. So to connect to Postgres server, log in to your system as user postgres and connect the database.

sudo su - postgres
psql

Now configure PostgreSQL to make is accessible by your normal users. Change your_username with your actual user already created on your Ubuntu system.

postgres-# CREATE ROLE your_username WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'your_password';
postgres-# \q

Then switch to the user account and run createdb command followed by the database name. This will create a database on PostgreSQL.

su - your_username 
createdb my_db

After that connect to the PostgreSQL server. You will be logged in and get database prompt. To list all available databases use these commands.

psql

rahul=> \list
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 my_db     | rahul    | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to the Ubuntu command prompt.

postgres-# \q

Conclusion

Your PostgreSQL installation has been completed successfully. Let’s move to install Graphical user interface for PostgreSQL like pgAdmin4 and phpPgAdmin of Ubuntu systems.

The post How to Install PostgreSQL on Ubuntu 18.04 & 16.04 LTS appeared first on TecAdmin.

How To List Databases and Tables in PostgreSQL

Rahul — Tue, 20 Jan 2015 04:24:53 +0000

While working with PostgreSQL database servers, there is much useful application available for administering databases like pgAdmin4, phpPgAdmin, and SQL Workbench. A Postgres process contains multiple databases and is stored in a separate set of files under the server’s data directory.

The Postgres server also comes with psql utility for database administration via command-line. This article will help you to how to list databases in PostgreSQL server along with all tables in a single database.

Listing Databases in PostgreSQL

First connect to PostgreSQL terminal with psql command as user “postgres”. The default installation doesn’t require any password until specified manually. You can execute sudo command as below to directly connect to psql terminal as postgres user:

sudo -u postgres psql

Once you are connected to the psql terminal, type \l to list all available databases.

postgres=# \l

Output:
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN |
 myDatabase| tecadmin  | UTF8     | en_IN   | en_IN | =CTc/tecadmin         +
           |          |          |         |       | tecadmin=CTc/tecadmin
 template0 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

You can also use \list to find similar results, but to find extended details use \l+\list+.

postgres=# \list+

With this command, you will find the additional details of the PostgreSQL database like size, tablespace, and description.

The postgres also provides an traditional SQL like statement to show all databases in Postgres. To determine the set of existing databases, fetch records from the pg_database system catalog, for example:

postgres=# SELECT datname FROM pg_database;

Output:
  datname
-----------
 postgres
 template1
 template0
 myDatabase
(4 rows)

Listing Tables in Postgres Database

To list tables of any database first you connect to that database and then view tables in that database. The first command will connect you with the database (example: myDatabase) to which you want to see tables:

postgres=# \c myDatabase

Once you’ve connected to a database, you will see the database name in the prompt. Then execute the \dt meta-command to list all the tables in current database.

myDatabase=# \dt

Output:
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | results    | table | tecadmin
 public | roles      | table | tecadmin
 public | employee   | table | tecadmin
 public | users      | table | tecadmin
(4 rows)

Conclusion

In this quick guide, you have learned to list databases in the PostgreSQL database server. Also found instructions to connect the database and list available tables in the database.

The post How To List Databases and Tables in PostgreSQL appeared first on TecAdmin.