{"id":4760,"date":"2019-11-28T12:33:39","date_gmt":"2019-11-28T12:33:39","guid":{"rendered":"https:\/\/www.kerneldatarecovery.com\/blog\/?p=4760"},"modified":"2020-01-16T06:43:00","modified_gmt":"2020-01-16T06:43:00","slug":"restore-sql-database-from-backup-in-single-user-mode","status":"publish","type":"post","link":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/","title":{"rendered":"Restore SQL Database From Backup In Single User mode"},"content":{"rendered":"<p>SQL is a popular database management system used across the globe. Though it\u2019s a mature application, SQLs data needs to be backed up from time to time. From this backup, you can restore the database when any need arises.<\/p>\n<h4>Situations When You Have To Restore SQL Data<\/h4>\n<p>In certain situations, you need to restore the SQL database from the backups. Let\u2019s discuss them:<\/p>\n<ul>\n<li>Accidental deletion of SQL data<\/li>\n<li>Damage to SQL databases<\/li>\n<li>Data loss due to virus attacks<\/li>\n<li>A sudden interruption in electricity supply<\/li>\n<li>Data theft<\/li>\n<li>SQL database corruption<\/li>\n<li>Storage device failure<\/li>\n<li>Natural calamity<\/li>\n<\/ul>\n<p>These are a few of the popular reasons behind the data loss due to which a user needs to restore the data.<\/p>\n<h4>Ways to Get Exclusive Access to Restore SQL Database<\/h4>\n<p>Before moving to the restoring process, one should know that if you want to restore the SQL database, you need exclusive access to it. This can be done by putting the database to <strong>Single User Mode<\/strong>.<\/p>\n<p>So, let\u2019s first discuss the ways to get exclusive access;<\/p>\n<p><strong>Set database in Single User Mode<\/strong><\/p>\n<p>During maintenance actions, databases are set to Single-use Mode, so that only one user can access the database at a time (this is the case with master databases).<\/p>\n<p>Different methods can be used to set a database to Single-user Mode. For e.g.: ALTER DATABASE command can be used with different switches to put a database  in a single user mode:<\/p>\n<ul>\n<li>With Rollback Immediate<\/li>\n<li>With Rollback After (N)<\/li>\n<li>With No _Wait<\/li>\n<li><strong>With Rollback Immediate<\/strong>: Rollbacks all the current transactions, means, resets the database to the point where the transaction was started in the first place.<\/li>\n<div class=\"brd_line-blue\">ALTER DATABASE [Database name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE<\/div>\n<li><strong>With Rollback After (n)<\/strong>: Rollbacks all the open transactions after \u2018N\u2019 no. of seconds.<\/li>\n<div class=\"brd_line-blue\">ALTER DATABASE [Database name] SET SINGLE_USER WITH ROLLBACK AFTER <strong>[N]<\/strong><\/div>\n<li><strong>With No _Wait<\/strong>: This command is considered as the best in all commands as it simply waits for the transaction to complete. If the transaction doesn\u2019t complete for a specified time, then it will not work.<\/li>\n<div class=\"brd_line-blue\">ALTER DATABASE [Database name] SET SINGLE_USER WITH NO_WAIT<\/div>\n<\/ul>\n<h4>How to Restore SQL Database form backups?<\/h4>\n<p>Now, let\u2019s move to the restoring process and know how one can restore the database.<\/p>\n<p><strong>Point to Keep in Mind:<\/strong> The very first rule to keep in mind while starting the restore process is that you need to put the database in Single user mode.<\/p>\n<p><strong>Step 1-<\/strong> Put the Database in Single User Mode<\/p>\n<p>To put the database on Single User mode (With Rollback Immediate), follow the Command mention below:<\/p>\n<div class=\"brd_line-blue\">ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE<\/div>\n<p>This ROLLBACK IMMEDIATE  doesn\u2019t wait for transactions to complete. As the name suggests \u2018Rollback,\u2019 it resets the database to the point where the transaction actually started, and \u2018With Rollback Immediate.\u2019 It basically starts rolling back all the incomplete transactions immediately.<\/p>\n<p><strong>Step 2- Provide the Restore Command<\/strong><\/p>\n<p>Once you put the database to Single User Mode, you can run the Restore command, so that the restore process could take place. Enter the following command:<\/p>\n<div class=\"brd_line-blue\">RESTORE DATABASE [Database Name]<br \/>\nFROM DISK = &#8216;Backup location\\Backup file.BAK&#8217;<\/div>\n<p><strong>Step 3-<\/strong> Put the Database back to Multi-User Mode<\/p>\n<p>Once you are done with restoring the database, you can put the database to Multi-user mode. To put the database to Multi-user mode, input the following command:<\/p>\n<div class=\"brd_line-blue\">ALTER DATABASE [Database Name] SET MULTI_USER WITH ROLLBACK IMMEDIATE<\/div>\n<h4>How To Repair an SQL Database File?<\/h4>\n<p>If restoring the database is a bit complicated for you or it doesn\u2019t work, then don\u2019t be disappointed. With <a href=\"https:\/\/www.kerneldatarecovery.com\/sql-recovery.html\">SQL Recovery Tool<\/a>, you can achieve similar results faster. This is an automated method, and hence you need not have much technical knowledge to work with it. You just need to follow simple steps asked by the tool, and you are done with restoring the database in no time. To have a better idea of the tool, I am sharing a screenshot of it:<\/p>\n<p align=\"center\"><a class=\"dl\" href=\"https:\/\/www.kerneldatarecovery.com\/dl\/dl.php?id=77\" onclick=\"dataLayer.push({'event': 'TrackEvent', 'eventCategory': 'Download', 'eventAction': 'SQLRecovery', 'eventLabel': 'BlogDLid=77'});\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-1659 noshadow\" src=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2018\/06\/dl-btn-new.png\" alt=\"Download Now\" width=\"160\" height=\"49\"><\/a><\/p>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"951\" height=\"601\" src=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/Q2.jpg\" alt=\"SQL Recovery Tool\" class=\"alignnone size-full wp-image-4762\" srcset=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/Q2.jpg 951w, https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/Q2-300x190.jpg 300w, https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/Q2-768x485.jpg 768w\" sizes=\"(max-width: 951px) 100vw, 951px\" \/><\/p>\n<p>This is a screenshot of the tool where I have added the SQL database file. Next, click on the Recover button and proceed with the recovery process, ultimately resulting in Restoring the database.<\/p>\n<h4>Conclusion:<\/h4>\n<p>In this blog, we have discussed all about restoring the SQL database. We have discussed why one needs to restore the SQL database and how one can recover it manually.  Also, we have discussed about SQL Recovery Tool and explained how one could benefit from it in restoring the SQL database. We have more SQL related blogs on <a href=\"https:\/\/www.kerneldatarecovery.com\/blog\/how-to-restore-master-database-in-sql-server\/\">how to  restore master database<\/a>, or what to do when <a href=\"https:\/\/www.kerneldatarecovery.com\/blog\/sql-server-database-stuck-in-restoring-state\/\">SQL server database stuck in restoring state<\/a>, do check them. To gather more information about the tool, you can visit the tool\u2019s page on the Website.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL is a popular database management system used across the globe. Though it\u2019s a mature application, SQLs data needs to be backed up from time to time. From this backup, you can restore the database when any need arises. Situations When You Have To Restore SQL Data In certain situations, you need to restore the [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":7776,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_stopmodifiedupdate":true,"_modified_date":""},"categories":[6],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.10 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Restore SQL Database From Backup In Single User mode<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Restore SQL Database From Backup In Single User mode\" \/>\n<meta property=\"og:description\" content=\"SQL is a popular database management system used across the globe. Though it\u2019s a mature application, SQLs data needs to be backed up from time to time. From this backup, you can restore the database when any need arises. Situations When You Have To Restore SQL Data In certain situations, you need to restore the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/\" \/>\n<meta property=\"og:site_name\" content=\"Data Recovery Blog - KDR Tools\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-28T12:33:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-16T06:43:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/restore-database-with-recovery-rollback-1-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1600\" \/>\n\t<meta property=\"og:image:height\" content=\"739\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Manisha Rawat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Manisha Rawat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Restore SQL Database From Backup In Single User mode","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/","og_locale":"en_US","og_type":"article","og_title":"Restore SQL Database From Backup In Single User mode","og_description":"SQL is a popular database management system used across the globe. Though it\u2019s a mature application, SQLs data needs to be backed up from time to time. From this backup, you can restore the database when any need arises. Situations When You Have To Restore SQL Data In certain situations, you need to restore the [&hellip;]","og_url":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/","og_site_name":"Data Recovery Blog - KDR Tools","article_published_time":"2019-11-28T12:33:39+00:00","article_modified_time":"2020-01-16T06:43:00+00:00","og_image":[{"width":1600,"height":739,"url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/11\/restore-database-with-recovery-rollback-1-1.jpg","type":"image\/jpeg"}],"author":"Manisha Rawat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Manisha Rawat","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/#article","isPartOf":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/"},"author":{"name":"Manisha Rawat","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/36178d8a894c4f5b68ce416088beba49"},"headline":"Restore SQL Database From Backup In Single User mode","datePublished":"2019-11-28T12:33:39+00:00","dateModified":"2020-01-16T06:43:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/"},"wordCount":806,"commentCount":0,"publisher":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization"},"articleSection":["Database Recovery"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/","name":"Restore SQL Database From Backup In Single User mode","isPartOf":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#website"},"datePublished":"2019-11-28T12:33:39+00:00","dateModified":"2020-01-16T06:43:00+00:00","breadcrumb":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/restore-sql-database-from-backup-in-single-user-mode\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.kerneldatarecovery.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Restore SQL Database From Backup In Single User mode"}]},{"@type":"WebSite","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#website","url":"https:\/\/www.kerneldatarecovery.com\/blog\/","name":"Data Recovery Blog - KDR Tools","description":"Data Recovery Blog - KDR Tools","publisher":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.kerneldatarecovery.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization","name":"Data Recovery Blog - KDR Tools","url":"https:\/\/www.kerneldatarecovery.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/04\/logo.png","contentUrl":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/04\/logo.png","width":161,"height":47,"caption":"Data Recovery Blog - KDR Tools"},"image":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/36178d8a894c4f5b68ce416088beba49","name":"Manisha Rawat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2021\/11\/Manisha.jpg","contentUrl":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2021\/11\/Manisha.jpg","caption":"Manisha Rawat"},"url":"https:\/\/www.kerneldatarecovery.com\/blog\/author\/manisharawat\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/4760"}],"collection":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/comments?post=4760"}],"version-history":[{"count":16,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/4760\/revisions"}],"predecessor-version":[{"id":9296,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/4760\/revisions\/9296"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/media\/7776"}],"wp:attachment":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/media?parent=4760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/categories?post=4760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/tags?post=4760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}