User Deleted, Can they be rescued?


Hi, I have had a user that has been deleted by mistake. Is there anyway to get their posts and user info back from a DB backup?

Regards, Ant
InstantForum Question

Hi Ant,

Great to hear from you. I hope your well. I'm sorry to hear of the trouble. 

I'm afraid If you delete a user within InstantForum they will be permanently deleted from the database and all posts for that user will also be permanently deleted. 

If you don't wish to delete a user but simply hide the user and there posts from other users you can set the users primary member group as the banned member group. The banned member group is a hidden member group by default meaning users who have this member group as a primary member group will never appear in lists and posts from those users will never appear in forums or topics. 

The easiest option for you would of course be to perform a full restore from a recent database back-up. Not ideal. 

If your able to restore each database side by side you could use SQL to copy the user from a source InstantForum database into your target InstantForum database. To help you with this I've prepared the example below. This targets InstantForum 2016-2 so you may need to customize some column lists / names depending on your version. 

In addition due to foreign keys on the ForumID column within InstantForum_Topics both your source and target databases must have exactly the same forums with the same ForumIDs.

Of course please do test this SQL locally first against local databases. I've tested using 2 local InstantForum 2016-2 databases however we cannot be held responsible for any data loss. I'm simply providing this SQL to help you if you wish to perform the migration manually...

DECLARE @intUserID INT
SET @intUserID = 39 -- the user ID from the source database to copy into the target database

-- this example uses two InstantForum 2016-2 databases
-- if your using an earlier version of InstantForum you may need to adjust this script
-- For example you may need to remove newer columns added to InstanntASP_Users

-- source database = [InstantForumDbSource]
-- target database = [InstantForumDbTarget]

USE [InstantForumDbTarget]; -- USE TARGET DATABASE

-- ensure user does ot already exist in target
IF (NOT EXISTS(SELECT UserID FROM InstantASP_Users
WHERE UserID = @intUserID))
BEGIN

-- copy main user

IF (IDENT_SEED('InstantASP_Users') IS NOT NULL )
SET IDENTITY_INSERT InstantASP_Users ON

INSERT INTO InstantASP_Users
(
[UserID],
[PrimaryRoleID],
[Username],
[LdapUsername],
[EmailAddress],
[Password],
[PasswordSalt],
[Culture],
[TimeZoneOffset],
[ObserveDaylightSavingTime],
[DateTimeFormat],
[FirstDayOfWeek],
[FirstName],
[LastName],
[CompanyName],
[JobTitle],
[PublicEmail],
[WebAddress],
[BlogAddress],
[OpenID],
[PhotoImage],
[MSN],
[Skype],
[YIM],
[AIM],
[ICQ],
[Twitter],
[Facebook],
[GooglePlus],
[LinkedIn],
[MySpace],
[YouTube],
[Location],
[DOBDay],
[DOBMonth],
[DOBYear],
[ConfirmationCode],
[TotalVisits],
[CreatedDate],
[LastLoginDate],
[UserLevelTitle],
[UserLevelImageURL],
[IPAddress],
[UsernameEncoded],
[RecognitionPoints],
[BannerImage],
[Phone],
[SnapChat],
[WhatsApp],
[Skills],
[ShowAge],
[Gender],
[APIKey],
[UserIconID]
)
SELECT
[UserID],
[PrimaryRoleID],
[Username],
[LdapUsername],
[EmailAddress],
[Password],
[PasswordSalt],
[Culture],
[TimeZoneOffset],
[ObserveDaylightSavingTime],
[DateTimeFormat],
[FirstDayOfWeek],
[FirstName],
[LastName],
[CompanyName],
[JobTitle],
[PublicEmail],
[WebAddress],
[BlogAddress],
[OpenID],
[PhotoImage],
[MSN],
[Skype],
[YIM],
[AIM],
[ICQ],
[Twitter],
[Facebook],
[GooglePlus],
[LinkedIn],
[MySpace],
[YouTube],
[Location],
[DOBDay],
[DOBMonth],
[DOBYear],
[ConfirmationCode],
[TotalVisits],
[CreatedDate],
[LastLoginDate],
[UserLevelTitle],
[UserLevelImageURL],
[IPAddress],
[UsernameEncoded],
[RecognitionPoints],
[BannerImage],
[Phone],
[SnapChat],
[WhatsApp],
[Skills],
[ShowAge],
[Gender],
[APIKey],
[UserIconID]
FROM [InstantForumDbSource].[dbo].InstantASP_Users
WHERE UserID = @intUserID;

IF (IDENT_SEED('InstantASP_Users') IS NOT NULL )
SET IDENTITY_INSERT InstantASP_Users OFF

-- copy roles

IF (IDENT_SEED('InstantASP_UsersRoles') IS NOT NULL )
SET IDENTITY_INSERT InstantASP_UsersRoles ON

INSERT INTO InstantASP_UsersRoles
(
[UsersRolesID],
[RoleID],
[UserID]
)
SELECT
[UserRoleID],
[RoleID],
[UserID]
FROM [InstantForumDbSource].[dbo].InstantASP_UsersRoles
WHERE UserID = @intUserID;

IF (IDENT_SEED('InstantASP_UsersRoles') IS NOT NULL )
SET IDENTITY_INSERT InstantASP_UsersRoles OFF

-- copy forum specific user

INSERT INTO InstantForum_Users
(
[UserID],
[PermissionID],
[Skin],
[Notes],
[Followers],
[Occupation],
[Interests],
[Biography],
[PostSignature],
[PostCount],
[LastPostDate],
[LastPMDateStamp],
[IsAnonymous],
[ReceiveEmailFromAdmins],
[ReceiveEmailFromMembers],
[EnablePM],
[EnablePMPopUp],
[ReceivePMsFromAdmins],
[ReceivePMEmailNotification],
[ReceivePMReadReceipts],
[CreateSentPMCopy],
[ViewSignatures],
[ViewImages],
[ViewEmotIcons],
[ViewAvatars],
[TopicsPerPage],
[PostsPerPage],
[DefaultDateFilter],
[DisplayMode],
[RichTextBoxTemplate],
[ExecuteSQL],
[TagLine],
[WarningCount]
)
SELECT
[UserID],
[PermissionID],
[Skin],
[Notes],
[Followers],
[Occupation],
[Interests],
[Biography],
[PostSignature],
[PostCount],
[LastPostDate],
[LastPMDateStamp],
[IsAnonymous],
[ReceiveEmailFromAdmins],
[ReceiveEmailFromMembers],
[EnablePM],
[EnablePMPopUp],
[ReceivePMsFromAdmins],
[ReceivePMEmailNotification],
[ReceivePMReadReceipts],
[CreateSentPMCopy],
[ViewSignatures],
[ViewImages],
[ViewEmotIcons],
[ViewAvatars],
[TopicsPerPage],
[PostsPerPage],
[DefaultDateFilter],
[DisplayMode],
[RichTextBoxTemplate],
[ExecuteSQL],
[TagLine],
[WarningCount]
FROM [InstantForumDbSource].[dbo].InstantForum_Users
WHERE UserID = @intUserID;

-- add PM folders
EXEC if_sp_InsertFolder
@intUserID=@intUserID,
@strFolderName=N'PMFolder_InBox',
@bitInBox=1,@bitSentItems=0,
@bitDeletedItems=0,
@intSortOrder=3,
@bitRequired=1

EXEC if_sp_InsertFolder
@intUserID=@intUserID,
@strFolderName=N'PMFolder_SentItems',
@bitInBox=0,@bitSentItems=1,
@bitDeletedItems=0,
@intSortOrder=2,
@bitRequired=1

EXEC if_sp_InsertFolder
@intUserID=@intUserID,
@strFolderName=N'PMFolder_DeletedItems',
@bitInBox=0,@bitSentItems=0,
@bitDeletedItems=1,
@intSortOrder=1,
@bitRequired=1

---------------------------------
-- ALL USER DATA COPIED, NOW COPY POSTS
---------------------------------

-- loop through posts in source adding to target
DECLARE @intPostID int
DECLARE MSGCURSOR CURSOR FOR
SELECT PostID FROM [InstantForumDbSource].[dbo].InstantForum_Topics
WHERE UserID = @intUserID
OPEN MSGCURSOR

FETCH NEXT FROM MSGCURSOR
INTO @intPostID

WHILE @@FETCH_STATUS = 0
BEGIN

-- add our topics

IF (IDENT_SEED('InstantForum_Topics') IS NOT NULL )
SET IDENTITY_INSERT InstantForum_Topics ON

INSERT INTO InstantForum_Topics
(
[PostID],
[ForumID],
[TopicID],
[ParentID],
[UserID],
[MessageIconName],
[LastPosterUserID],
[LastPosterUsername],
[LastPosterPostID],
[Replies],
[Views],
[Title],
[Description],
[DateStamp],
[LastPosterDate],
[Queued],
[IsPinned],
[IsLocked],
[IsPoll],
[IsMoved],
[Rating],
[TotalRatings],
[HasAttachments],
[EditUserID],
[EditUsername],
[EditDateStamp],
[IPAddress],
[TitleEncoded],
[IsAnswer],
[IsAnswerUserID],
[Likes],
[TotalLikes],
[TotalAnswers],
[TotalFavorites],
[IsSpam],
[TopicTypeID],
[ReportedCount]
)
SELECT
[PostID],
[ForumID],
[TopicID],
[ParentID],
[UserID],
[MessageIconName],
[LastPosterUserID],
[LastPosterUsername],
[LastPosterPostID],
[Replies],
[Views],
[Title],
[Description],
[DateStamp],
[LastPosterDate],
[Queued],
[IsPinned],
[IsLocked],
[IsPoll],
[IsMoved],
[Rating],
[TotalRatings],
[HasAttachments],
[EditUserID],
[EditUsername],
[EditDateStamp],
[IPAddress],
[TitleEncoded],
[IsAnswer],
[IsAnswerUserID],
[Likes],
[TotalLikes],
[TotalAnswers],
[TotalFavorites],
[IsSpam],
[TopicTypeID],
[ReportedCount]
FROM [InstantForumDbSource].[dbo].InstantForum_Topics
WHERE PostID = @intPostID;

IF (IDENT_SEED('InstantForum_Topics') IS NOT NULL )
SET IDENTITY_INSERT InstantForum_Topics OFF

-- add our messages

INSERT INTO InstantForum_Messages
(
[PostID],
[Message],
[DateStamp]
)
SELECT
[PostID],
[Message],
[DateStamp]
FROM [InstantForumDbSource].[dbo].InstantForum_Messages
WHERE PostID = @intPostID;

FETCH NEXT FROM MSGCURSOR
INTO @intPostID

END
-- tidy cursor
CLOSE MSGCURSOR
DEALLOCATE MSGCURSOR

END -- end user to see if user exists in target

GO


This will copy all core user information and all posts for the user. This won't copy private messages or update post counts so you may need to use the synchronize & rebuild options via the Admin CP if the user has lots of posts to ensure counts are reflected correctly after the import. 

I've also attached this code as a SQL file for you which may be easier to work with below. 

If I can assist further Ant don't hesitate to respond, 


http://www.instantasp.co.uk/images/line.gif
Kindest Regards,

Ryan Healey



ryan@instantasp.co.uk
www.instantasp.co.uk
Blog | Community | Docs

http://www.instantasp.co.uk/core/assets/images/email/facebook.png http://www.instantasp.co.uk/core/assets/images/email/twitter.png http://www.instantasp.co.uk/core/assets/images/email/google.png http://www.instantasp.co.uk/core/assets/images/email/linkedin.png
Attachments
copyforumuser.sql (0 views, 7.00 KB)

Hi Ryan

Thanks for the information. I will try it off site on our test site.

Cheers, Ant
GO

Login
Existing Account
Email Address:


Password:


Social Logins

Select a Forum....








InstantASP Forums


Search