I recently had to back up one critical table for a major maintenance activity for my SQL server database. I have identified a few simple approaches to do this. In this article, I will walk you through all those approaches to backup a table in an SQL Server.
How To Backup A Table In SQL Server
Let us discuss all the approaches individually.
Approach-1 Using SQL Server Management Studio
To backup a table in SQL server, follow the below steps.
1. Open SQL Server Management Studio (SSMS) and connect to the database instance.
2. Right-click on the database name, select tasks, and then click on the Generate Scripts option, as shown in the screenshot below.
3. Click on the Next button on the pop-up below.
4. On the Choose Objects window, Select the Select specific database objects option, expand the Tables node, select the table you want to back up, and click the Next button.
5. On the set scripting options window, click the Advanced button. Now, on the Advanced scripting options window, ensure to select the Schema and data option for the Types of data to script option and then click the OK button, as shown in the screenshot below.
6. Select the Open in new query window as shown below and then click on the Next button as shown below.
7. On the Summary page, review the selections carefully and then click on the Next button.
8. Finally, it will generate the script with schema and data; click on the Finish button as below.
Now, on the SQL server Management studio, you will see the complete query in a new window. Now, copy the query and execute the query in any database, which will create the same table with that same data.
Approach-2 Using SELECT INTO statement
Using the query below, we can also use the SELECT INTO statement to back up the SQL server table with data.
SELECT * INTO LatestProduct FROM ProductAfter executing the above query, I got the expected output below.
Now, to cross-check, we can execute the below select query, and you can see the exact data.
Select * from LatestProduct
Conclusion
Taking the backup of a table in SQL Server is so easy using SQL Server Management Studio (SSMS) design approach or using the SELECT INTO statement as mentioned in this article.
You may also like following the articles below.
- How To Make Font Bigger In SQL Server Management Studio
- How to Add Column to a Table in SQL Server?
- How to Create a Table in SQL Server Management Studio
- How to Create Temp Table in SQL Server
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
