Take complete backup of table with index and keys in SQL Server

I have a table (i.e. tbl_test) with 46 index and 42 keys. I have taken backup via this command: But it is unable to copy index and keys in the backup table. I want to take a complete backup of the table with index and keys. Please suggest how.

I have a table (i.e. tbl_test) with 46 index and 42 keys.

I have taken backup via this command:

select * into tbl_test_bkp_18112019 from tbl_test

But it is unable to copy index and keys in the backup table. I want to take a complete backup of the table with index and keys. Please suggest how.

2

2 Answers

Select * into - Does not copy indexes, constraints, etc.

You should generate a script from SQL server management studio by right-clicking on the desired table -> Script table as -> create to -> New query windows.

Change the table name as needed and run the script.

This will create a new table with the same structure, indexes, constraints, etc.

If you need data as well then you can insert from the orignal table to this new table using insert command

Insert newtablename select columns from oldtablename
2

You can Try Task > Generate Script and select only table with data and check in SQL Management Studio

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobHFxYmuAeYSOrZiknV2YvK68y56rnmWSlrCswc9mpp9lpJavrbGMsKCtoF2eu6Wx12aYp5xdoLK6v4yipWaroaF6tLHRr5yr

 Share!