How to restore database in sql server – Restore DB means bringing it back to its original location.in RDBMS restoring databases is process to regain backup to its original locations. Or you can restore the same copy of the database to another location if you have a database backup.
To restore Database you need a backup of your database. Without a backup, you can’t restore a database, it means you should have a copy of your database backup for restoring a database in any RDBMS.
click here to see how to backup database
In this article we see following sql restore
1.how to restore Database in My sql
2.how to restore Database in PHpMyAdmin
3.how to restore Database in SQL Server
now we will see how can we restore database in different ways.
Restore Database in MY SQL
To restore Database in MY sql there are different ways to restore database in my sql server
Restore database using CMD
To restore my sql database using command prompt follow below steps.
step1.Open CMD and Go to the path where your MY SQL Server is installed e.g.
C:\Program Files\MySQL\MySQL Server 5.7\bin
step2. type below restore script for my sql restore
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h localhost -u root -proot databsename <d: new_databasename.sql
here my username and password for my sql server both are root
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h localhost -u root -proot databsename <d: new_databasename.sqlMy sql restore script
mysql -h localhost -u root -proot databasename <d:\databasename.sqlstep3.Enter Your Username and password and set path where your backup file is located. here my backup file was in D: path
here you need to change your sql username and password.here my username and password both are same i.e. root ,
Note that my sql default user is root you can add your new user as per your requirement
step4. after restore script Click Enter button your database will restored successfully.
restore Database Using PhpMyAdmin
Fo Restrong Database in any environment you needs an backup file of database which has to be restore File may be compressed (gzip, bzip2, zip) or uncompressed.
A
maximum size to restore database in phpmyadmin is (Max: 2,048 MiB)
To restore PhpMyAdmin database follow below steps
Step1. To restore Database in PhpMyadmin login to Phpmyadmin panel by using your username and password.
Step2. click on Import Option on top of header
step3. click on Choose file to Browse your backup file from your computer. File may be compressed (gzip, bzip2, zip) or
uncompressed and compressed file’s name must end in .[format].[compression]. Example: .sql.zip
Step4. Click on Import button. Your database will be restored successfully.
restored database name will be same like your backup file name.
Restore Database in MS SQL Server
to restore database sql server
In Microsoft SQL Server you can restore your database in two different ways
- Restore Database Manually in SQL server
- Restore Database Using SQL Script in SQL server
let us see one by one database restore process from .bak file in MS Sql server
Restore Database Manually in SQL server
To restore Database in MS SQL Server connect to your SQL server instance by entering username and password or you can connect by using windows authentication mode.
When restoring a database, you will get a dependent option on whether you want to restore an all-ready database or a new database.
If you want to restore a new database, right click on databases and click on restore option. If your database is there,
then right click on the database you want to restore and click on the task button,
after clicking on the task, you will see the restore option, and after clicking on Restore, click on the database.
after select Restore Database option you will see Three different option on top right side 1. General 2. Files 3.Options
you will redirect directly by default to General Option
I. General Option
IN general Option You will see three different Options Source, Destination and Restore Plan.
Source
in Source Option You will see Device Button Click on Device Button in front of Device you will see ellipse three dotted button click on that ellipse and click on Add and browse the backup file which you want to restore.
Destination
IN Destination Option You can name your database. IN this destination option you can give database name. by default name is your backup file name.
Restore Plan
IN this Restore plan option you can see Backup sets to restore. here you will see information like backup type, component, server, position, name ,LSN Number ,Started date and time etc.
II. Files
IN File option You can decide which location you want to keep your database logs and data files.
The path of default logs and data files is the path of the sql server you installed
The by default path of Log file and data file is where you have installed your Sql Server. Example D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\databasename.bak
D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\databasename.bakIf you want to change the path of logs and data files, you can click on the relocate all files to folder check box and give the path of which folder location you want to keep the files
you will appear two different paths there
1.Data File Folder
2.Log file Folder
१. डेटा फाइल्स फोल्डर अँड २.लॉग फाइल्स फोल्डर डेटा फाईल फोल्डर मध्ये तुमची .mdf(Master data file)फाईल ठेवा आणि लॉग फाईल फोल्डर च्या पाठ ला .ldf(log data file)फाईल ठेवा
1.Data File Folder
Place your .mdf master data file file in the data files folder and
2.Log file Folder
place the .ldf (log data file) file on the back of the log file folder.
after you set path you will see file types and Restore as path below.
III. Options
IN Option you will see 1.Restore Options 2.Trail Log backup 3.Server Connections and 4.Promt
in Restore option you have three different options
1.OVERRIDE EXISTING DATABASE (WITH REPLACE) this option will replace database if you have existing database.
2.Preserver the replication Setting
3.Restrict Access to restored database
Recovery State
in the Recovery state you can change recovery mode to RESTORE WITH RECOVERY,RESTORE WITH NORECOVERY and RESTORE WITH STANDBY
after this all settings click on Ok Button Your Restore Database will start and successfully restore.
you can see restore status with timeline in upper right side corner.
Restore Database using SQL script
USE [master]
BACKUP LOG [DatabaseName] TO DISK = N'J:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\DatabaseName_LogBackup_2023-05-24_18-11-31.bak' WITH NOFORMAT, NOINIT,
NAME = N'DatabaseName_LogBackup_2023-05-24_18-11-31', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [DatabaseName] FROM DISK = N'G:\BACKUP\DatabaseName.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
GOuse above script to backup sql server database using script put your name of database instead of DatabaseName which you want to restore and locate backup file and set a path to restore database
