[Guide]Restore a table without restoring the whole database

[QuantumRising]

In playing with my test server I've fucked up a few times as I'm sure some of you have as well.

Here's how you can restore an entire table or only parts of a table without restoring the entire database.

This is best used on _Ref<X> tables, tables that are only ever changed if you do it yourself. Dynamic tables like _Items or _Char are subject to problems if you use this kind of query and I don't recommend it and won't help you if you do it and something breaks.

First

ALWAYS MAKE A BACKUP BEFORE YOU RUN ANY QUERY THAT MODIFIES DATA IN YOUR DATABASE

Should go without saying.

Second

This method would probably give professional DBAs a heart attack. But it works in specific instances as mentioned above, on static tables.

Here it is:

USE DATABASE --Replace with whatever db youre updating eg: SRO_VT_SHARD
GO
SET IDENTITY_INSERT TableName OFF --Replace with the table youre updating eg: _RefSkill
MERGE DATABASE.dbo.TableName dest --Replace with the multipart reference to your table eg: SRO_VT_SHARD.dbo._RefSkill
USING (SELECT * FROM SRO_VT_SHARD_BACKUP.dbo.TableName) AS src --Replace with the multipart reference to your backup table eg: SRO_VT_SHARD_BACKUP.dbo._RefSkill
	ON dest.UniqueIdentifier = src.UniqueIdentifier --Replace with the unique identifier columns in your table. (The one that is auto incremented and that you aren't allowed to change) In the case of _RefSkill it's dest.id = src.id
WHEN MATCHED THEN UPDATE
SET 
	dest.ColumnName1 = src.ColumnName1, --Replace with the columns you want to update. eg: dest.Param4 = src.Param4
	dest.ColumnName2 = src.ColumnName2; --Replace with the more columns you want to update. eg: dest.Param4 = src.Param5

That being said, it's ghetto and not recommended. If you happen upon this guide before you fuck something up give RoyalBlade's method a shot instead and use that in the future to make and test changes.

A BETTER METHOD

EXEC SP_RENAME _RefSkill, _RefSkillReal; --Renames the RefSkill table to _RefSkillReal
SELECT * INTO _RefSkill FROM _RefSkillReal; --Recreates the _RefSkill table from the data in _RefSkillReal

Now you can make changes and test things without index and foreign key restraints, and if something doesn't work you can just run

DROP TABLE _RefSkill; --Drop the table
SELECT * INTO _RefSkill FROM _RefSkillReal; --Remake it fresh from the backup

To start over with a fresh _RefSkill table from your last working version
Once your change is confirmed working you can create a query for your changes and then restore the original table and run the query to change things legitimately.

DROP TABLE _RefSkill; --Pretty self explanatory
EXEC SP_RENAME _RefSkillReal, _RefSkill; --Renames the real _RefSkill table back as it should be

Thanks to RoyalBlade's original post:

 Spoiler

Originally Posted by Royalblade*

EXEC SP_RENAME _RefSkill, _RefSkillReal
SELECT * INTO _RefSkill FROM _RefSkillReal

Now you can go ahead and test shit on the new index & keyless RefSkill. You can edit data much easier without worrying about shit. No Primary keys, no foreign keys, no mofo to tap your nuts. If shit works, you apply the same change (which should always be 'querified') on the REAL table. Then you drop the test table and rename the real table back. Done.

No errors. No Insanity.

Peace out

[Royalblade*]

I've lost at least 5 years of life expectancy just seeing this load.

You are asking for improvements? Gotta remove it!

..... RAGE END:

How about you simply..


EXEC SP_RENAME _RefSkill, _RefSkillReal
SELECT * INTO _RefSkill FROM _RefSkillReal

Now you can go ahead and test **** on the new index & keyless RefSkill. You can edit data much easier without worrying about ****. No Primary keys, no foreign keys, no mofo to tap your nuts. If **** works, you apply the same change (which should always be 'querified') on the REAL table. Then you drop the test table and rename the real table back. Done.

No errors. No Insanity.

Peace out

[B1Q]

*reads query*
*goes blind*

[QuantumRising]

Originally Posted by Royalblade*

I've lost at least 5 years of life expectancy just seeing this load.

You are asking for improvements? Gotta remove it!

..... RAGE END:

How about you simply..


EXEC SP_RENAME _RefSkill, _RefSkillReal
SELECT * INTO _RefSkill FROM _RefSkillReal

Now you can go ahead and test shit on the new index & keyless RefSkill. You can edit data much easier without worrying about shit. No Primary keys, no foreign keys, no mofo to tap your nuts. If shit works, you apply the same change (which should always be 'querified') on the REAL table. Then you drop the test table and rename the real table back. Done.

No errors. No Insanity.

Peace out

I did say it would give you a heart attack didn't I?

Anyway this is a better method for sure. Updated.

Originally Posted by hazemooking

*reads query*
*goes blind*

We all do stupid things when we're learning