Hi Guys, welcome back to codingofcents.com, today we will learn how to create a backup restore SQL server database.
Backup and restore are very important on the Database administrator job because we need to back up the production database so when there is something wrong in database we can restore the previous version of the database.
For this backup and restore tutorial I’ll use SQL server 2012 and here you can follow the step by step.
Open your SQL Server Management Studio then login to with your account.
- Login to SQL
First of all, we need to login to access which database you want to backup
- Create Backup
Then right-click to the database you want to backup, then click Tasks then Back Up
- Change Name When Backup
Then, for this tutorial, we will change the name of the backup file. To change the name click Remove button then click Add button.
- Change Backup Directory
After clicking the Add button, click 3 dots button to select the directory for the backup file. Then type the name for the backup database file. Please make sure you type the name of the backup database file end with .bak extension. After that, click OK and OK again.
- Change Backup Compression
Next, click the Options tab and on Compression section select Compress Backup to make size of the backup database file smaller.
- Create Backup
After that, click OK and the backup database file is ready.
- Restore Database
To restore the database, we can restore to the new database or override the existing SQL Server database. For now, we will restore the backup to the new SQL Server database.
Make sure you close all the tab and collapse all tab to prevent error, the right click on Databases then click Restore Files and Filegroups.
- Find Backup File
After that, on To database type your new database name (will created on restore). On Sour for restoring select From media and click 3 dots button then click Add. After that select where your backup database file located And click OK and OK again.
- Restore Destination
Then, on Select the backup set to restores selects the Restore checkbox.
Next, on Options of you want to restore to the existing database check the Override the existing database. Then CLick OK.
- Restore Created
After that restored file will be created
- Refresh Databases
If you did not see the new database that we have been created, right-click on Databases and click Refresh.
- Restore Complete
Finally, we can see new database created. Yeay! 😁