Summary: In this article, we will show the right way to backup and restore SQL Server Database. The ways to backup and restore a database applies to SQL Server 2019, 2017, SQL Server 2016, SQL Server 2014, and SQL Server 2012. We will cover SQL Server on-premises and focus on SQL Server for Windows. SQL Server for Linux is a little different and will not be covered here. You cannot restore a database from a corrupted or obsolete backup. In that case, you can try repairing the corrupt SQL database using Stellar Repair for MS SQL software.
Getting started
Here are few important points related to backup and restore SQL Server Database:
- Use the SQL Agent to automate backups
A good practice to back up a database is to do it automatically. We will usually automate the backup using the SQL Server Agent.
The SQL Server Agent is available in most of the SQL Server editions (Enterprise, Standard, etc. ) except in the Express edition:
- Try to back up when there are not many users online
The best time to backup is at night when there are not many users online. Backup consumes a lot of resources and it might be slower the performance when people is using it.
- Always test the backup
Once the backup is complete, restore your database with another name to verify that the backup is working fine. This practice will help you restore the database from the backup without any issue.
- If possible, try to automate restoration also
It is possible to automate the backup and restoration using the SQL Server Agent. You can use one step to backup and on success, you can try a restoration. Use another name of the database to restore in order to avoid problems with the database online. In general, use another machine to test the restoration if possible.
- Combine your backups types
Differential backups can save you a lot of space instead of many full backups. Especially, if not many changes were done in the database.
Also, you can backup per files or filegroups instead of full backups.
- Always keep your backups in a safe place
Do not ever store your backups in the same hard drive or server than the data files of your current database. Always keep the backups in another server and if possible, make sure that the other server is in another city, office or country. You never know if a natural disaster will occur. If possible try to store the backups in a safe place.
- Compress your backup
If possible, use the compress option to compress your backup. Not all the SQL Server editions support this feature, but if it supports, use it. It will save a lot of space:
- Try to encrypt your backups
In order to have security, you can use Transparent Data Encryption to secure your backups and store your data. This feature will avoid someone to get your data from your database.
Software Approach
- Use Stellar Repair for MS SQL to repair the database if it is corrupted.
If the database is corrupted and you cannot back up it, you can always use Stellar Repair for MS SQL. This software allows repairing corrupt databases. Once repaired, you can successfully create a backup.
Microsoft Most Valuable Professional (MVP) Review:
Conclusion
In this article, we learned the best practices to back up and restore SQL databases. We learned how to backup, where to backup, increase security. If you have more questions, please contact us and we will gladly help you on this topic.
About The Author
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
We are trying to restore SQL database but it stuck in restoring state. We are using SharePoint Server. Is there any way to stop the restoring state and execute the commands again?
You can restore the database again, with the use of WITH RECOVERY command. You may cause loss of data if you are not careful.
I have taken backup of the database and then tried to restore it. But SQL Server database got stuck in restoring state. In this stage, I got following errors:
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How to fix these errors & restore the SQL server database 2014.
Try to use the restore database wizard in SSMS (SQL Server Management Studio). Through this, you can select the overwrite option and WITH recovery option.
While Restoring SQL database, I have received an error message:
‘Error 3154 – Restore SQL database is terminating abnormally”
“Msg 3154, Level 16, State 4, Line 1”
How to fix SQL Database Error 3154?
When backup gets corrupted then you can face SQL database error 3154. So, to fix this error by executing the T-SQL command. If the error still not resolved then adopt full proof method Stellar Repair for MS SQL tool.
Thank you for providing such a useful information.
Definitely, I will use this technique to take backup of SQL Server database.
Bookmark this page otherwise, you will miss any important point during backup of SQL Database. If you have any question, we will gladly help you with this topic.
But, I recommend trying the above methods under the observation of experts.
I was totally stuck in an unforeseen situation because SQL database corrupted. I was completely hopeless. But your database repair tool saved me from this unexpected situation.
Your software is a miracle for our small team.