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:
- Is there any issue with changing names for objects listed below on Production?
Indexes, Triggers, Primary keys, Foreign keys, Default Constraints and Check Constraints
- 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.
1条答案
按热度按时间gudnpqoy1#