Create a test database<\/h2>\n\n\n\n- Launch SQL Server Management Studio (SSMS)<\/a> and connect to your SQL Server instance.<\/li>
- Open a New Query<\/strong> window.<\/li>
- Run the following Transact-SQL (T-SQL) code to create your test database. Refresh the Databases<\/strong> node in Object Explorer<\/strong> to see your new database.<\/li><\/ol>\n\n\n\n
SQLCopy<\/p>\n\n\n\n
USE [master]\nGO\n\nCREATE DATABASE [SQLTestDB]\nGO\n\nUSE [SQLTestDB]\nGO\nCREATE TABLE SQLTest (\n\tID INT NOT NULL PRIMARY KEY,\n\tc1 VARCHAR(100) NOT NULL,\n\tdt1 DATETIME NOT NULL DEFAULT getdate()\n)\nGO\n\n\nUSE [SQLTestDB]\nGO\n\nINSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')\nINSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')\nINSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')\nINSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')\nINSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')\nGO\n\nSELECT * FROM SQLTest\nGO\n<\/code><\/pre>\n\n\n\nTake a backup<\/h2>\n\n\n\n
To take a backup of your database, do the following:<\/p>\n\n\n\n
- Launch SQL Server Management Studio (SSMS)<\/a> and connect to your SQL Server instance.<\/li>
- Expand the Databases<\/strong> node in Object Explorer<\/strong>.<\/li>
- Right-click the database, hover over Tasks<\/strong>, and select Back up…<\/strong>.<\/li>
- Under Destination<\/strong>, confirm the path for your backup is correct. If you need to change this, select Remove<\/strong> to remove the existing path, and then Add<\/strong> to type in a new path. You can use the ellipses to navigate to a specific file.<\/li>
- Select OK<\/strong> to take a backup of your database.<\/li><\/ol>\n\n\n\n
![\"Take](\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/media\/quickstart-backup-restore-database\/backup-db-ssms.png?view=sql-server-ver15\")
<\/figure>\n\n\n\nAlternatively, you can run the following Transact-SQL command to back up your database:SQLCopy<\/p>\n\n\n\n
BACKUP DATABASE [SQLTestDB] \nTO DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\SQLTestDB.bak' \nWITH NOFORMAT, NOINIT, \nNAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10\nGO\n<\/code><\/pre>\n\n\n\nRestore a backup<\/h2>\n\n\n\n
To restore your database, do the following:<\/p>\n\n\n\n
- Launch SQL Server Management Studio (SSMS)<\/a> and connect to your SQL Server instance.<\/li>
- Right-click the Databases<\/strong> node in Object Explorer<\/strong> and select Restore Database…<\/strong>.
![\"Restore](\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/media\/quickstart-backup-restore-database\/restore-db-ssms1.png?view=sql-server-ver15\")
<\/li>- Select Device:<\/strong>, and then select the ellipses (…) to locate your backup file.<\/li>
- Select Add<\/strong> and navigate to where your
.bak<\/code> file is located. Select the .bak<\/code> file and then select OK<\/strong>.<\/li>- Select OK<\/strong> to close the Select backup devices<\/strong> dialog box.<\/li>
- Select OK<\/strong> to restore the backup of your database.
![\"Restore](\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/media\/quickstart-backup-restore-database\/restore-db-ssms2.png?view=sql-server-ver15\")
<\/li><\/ol>\n\n\n\nAlternatively, you can run the following Transact-SQL script to restore your database:SQLCopy<\/p>\n\n\n\n
USE [master]\nRESTORE DATABASE [SQLTestDB] \nFROM DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\SQLTestDB.bak' WITH FILE = 1, NOUNLOAD, STATS = 5\nGO\n<\/code><\/pre>\n\n\n\nClean up resources<\/h3>\n\n\n\n
Run the following Transact-SQL command to remove the database you created, along with its backup history in the MSDB database:SQLCopy<\/p>\n\n\n\n
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB'\nGO\n\nUSE [master]\nDROP DATABASE [SQLTestDB]\nGO<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"In this quickstart, you will create a new database, take a simple backup of it, and then restore it. Prerequisites To complete this quickstart, you will need the following: SQL Server SQL Server Management Studio (SSMS) Create a test database Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Open a New Query window. Run Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,625],"tags":[622],"class_list":["post-3078","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql","tag-backup-and-restore-a-sql-server-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3078"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078\/revisions"}],"predecessor-version":[{"id":3079,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078\/revisions\/3079"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
SQLCopy<\/p>\n\n\n\n
USE [master]\nGO\n\nCREATE DATABASE [SQLTestDB]\nGO\n\nUSE [SQLTestDB]\nGO\nCREATE TABLE SQLTest (\n\tID INT NOT NULL PRIMARY KEY,\n\tc1 VARCHAR(100) NOT NULL,\n\tdt1 DATETIME NOT NULL DEFAULT getdate()\n)\nGO\n\n\nUSE [SQLTestDB]\nGO\n\nINSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')\nINSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')\nINSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')\nINSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')\nINSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')\nGO\n\nSELECT * FROM SQLTest\nGO\n<\/code><\/pre>\n\n\n\nTake a backup<\/h2>\n\n\n\n
To take a backup of your database, do the following:<\/p>\n\n\n\n
- Launch SQL Server Management Studio (SSMS)<\/a> and connect to your SQL Server instance.<\/li>
- Expand the Databases<\/strong> node in Object Explorer<\/strong>.<\/li>
- Right-click the database, hover over Tasks<\/strong>, and select Back up…<\/strong>.<\/li>
- Under Destination<\/strong>, confirm the path for your backup is correct. If you need to change this, select Remove<\/strong> to remove the existing path, and then Add<\/strong> to type in a new path. You can use the ellipses to navigate to a specific file.<\/li>
- Select OK<\/strong> to take a backup of your database.<\/li><\/ol>\n\n\n\n
<\/figure>\n\n\n\nAlternatively, you can run the following Transact-SQL command to back up your database:SQLCopy<\/p>\n\n\n\n
BACKUP DATABASE [SQLTestDB] \nTO DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\SQLTestDB.bak' \nWITH NOFORMAT, NOINIT, \nNAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10\nGO\n<\/code><\/pre>\n\n\n\nRestore a backup<\/h2>\n\n\n\n
To restore your database, do the following:<\/p>\n\n\n\n
- Launch SQL Server Management Studio (SSMS)<\/a> and connect to your SQL Server instance.<\/li>
- Right-click the Databases<\/strong> node in Object Explorer<\/strong> and select Restore Database…<\/strong>.<\/li>
- Select Device:<\/strong>, and then select the ellipses (…) to locate your backup file.<\/li>
- Select Add<\/strong> and navigate to where your
.bak<\/code> file is located. Select the .bak<\/code> file and then select OK<\/strong>.<\/li>- Select OK<\/strong> to close the Select backup devices<\/strong> dialog box.<\/li>
- Select OK<\/strong> to restore the backup of your database.<\/li><\/ol>\n\n\n\n
Alternatively, you can run the following Transact-SQL script to restore your database:SQLCopy<\/p>\n\n\n\n
USE [master]\nRESTORE DATABASE [SQLTestDB] \nFROM DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\Backup\\SQLTestDB.bak' WITH FILE = 1, NOUNLOAD, STATS = 5\nGO\n<\/code><\/pre>\n\n\n\nClean up resources<\/h3>\n\n\n\n
Run the following Transact-SQL command to remove the database you created, along with its backup history in the MSDB database:SQLCopy<\/p>\n\n\n\n
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB'\nGO\n\nUSE [master]\nDROP DATABASE [SQLTestDB]\nGO<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"In this quickstart, you will create a new database, take a simple backup of it, and then restore it. Prerequisites To complete this quickstart, you will need the following: SQL Server SQL Server Management Studio (SSMS) Create a test database Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Open a New Query window. Run Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,625],"tags":[622],"class_list":["post-3078","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql","tag-backup-and-restore-a-sql-server-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3078"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078\/revisions"}],"predecessor-version":[{"id":3079,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3078\/revisions\/3079"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3078"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3078"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3078"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}