How to restore a SQL Server backup SQL Server Restore Commands windows/linux centos

Cpanel/Whm License $3/mo Plesk License $10/mo Cloudlinux License $5/mo

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.


T-SQL

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

Assumptions

  • 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

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.

Restore

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

Related posts

How to install SIPp testing tool on Ubuntu 18.04 / Ubuntu 16.04

SXI ADMIN

Bitcoin Price Resilient as Antonopoulos, Andreessen Weigh in on Mt. Gox Debacle

SXI ADMIN

tZERO Announces Plans to Tokenize Atari Founder’s Biopic

SXI ADMIN

Grayscale Opens Ethereum Classic Vehicle to Accredited Investors

SXI ADMIN

Facebook Seeks Wallet Engineers as Blockchain Job Openings Top 30

SXI ADMIN

Widow of QuadrigaCX CEO Denies Hiding Assets from Crypto Creditors

SXI ADMIN

White Paper Published for Blockchain Privacy Tech Zk-starks

SXI ADMIN

Install ntop on Red Hat Enterprise Linux / CentOS Linux

SXI ADMIN

Bitcoin Startup Cleared of Breaching Securities Law

SXI ADMIN

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More