How to take back up and restore SQL server database?
In this article I have explained about how to take up of SQL Server database and restore backup database. In this example I have used SQL Server management studio for access SQL server database. This back up file is used to restore it another server or same server in future.
How to take Back up of SQL Server database?
Step 1:
Connect with your SQL Server through windows authentication or SQL Server authentication. If you remotely connected with Server PC then you must login with SQL Server authentication.
Step 2:
Select your database in the object explorer for take back up, and right click of that database select
Tasks --> Back Up for taking back up.
Step 3:
After click that option new pop up window is appear you can check the database name correct or not in that source section of pop up window, check options are same like below screen.
Step 4:
Now click the "Add" button in the destination section of that pop up window, now new small pop up window is appeared and ask location for save back up file.
Step 5:
Choose path in that window and click "Ok" button, now check that your selected path is stored in destination section of pop up window.
Step 6:
After that finally click "Ok" button for save back up file in your selected path.
How to restore Back up file in SQL Server Database?
Step 1:
Connect with your SQL Server through windows authentication or SQL Server authentication. If you remotely connected with Server PC then you must login with SQL Server authentication.
Step 2:
Create new database or select your existing database in the object explorer for restore back up data, and right click of that database select Tasks --> Restore --> Database options for restore back up. New pop up window appear and then select "From device" option in that pop up window.
Then choose location option (available of right side "From device" enabled textbox) in that pop up window, now new small popup window 2 appear. 
Step 3:
Click "Add" button in the second small pop up window and select your back up location path Click "OK" button now check the first pop window your selected back up file path is displayed then click first column "Restore" check box in the pop up window.
Step 4:
Finally click "OK" button in the first pop up window. Now check your restored database tables. The all back up data is restored in that database.
Error Detail:
They are two errors are come first time when you take backup/Restore in the local System SQL Server.
1) Cannot open backup device Operation system error 5(Access is denied)
2) The backup set holds a backup of a database other than the exisiting "xyz" database
Error Solutions:
1) Cannot open backup device Operation system error 5(Access is denied)
This error comes only if you choose database backup path outside of "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup". Go to Control Panel --> Administrative tools --> Services double click that Services shortcut then choose SQL Server services and right click that SQL Server services go to properties and choose second "Log on" tab check option as "Local System Account". Then Stop and Start SQL Service for reflect that changes. After that take database back up now that error not come.
2) The backup set holds a backup of a database other than the exisiting "xyz" database. Whenever you can take backup a database and restore it on same server with another newly created database then you got this error. 
Reason for this error is already same name database log file available in the same server so you can change that log file name.
After add the backup file path in to restore pop up window then change log file name like below
Go to "Options" (Left side second option of pop up window)
Finally click "OK" button for restore successfully.
Conclusion:
I hope this article is help to you for taking back up for a SQL Server database.