I am always kind of nervous when I am deleting data from a production environment. Or updating large datasets. To be able of rolling back changes you can save you data into a backup table by using the output clause.

Lets take a look at an example

create table myTest (ID int, name varchar(50))

insert into myTest values (1, 'John')
insert into myTest values (2, 'Frank')
insert into myTest values (3, 'Sammy')
insert into myTest values (4, 'Julie')
insert into myTest values (5, 'Mike')

delete from myTest
output deleted.* 
where ID = 5;

drop table myTest

And this will give us an output on the rows we are deleting

If we want to save this to a new table we can change our syntax to this

create table myTest (ID int, name varchar(50))

insert into myTest values (1, 'John')
insert into myTest values (2, 'Frank')
insert into myTest values (3, 'Sammy')
insert into myTest values (4, 'Julie')
insert into myTest values (5, 'Mike')

create table myBackup (ID int, name varchar(50))

delete from myTest 
output deleted.* into myBackup
where ID = 5;

select * from myBackup

drop table myTest;
drop table myBackup;

To do the same for updated rows you can use this syntax (saves the data BEFORE the update)

update myTest 
	Set name = 'Willy'
output deleted.* into myBackup
where Id = 5

And to save the new data

update myTest 
	Set name = 'Willy'
output inserted.* into myBackup
where Id = 5