This article will walk through the process of installing MySQL 8 on Amazon Linux 2. When you’re finished, you’ll have a fully-functioning MySQL database that you can use with either the AWS Console or your own application. You can also use Amazon RDS to configure other databases besides MySQL.

How to Install MySQL 8 on Amazon Linux

The MySQL official team provides the RPM packages for the installation of Amazon Linux systems. Follow the below steps one by one to complete the MySQL installation.

Configure Yum Repository

Most of the packages required the dependencies that are available in other third-party repositories. Use the following command to configure the EPEL repository that is required for package installation.

sudo amazon-linux-extras install epel -y 

Then configure the MySQL repository by installing the package provided by the MySQL official site.

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm 

Install MySQL Server

You can successfully configure the repositories, your system is ready for MySQL installation. Execute the below-mentioned command to install MySQL 8 community server on Amazon Linux.

sudo yum install mysql-community-server 

Press ‘y’ for the confirmation prompted during the installation.



Activate and Start MySQL Service

Once the installation is successfully finished. The default MySQL service will be stopped and in an inactive state. First, use the following commands to activate the service to auto-start on system startup, then start it manually for the first time.

systemctl active mysqld 
systemctl start mysqld 

Then, use the following command to view the MySQL service status. It should be active and running.

systemctl status mysqld 



Find initial root password

During the installation of packages, an initial password is configured for the MySQL root account. You can find this password from the MySQL log file.

cat /var/log/mysql.log | grep "A temporary password" 

You will see the output below that includes the default root password.



This password will be required in the next step.

MySQL Post Installation Setup

A post-installation script is provided by the MySQL packages. That is helpful for configuring MySQL once after the installation. This helps us to configure a strong root account password, remote anonymous users, disallow root access remotely and remove the test database.

Execute the following command from the terminal:

sudo mysql_secure_installation 

Enter the root password found in the above step, then set a new password for the MySQL root account. Next, follow the onscreen instructions and Press Y for all other operations to apply improved security.

• Enter password for user root: [Enter current root password]
• New password: [Enter a new root password]
• Re-enter new password: [Re-Enter the new root password]
• Estimated strength of the password: 100
  Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
• Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
• Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
• Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
• Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
• All done!

Connect to MySQL

Your MySQL server is ready to use now. From the terminal, you can run the below command to connect to the MySQL command line interface. It will prompt for the root account password. On successful authentication, you will get the MySQL prompt.

mysql -u root -p 

Enter the MySQL root user password:

Output:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Here you can create databases, tables, users, and all the required things by using the structured query language statements.

Next Steps for Maintaining MySQL

As you’re setting up your new database, it’s a good idea to put some thought into how you will maintain the database in the long term. This guide focuses on setting up a new database, but you should also consider ways to make your database more automated and easier to manage. One simple way to do this is to automate the process of backing up your database. This will allow you to keep a copy of your data in case something goes wrong and you need to restore it from a previous point in time. This can be done with the help of some simple scripts that call the MySQL database and write the data to a different location.

We already have created a simple database backup script and one advance MySQL database backup script for the purpose. You can use these scripts to quickly configure the database backups.

Final Words

There are many reasons why you might want to run your database on Amazon’s cloud. Some common ones have cost, ease of setup and maintenance, and the ability to scale up or down as needed. Running your database on Amazon Linux has a few advantages over using a different Linux distribution. Amazon has thoroughly tested its distribution and it is optimized for running on its cloud infrastructure. When you’re setting up a new database, it’s important to choose a solution that meets your needs and can grow with your business.

This guide focuses on installing MySQL on Amazon Linux, which is one of the easiest and most cost-effective ways to get a new database up and running.

The post How To Install MySQL 8 on Amazon Linux 2 appeared first on TecAdmin.

Let’s see the problem and solution to the issue faced recently:

The Problem:

During the migration of a WordPress application, I faced the following error with the restoration of the MySQL database. The collation id may differ based on the MySQL version.

Error message:

Error 1273 (HY000) at line 36 Unknown collation: 'utf8mb4_unicode_520_ci'

Here you go with a solution.

The Solution:

After searching for the error, I found that the MySQL server running on the other server is an older version than the source. So we find out that the destination server doesn’t support the ‘utf8mb4_unicode_520_ci’ collation.

To resolve this issue, I did a little change in the backup file. Edit the database backup file in text editor and replace all occurrences of “utf8mb4_unicode_520_ci” with “utf8mb4_general_ci“. Also, if you found “CHARSET=utf8mb4“, replace this with “CHARSET=utf8“.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save the backup file and restore the database.

The Linux system users can use the sed command to replace text in files directly.

sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/g' backup.sql  
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  

That’s it, Now the database is successfully restored without any errors!

Hopefully, this is solution helped you to resolve “Unknown collation: ‘utf8mb4_unicode_520_ci’” issue with MySQL databases.

The post (Resolved) Unknown collation: utf8mb4_unicode_520_ci appeared first on TecAdmin.

This tutorial will guide you with the installation of the MariaDB server on the Ubuntu 22.04 Linux system.

1. Configure Repository

The MariaDB packages are available in default Ubuntu repositories. Also, MariaDB provides an official repository to install the latest version on Ubuntu systems.

In order to configure MariaDB Apt repository, open a terminal with a sudo privileged account and run the following commands.

sudo apt install software-properties-common dirmngr apt-transport-https 
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc' 
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://atl.mirrors.knownhost.com/mariadb/repo/10.8/ubuntu jammy main' 

The above commands will create a repository file under /etc/apt/sources.list.d directory.

2. Installing MariaDB on Ubuntu

Next, execute the following commands to install the MariaDB server and client on your Ubuntu system.

sudo apt update 
sudo apt install mariadb-server 

Press ‘y’ and hit Enter, when prompted for the confirmation.

Once the installation is finished, execute the mysql_secure_installation script to secure MariaDB server and set a password for root account.

sudo sudo mysql_secure_installation 

Follow the on-screen instructions to complete the security wizard.

3. Manage MariaDB Service

MariaDB creates a systemd configuration file to manage its service. Use the following commands to stop, start, or restart the MariaDB service.

sudo systemctl start mariadb 
sudo systemctl stop mariadb 

To view the current status of MariaDB service, type:

sudo systemctl status mariadb 

4. Connect to MariaDB

You can get a MariaDB shell to create users, databases, and other related activities. To connect shell, type:

mysql -u root -p 

Type the MariaDB root user password you set with the mysql_secure_installation command.

Output:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.8.3-MariaDB-2ubuntu1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Conclusion

This tutorial helped you to install and configure the MariaDB server on the Ubuntu system. Now you can create databases for your awesome applications. We strongly recommended creating a separate user account for the databases.

The post How to Install MariaDB on Ubuntu 22.04 appeared first on TecAdmin.

This tutorial will help you to install the MySQL server on Ubuntu 22.04 Jammy Jellyfish Linux systems.

Prerequisities

You must have a running Ubuntu 20.04 Linux system with sudo privileges user access.

Step 1 – Installing MySQL on Ubuntu 22.04

The default Ubuntu repositories contain MySQL 8.0. Which can be installed directly using the package manager without adding third-party PPA.

To install the available MySQL server version, execute the following command.

sudo apt-get install mysql-server 

Press ‘y’ for any confirmation asked by the installer.

Once the installation is finished, you can secure the MySQL server by executing the following command.

sudo mysql_secure_installation 

You will go through a wizard of questions to secure the MySQL server. Follow the onscreen instructions below:

1. Press ‘y’ to enable validate password plugin. This will allow you to set a strict password policy for user accounts.

   VALIDATE PASSWORD COMPONENT can be used to test passwords
   and improve security. It checks the strength of password
   and allows the users to set only those passwords which are
   secure enough. Would you like to setup VALIDATE PASSWORD component?
   
   Press y|Y for Yes, any other key for No: y
   

2. Chose the password complexity level. Read the all 3 options and choose one:

   LOW    Length >= 8
   MEDIUM Length >= 8, numeric, mixed case, and special characters
   STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
   
   Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
   

3. Enter a new password and re-enter it. Make sure it matches the complexity level as described above.

   New password: *************
   Re-enter new password: *************
   

4. Press ‘y’ to continue with provided password.

   Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
   

5. Remove default anonymous users from MySQL server:

   Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
   

6. Disable root login from remote systems

   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
   

7. Remove test database form MySQL created by default during installation.

   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
   

8. Reload all privileges to apply above changes immediately.

   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
   

You have secured the MySQL server in the LAMP stack on Ubuntu 22.04 Linux system.

Step 2 – Connect to MySQL Server

Remember that the above password set for the root accounts is used for remote users only. To log in from the same system, just type mysql on terminal.

sudo mysql 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28-0ubuntu4 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Step 3 – Creating Database and Users

Here is few example queries to create database and users in MySQL server.

• Create a database named ‘mydb’.

  CREATE DATABASE mydb; 
  

• Next, create a user named ‘myuser’ accessible from ‘localhost’ only.

  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'secure_password_'; 
  

• Grant permissions on database to user.

  GRANT ALL ON mydb.* to 'myuser'@'localhost'; 
  

• Apply the permission changes at runtime.

  FLUSH PRIVILEGES; 
  

Step 4 – Manage MySQL Service

• To check the database server status.

  sudo systemctl status mysql 
  

• Use below command to start MySQL server.

  sudo systemctl start mysql 
  

• To stop MySQL server:

  sudo systemctl stop mysql 
  

• Restart MySQL database server, type:

  sudo systemctl restart mysql 
  

Step 5 – Uninstall (Remove) MySQL Server

If you no longer need to use the MySQL server, uninstall it from your server.

To remove MySQL server type:

sudo apt purge mysql-server-* 

To completely uninstall MySQL, remove the following folders as well.

rm -rf /etc/mysql 
rm -rf /var/lib/mysql 

Conclusion

This tutorial helped you to install the MySQL server on Ubuntu 20.04 LTS Linux system. Also includes instructions to secure the database server and uninstall it.

The post How To Install MySQL Server on Ubuntu 22.04 appeared first on TecAdmin.

MySQL is currently not available for Debian 11 Bullseye, so MariaDB is a perfect choice. This article is focusing on how to install MariaDB, an alternative to MySQL on Debian 11.

Install MariaDB on Debian 11

The MariaDB packages are available under the official repositories. You can directly install it without adding an extra repo to your system. For this tutorial, we will install MariaDB on Debian 11 system via default repositories.

Firstly, update the packages list using:

sudo apt update 

Now, to install MariaDB, execute the below-mentioned command:

sudo apt install mariadb-server 

Configure MariaDB on Debian

To configure MariaDB properly we need to run a security script using the below-mentioned command:

sudo mysql_secure_installation 

After running the above command, you will be prompted with various options:

The options are self-explanatory, for the first two options choose “n” and for the next sequence of options press “y” for yes.

Create Privileges User with Authentication

For security purposes, MariaDB uses a unix_socket plugin to authenticate the root user. This may cause complications therefore, it is recommended to set a new user with password-based access. And to create a new user login to MariaDB using:

sudo mysql  

Now create a new user with a password in the MariaDB server.

CREATE USER 'admin'@'localhost' IDENTIFIED BY '_pa$$w0rd_'; 

Make sure to change admin with your username and _pas$$w0rd_ with a new secure password.

Next, grant permissions on all databases to a newly created account. Here the GRANT OPTION allow a user to create other users and assign them permissions.

GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;  

Apply the new changes, execute:

FLUSH PRIVILEGES;  

And to quit typing “exit”.

EXIT 

The SQL statements are case insensitive, So you can write them in any case.

Connect MariaDB Server

One can manage MariaDB service using the Systemd. To test the status of MariaDB use the following command:

sudo systemctl status mariadb 

If for some reasons MariaDB is not running then use the below-mentioned command to start it:

sudo systemctl start mariadb 

For one more check you can try to connect to the database using:

sudo mysqladmin version 

Output 
mysqladmin  Ver 9.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.5.11-MariaDB-1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /run/mysqld/mysqld.sock
Uptime:                 3 hours 45 min 24 sec

Threads: 1  Questions: 497  Slow queries: 0  Opens: 171  Open tables: 28  Queries per second avg: 0.036

Next, connect to the MySQL shell by using the credentials created in the above step.

mysql -u admin -p 

The output of the above command asks for the password; use the password you set in the above steps. On successful authentication, you will get the MariaDB shell as below:

Output 
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 60
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Conclusion

MariaDB is an open-source alternative to MySQL in the latest version of Debian. This write-up is a guide to install MariaDB on Debian 11 Bullseye. We learned how to install and configure MariaDB on Debian 11. We also created a separate user to manage the database with password access. Finally, we also discussed utilities to test the MariaDB status.

The post How To Install MariaDB on Debian 11 appeared first on TecAdmin.

This tutorial will help you to install Redis server on Debian 11 (Bullseye) Linux system.

Step 1: Updating System Packages

It’s a good practice to keep packages up to date on your system. You should always update the before beginning any major installations. Issue the command below:

sudo apt update 
sudo apt upgrade 

Step 2: Installing Redis on Debian 11

Redis 6.0 packages are available under the default Bullseye repositories. You can quickly install Redis by using the apt package manager on your Debian Linux system.

sudo apt install redis-server 

Once the installation is finished successfully, check the Redis service status by the below-mentioned command.

sudo systemctl status redis.service 

Step 3: Configuring Redis

You can use Redis with the default settings from the local system. But in case you need to customize the Redis server like allowing access from remote hosts, changing the default port, or increasing the memory allocation.

Edit the Redis configuration file in a text editor:

sudo nano /etc/redis/redis.conf 

Now, make the required changes to the Redis server. Below are some quick uses changes in the Redis server.

• Change Redis Port: You can run your Redis server to a non-standard port. This is good practice for security purposes. Search for the below section and update the port under port 6379.

  # Accept connections on the specified port, default is 6379 (IANA #815344).
  # If port 0 is specified Redis will not listen on a TCP socket.
  port 6379

• Allow Remote Connection: Search for bind 127.0.0.1 ::1 line and comment it by adding “#” at start of line.

  # IF YOU ARE SURE YOU WANT YOUR INSTANCE TO LISTEN TO ALL THE INTERFACES
  # JUST COMMENT OUT THE FOLLOWING LINE.
  # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  # bind 127.0.0.1 ::1

• Change Memory Allocation: Update the following values in the Redis configuration file according to your requirement. You can increase the max memory limit as per available memory on your server.

  maxmemory 256mb
  maxmemory-policy allkeys-lru

After doing the required changes, save the file. Then restart the Redis service to apply changes.

sudo systemctl restar redis.service 

Step 4: Connect to Redis

Type redis-cli on the command line to connect to the Redis server.

redis-cli 

You will get the Redis server prompt as below. Now type “ping” on the Redis command prompt. On successful connection with the Redis server, you will get PONG as a result.

 ping 
PONG

Conclusion

This tutorial helps you with the installation of the Redis server on the Debian 11 Bullseye Linux system.
You can find more details about the `redis-cli` command line tool from its official documentation.

The post How to Install Redis on Debian 11 Linux appeared first on TecAdmin.

As of today, PostgreSQL 13.3 is the latest stable version available for installation. PostgreSQL version 9.5 and lower versions are no more supported by the team.

In this tutorial, you will learn, how to check the PostgreSQL version on your system.

How to Find PostgreSQL Version using Command line

Use one of the following methods to check the PostgreSQL version via the command line.

1. Check Version with Login

   Once you login to the PostgreSQL server via terminal, The post login screen displays the PostgreSQL version you have connected to.

   psql 
   

   Output:
   psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
   Type "help" for help.
   

The above output displays, that you are running version 13.3 of PostgreSQL server.

2. Check Version with version() Function

   You can also execute version() function to find out the PostgreSQL version. This method can also help you to check PostgreSQL version in automation scripts.

   SELECT version();
   

   Output:
                                            version
   ------------------------------------------------------------------------------------
    PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc
   (1 row)
   

3. Check Version with Parameters

   Alternatively, you can also find the PostgreSQL version by the preset parameters, and you can also use this method for automatic version checks.

   SHOW server_version;
   

   Output:
             server_version
   ----------------------------------
    13.3 (Ubuntu 13.3-1.pgdg20.04+1)
   (1 row)
   

Check the below screenshot for all commands in action to check the Postgres version on command line.

How to Find Postgres Version in pgAdmin4

pgAdmin4 is an excellent web interface for managing the PostgreSQL servers. So the pgAdmin4 users can also find the Postgres version on the web interface.

To get the version, follow:

1. Login to pgAdmin4
2. In the left sidebar, expand the Servers and select your Postgres server
3. Go to Properties tab
4. Under the General section, check for PostgreSQL version.

Conclusion

In this tutorial, you have learned 3 methods to check the PostgreSQL version via the command line. Also provided the steps to find version in pgAdmin4.

The post How to Check the PostgreSQL Version appeared first on TecAdmin.

You can follow one of the below instructions to rename a MySQL database with the help of cPanel, phpMyAdmin, or command line as per the availability. After renaming the database, remember that you need to reconfigure the permission on the new database for the users.

In this tutorial, you will find three methods to rename a MySQL database.

Method 1 – Rename MySQL Database with Command Line

As you know that there is no direct command or SQL statement available for renaming the database in the MySQL server. But you can still change the database name using backup and restore options.

1. First, take a backup of current database:

   mysqldump -u root -p old_db > old_db.sql 
   

2. Then create a new database with the desired name in the MySQL server.

   mysqladmin -u root -p create new_db 
   

3. Finally restore the backup taken above to the newly created database.

   mysql -u root -p new_db < old_db.sql 
   

You have a new database with a new name. Verify the new database and make sure that restore completely and functioning properly.

Method 2 - Rename MySQL Database with phpMyAdmin

phpMyAdmin is the most popular web application used for managing MySQL databases. It provides you an option to rename the database in the MySQL server.

1. Log in to the phpMyAdmin
2. Select database in the left sidebar.
3. Click the "Operations" tab.
4. Type a new database name in the field “Rename database to:” and click Go.

   

5. On confirmation dialog, click OK.

All done. Here phpMyAdmin will create a new database with a new name and copy all content from the old one. After that drop the old database.

Method 3 - Rename MySQL Database with cPanel

cPanel is a web-based control panel for the CentOS and Redhat Linux systems. It is popular among shared hosting providers. You can easily rename a MySQL database with the help of cPanel.

The cPanel offers the easiest way to rename a MySQL database.

1. Log in to the cPanel.
2. Go to the Databases section and click MySQL Databases.

   

3. Scroll down to this page, you will find the list of databases under the Current Databases section.
4. Click the "rename" button in front of the database to be rename.

   

5. Provide a new database name and click Proceed.

   

That's it. You have successfully renamed a MySQL database.

Conclusion

This guide helps you to understand how to rename MySQL databases. You will have to reconfigure the user permissions since the database has been renamed.

The post How to Rename MySQL Database appeared first on TecAdmin.

How to Backup SQL Server Database

We can backup the SQL Serer database either with the T-SQL statements or we can use the SSMS wizard process to take full, differential, or transactional backup of the database.

Use one of the below options to backup the SQL Server database:

1. Backup Database using T-SQL

Launch SQL Server Management Studio and connect to the database server. Now open a query window and execute the following statement to back up the database in your local drive.

BACKUP DATABSAE [Test_db] TO DISK = 'D:\backup\Test_db.bak'
Go

Here Test_db is the database name and D:\backup\Test_db.bak is the backup file name with full path.

The above screenshot shows a successful database backup in the SQL server using a T-SQL statement. The next option will help you to backup the SQL Server database with a graphical interface.

2. Backup Database using SSMS

SQL Server Management Studio provides an option to backup SQL Server databases manually. To back up a SQL server database follow the below steps.

1. Launch Management Studio and Connect to SQL Server
2. Under databases, Select your database to backup
3. Right-click on database and navigate to >> Tasks >> Backup

   

4. Under the Source option, Correct databse is selected in dropdownlist. The backup type is set to Full.
5. Under the Destination section, Remove any entry that already exists by clicking the remove button available on the right side. Then Click Add button to provide the correct backup path with filename.
6. See the below screenshot, taking a full database backup of “Test_db” at path D:\backup\Test_db.bak.

   

7. Once the backup completed successfully, you will see a message like below.

   

Conclusion

In this tutorial, you have learned two methods to backup a SQL Server database. Next, you may like our tutorial to restore SQL Server database with T-SQL and SQL Server Management Studio

The post How to Backup SQL Server Database appeared first on TecAdmin.

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.

1. Right click on database >> Tasks >> Restore >> Database
2. Select Device and click on three dots (…) in front of that
3. Select backup file and click Ok
4. Go to Files tab
5. If the files location is differnt than source. Select checkbox “Relocate all files to folder”
6. Select the MDF and LDF files directory, This will update files path as well
7. Now, go to Options tab
8. Select checkbox Overwrite the existing database (WITH REPLACE)
9. Uncheck the box Take tail-log backup before restore
10. 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.