SQL Server Renaming Index, Trigger, Primary key, Foreign key and Constraints

xu3bshqb  于 2023-04-19  发布在  其他
关注(0)|答案(1)|浏览(138)

This is for MS SQL server 2017.

I'm working on having secure backup of some tables in case i need to rollback changes. I have some tables which will be updated. These tables' have Indexes, Triggers, Primary keys, Foreign keys, Default Constraints and Check Constraints

To backup such tables I use the following statement.

select * into DBMain.dbo.Test from DBBackup.dbo.DBMain_Test

As you know the statement above does not move / create any indexes, foreign and primary keys, triggers and constraint.

In case something goes I'm planning simply rename real tables that have been updated using "sp_rename"

USE DBMain;
GO
exec sp_rename N'dbo.Test', 'Test_IncorrectlyUpdated'
select * into DBMain.dbo.Test from DBBackup.dbo.DBMain_Test
-- then recreating indexes, triggers, keys and constraints

then move backup tables to designated databases and create the same indexes, FK, PK, triggers and constraints on them, but with different names. Please pay attention that tables names will remain the same in case of rollback, but the constraints, indexes, triggers and keys will have different names as SQL Server does not allow duplicate names for these objects withing the same database.

QUESTION:

  1. Is there any issue with changing names for objects listed below on Production?

Indexes, Triggers, Primary keys, Foreign keys, Default Constraints and Check Constraints

  1. is there any better approach of achieving this rollback?

Please note, that no backup of all affected databases is possible, neither switching to a mirror server is possible.

gudnpqoy

gudnpqoy1#

  1. Renaming objects like indexes, Triggers, Primary keys, Foreign keys, Default Constraints and Check Constraints should not cause any issues.
  2. You should consider is to use SQL Server's transaction log backups to achieve point-in-time recovery. Transaction log backups capture all the transactions that occur on a database since the last full or differential backup, allowing you to restore the database to a specific point in time.

相关问题