When restoring a database will need exclusive access to the database, which means no other user connections can be using the database.
The RESTORE DATABASE option can be done using either T-SQL or using SQL Server Management Studio.
Restore a full backup
This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup. The original location can be checked by using RESTORE FILELISTONLY.
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' GO
Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY GO
Restore a differential backup
To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF' GO
Restore using a backup file that has multiple backups
Let’s say we use the same backup file, AdventureWorks.BAK, to write our full backup and our differential backup. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let’s say that the restore headeronly tells us that in position 1 we have a full backup and in position 2 we have a differential backup. The restore commands would be.
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1 GO RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2 GO
Restore sql command Centos
MySQL Backup and Restore from Command Line
- You have access to a Unix-like terminal
- You have the password or MySQL’s root user, or another user’s password with access to the database
Backup of a single database
Backup a single database to a plain text file, containing the sql commands to restore the tables and their data
mysqldump -u [user] -p [database_name] > [filename].sql
Backup a single database to a gzipped version of the sql file
mysqldump -u [user] -p [database_name] | gzip > [file_name].sql.gz
Backup a single database to a bz2 compressed sql file.
mysqldump -u [user] -p [database_name] | bzip2 > [file_name].sql.bz2
You can usually upload gz, and bz2 files directly to a database using PHPMyAdmin, so is a good idea to have the outpud compressed.
Backup more than one database
You can backup more than one database at the same time.
mysqldump -u [user] -p –databases [database_name_1] [database_name_2] [database_name_n] > [filename].sql
You can use the same options as with the single database to compress the mysqldump command output.
Backup of all databases
mysqldump -u [user] -p –all-databases > [file_name].sql
This will dump all databases to a single file, and you can use that file to restore all databases at once. This is very useful to move your databases from one server to another.
Using the dump file, it is possible to restore the database with all its tables to a new MySQL server.
Create the database
mysql -u [user] -p
The [user]field in this case will usually be root. At the mysql> root.
create database [database_name];
Create a user for that database, it is actually not needed, but it is a good security measure.
grant all privileges on [database_name].* to [new_user]@[hostname] identified by [new_user_password]; exit;
Once again on the Linux command line prompt.
Restore database dump file
mysql -u [new_user] -p [database_name] < [file_name].sql
If the file was compressed, uncompressed it first.
gunzip [file_name].sql.gz bunzip2 [file_name].sql.bz2