Backup and restore MS SQL database with SQL query

In Microsoft SQL Server Management Studio, you can backup and restore your database on graphic user interface. But, sometimes, you will see some problems with some database/backup file which isn't on your PC. Then, SQL query maybe useful for you.

• Backup database to file:

BACKUP DATABASE your_database_name
TO DISK = '\\your_pc_name\shared_folder\your_database_name.bak'

• Restore database from file:
  
  
  
  • Get old file list in your backup file:
  
  
  
  

RESTORE FILELISTONLY
FROM DISK = '\\other_pc_name\shared_folder\your_database_name.bak'

• Restore database with LogicalName from result list

RESTORE DATABASE new_database_name
FROM DISK = '\\other_pc_name\shared_folder\your_database_name.bak'
WITH MOVE 'LogicalName of data' TO 'C:\new_database_name.mdf'
,MOVE 'LogicalName of log' TO 'C:\new_database_name_log.ldf'