sql database backup is another copy of database, DB Backup is technique to copy any organizations database for restore purpose for any failure incident.
- it is necessary to backup your database in any way if you have backup then you can restore your backup in case of any emergency or failure suppose you have a database server and your live database is in that database server and your database server get crashed or server is not able to work properly, that time if you have a database backup copy then you can restore it in another server, and keep your applications on going without any interruption.
- some time you are working on your database and by mistakenly some changes are made and your application stop working that time if you have a database backup then you can restore it and start your application. make sure to take backup before release deployment or any changes in the database or application. you can schedule the auto backup daily, monthly ,yearly for any frequency that you want. We will se in next post how to schedule database bkp differently in My SQL, MS SQL server ,oracle etc.
- to restore database you must have backup file which database you want to restore in my sql you can backup your database with .sql extension or you can import it as csv or you can backup your database in sql scripts lets start how you can backup your database in My SQL and MS SQL server
there are many ways to backup the database in differently as per database, i.e MY SQL , MS SQL Server,Oracle
how to backup my sql database
My SQL Database can be backed using different ways which are as follows
backup my sql database using cmd
to backup my sql db by using command prompt follow below steps
step 1: open CMD and go to the path where you have installed.
step 2: Enter below my sql dump script.
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -proot --databases Test> d:/newdatabasename.sqlIn this script, enter your Username after U password after P and after databases enter the database_name that you want to back up. The databases and then enter the drive or folder path where you want to back up the database and enter the extension by entering the database_name.sql. Here both username and password are root for my sql.
step3: change your database name and path where you want to backup your database.
step4: your backup with .sql extension will get successful on your entered location.
Backup database in phpMyAdmin
To backup my sql db by using php myadmin follow below steps:
Step 1.To backup my sql db in php myadmin login to php myadmin panel
step2. in right side click on database which you want to backup
step3. after selecting database then click on Export option in top header
Step4. after click on export option choose backup format here you can select multiple backup formats like php, csv,sql,json, xml, php array etc
Step5. once you select backup format then click on export option your backup will download in your download folder
db backup in sql server
database backup in sql server
To backup database in MS Sql Server you can backup your database in two ways 1. backup database manually in SSMS( Sql Server management studio) 2. Backup Database using sql script
To backup SQL SERVER DB manually in SQL server management studio
step1. login to SSM and enter username and password to connect your SQL Instance.
step2. expand databases and right click on database which you want to backup the database.
step3. after right-clicking on database you will see task option Select that task option and click on Backup option.
step4. Here you will see three options: general, media option, backup option.
Make Sure to Do below things
- The first option is general, in this option you have to guide the location where you want to store your backup file. And the general option has a backup type option, in which the option of what type of backup you want to take is full backup, backup in the offices,
- And you also get a check box for translational log backup as well as copy only backup in the general option, so you can set out from the general option what type of backup you want to do.
- The media option gives you the option to override or append a backup if you have a backup file to your backup location, you can override or append backup.
- And go to the backup option and go to the set backup compression option and select compress backup so that your backup size will be reduced and the backup will be completed.
After all selections go to the general option and click on OK Button to database backup.
Backup MSSQL Database using SQL script
BACKUP DATABASE [TEST] TO DISK = N'D:\TEST.bak' WITH NOFORMAT, NOINIT,
NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO;Here your TEST database will be backed up in path D:\ with TEST.bak file.
1 thought on “SQL database backup”