Follow by Email

Wednesday, June 19, 2013

Backup and restore database using a sql query in MS-SQL | Example of backup and restore of database in SQL

1. For backup use this below query

BACKUP DATABASE <Database_Name> TO DISK = 'D:\back\dbname.bak'

Make sure the folder has full rights in which we are going to save the .bak file or else you can get this below error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ''. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


2. For Restoring a database use this below query 

Use MASTER 
GO
RESTORE DATABASE <Database_Name> FROM  DISK = N'D:\back\dbname.bak' WITH  FILE = 1,  KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 10
GO