How to backup and restore database in SQL Server

If you want to backup and restore one database in SQL Server.

1. Create a shared folder, and add everyone with read/write right.


2. Backup your database.

You can use the following script :

backup database DatabaseName to disk ='\\SharedFolder\backup_database\DatabaseBackup.bak' with INIT, stats=10


3. Restore your database

You have two methods to restore the database.

  • Use command line

If your database exists, you can drop it.

DROP DATABASE DatabaseName

Recreate it:

CREATE DATABASE DatabaseName

Find the logical names for the data files and log files of the database backup.

restore filelistonly from disk='\\SharedFolder\backup_database\DatabaseBackup.bak'

Then you can restore your database with the following information.

RESTORE DATABASE DFM_INT_FI
  FROM DISK = '\\SharedFolder\backup_database\DataBaseBackup.bak'
  WITH REPLACE,
  MOVE 'DB_PRIMARY1' TO 'c:\temp\DB.mdf',
  MOVE 'DB_PRIMARY2' TO 'c:\temp\DB2ndf',
  MOVE 'DB_log' TO 'c:\temp\DB_log.ldf';
  • Use SSMS

Right Click DataBases, and then click “Restore database…”

Choose “Device”, and then add the backup file

And then click OK, SSMS will restore the database itself.

Comments