Is there a way to "Reset" data for a particular CompanyID on one database so only records for that company get saved and restored.

We have all our companies and data in 1 database.
In the database everything related to CompanyID = 1 is the Demo Company that the salesmen uses to demonstrate the system .
Problem is the salesmen tend to add/update/delete data during the Demo.

Using SQL Server 2014 Standard Edition, Is there a cool way to some how snapshot the data in the data just related to CompanyID = 1?
So when the next salesmen wants to a demo they can just "reset" everything back to the way it was for the next salesman?

Instead of writing a massive stored procedure to manually add/update/delete data back to the way it was pre-demo?
Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
 
AndyAinscowFreelance programmer / ConsultantCommented:
A few possible ways come to mind - but depending on just what you do none might be suitable.
The simplest is just not to let your salesmen use the functional database but login to a copy which can be dropped and replaced with the original after use.
0
 
Mark WillsTopic AdvisorCommented:
1) Ideally have them log into "DEMO" db which you can backup restore rebuild as often as needed. Change to connection string, maybe a new Icon on their desktop / laptop whatever device.

2) Script. Byte the Bullet (pardon the pun). You will need to go through and clean anyway, and by the sounds of it, have been doing so. So, next time it has been 'cleansed' take a copy by select * into demo_copy_of_data_tablename from tablename where companyID = 1
then use the demo_copy.... tables to build scripts

And other possibilities....

How many tables ?
How many rows ?
How much is static v change v added transactions ?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could do this:

1. Create the Demo with the exact data needed.
2. Take a Backup of that Demo
3. Create a routine that would "restore" that demo. That routine would (a) delete the current Demo data and then (b) restore the Demo database using the backup from step 2.

I do something very similar with a program I created for a scoring system.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Is the backup smart about it? Or can it be made smart, being that all the companies are in one database... only backup and restore the records for CompanyID = 1?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could make a smart backup, but a better solution (to me) would be to create an entirely separate database for a DemoCompany, and install that on your sales laptops/machines. Unless your sales people need actual live data from their client on their laptops/machines (which is way to much on the "we're liable for your data now" side for my comfort), then they should only ever need that demo data.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
:-)   (see my first comment)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry Andy - I somehow missed your first comment. Didn't mean to step on toes!
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
No problems - glad to see someone of your expertise is of the same 'best' idea.
0
 
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you guys for all your help! :)
0
 
Mark WillsTopic AdvisorCommented:
>> Sorry Andy - I somehow missed your first comment. Didn't mean to step on toes!

Nor the second post apparently :)
0
 
PatHartmanCommented:
I see that you have already accepted answers but here is a more complete one.  I use a method similar to what Scott suggested except that it is formalized.  The original data is stored in the FE in local tables.  On a button click, the user can reset the training data.  The code deletes (cascade delete helps so I only need three delete queries) the training records and then runs append queries to bring back the deleted data from the local tables.  There are a dozen append queries.  The process is quite simple.  To avoid complications due to orphaning child records by changing the PK of parents, some original autonumbers are restored in the parent tables.  There is a little twist in the code because the BE could be ACE or it could be SQL Server and the procedure for appending existing identity columns for SQL Server is more complex than the simple Access method.
Private Sub cmdReset_Click()

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef

    Dim strTable As String
    Dim strConnect As Variant
On Error GoTo Err_Proc
    
    If ValidateUserRole(gAdminRole) = True Then  'Admin
    Else
        MsgBox "You are not authorized to run this update.", vbOKOnly + vbInformation
        DoCmd.OpenForm Me.OpenArgs, , , , , acWindowNormal
        Exit Sub
    End If

    If MsgBox("Are you sure you want to reset training?  this action cannot be undone.", vbYesNoCancel + vbQuestion) <> vbYes Then
        DoCmd.OpenForm Me.OpenArgs, , , , , acWindowNormal
        Exit Sub
    End If
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qRESET_020_DeleteAuditParms", acNormal, acEdit
    DoCmd.OpenQuery "qRESET_030_DeleteListValues", acNormal, acEdit
    
    strConnect = DLookup("Connect", "MSysObjects", "[Name] = 'tblMembers'")

    If IsNull(strConnect) Then
        DoCmd.OpenQuery "qRESET_110_AppendAuditParms"
        DoCmd.OpenQuery "qRESET_130_AppendListValues"
        DoCmd.OpenQuery "qRESET_140_AppendDocuments"
        DoCmd.OpenQuery "qRESET_210_AppendMembers"
        DoCmd.OpenQuery "qRESET_220_AppendDependents"
    Else
        'when tables are linked SQL, the identity insert must be set to on
        'to allow rows with existing autonumbers to be inserted and then off at the end.
        'We do this so that the test data always retains its original autonumbers.
        Set db = CurrentDb()
        Set qd = db.CreateQueryDef("")
            qd.Connect = "ODBC;" & strConnect
            qd.ReturnsRecords = False
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_110_AppendAuditParms"
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_130_AppendListValues"
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_140_AppendDocuments"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_210_AppendMembers"
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_220_AppendDependents"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " OFF"
            qd.Execute
            
            qd.Close
            db.Close
    End If
    
''  The identity columns of these tables do not need to be preserved

    DoCmd.OpenQuery "qRESET_230_AppendDepVerification"
    DoCmd.OpenQuery "qRESET_240_AppendComments"
    DoCmd.OpenQuery "qRESET_250_AppendHelpComments"
    DoCmd.OpenQuery "qRESET_260_AppendLtrSent"
    DoCmd.OpenQuery "qRESET_270_AppendRefDocs"
    If MsgBox("Do you also want to reset the bookmarks?  If you did not change them, say NO.", vbYesNo) = vbYes Then
        DoCmd.OpenQuery "qRESET_300_AppendBookmarks"
    End If
    
    MsgBox "Update Complete.", vbOKOnly + vbInformation
    
        'Add divisions to division table
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("qAppendExistingDivisions")
    DoCmd.SetWarnings True
    
    Forms!frmLogin!cboAuditParmsID.Requery
    DoCmd.Close acForm, "frmResetTraining"

Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
            Resume
    End Select
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.