SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CMS - Backup/Restore Facility?

    (2nd question ever...! )

    Currently my CMS uses an SQL Server 2000 DB for all data storage, and (once in production) the entire database will be backed up regularly, and thus restoring the entire database on demand will be no problem.

    However, the site is divided into sub-sites which are managed by different groups via the CMS, and I need to be able to offer a independent backup/restore facility for each of sub-sites - i.e. the system must be able to restore a sub-site while leaving all others unaffected.

    Now, backing up is fine, because I merely persist the data to an XML document. More difficult is restoring the data (let's assume that an entire sub-site has been wiped and all related records removed from the DB).

    Since I am using an autonumber field for the primary key in each table, and hence I would expect that once a record with an ID of 7 (for example) is removed from the table, it cannot simply be inserted into the DB with that ID - it would have to be given a new ID via the autonumber, which would then mess up linked records in the CMS.

    The only thing I can think of at the moment is to track the mapping of the old IDs to the new IDs, and to update the old IDs in dependent data once all the records have been reinserted/updated.

    However, perhaps there's an easier, tidier way around this, since it seems to me that this must be a relatively common need...

    Is there something that I'm not aware of here?

    Is there a way to insert a record with a specified value for an autonumber field?

    Any other suggestions?


    (BTW - I thought that this wasn't necessarily a database-specific question, so I thought I'd post here rather than elsewhere, but feel free to move it if you think it appropriate!!!)

    Edit:

    In retrospect, perhaps it should be in "Files, Databases and XML"...!!!
    Last edited by M@rco; Sep 30, 2002 at 04:45.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  2. #2
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Moved.

    Good question actually... I had thoughts such as "don't make it unique" and "use external DTS apps to do data verification and propagation of values" but those aren't:

    1. Professional
    2. Solving your problem (they just create different problems)

    Drawing a blank mate...
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Jeremy W.
    Drawing a blank mate...


    When I mentioned persisting the data to XML, I meant via my own script, but if I use ADO's persist to XML feature, delete the records in the database, load the XML back into the recordset and do a .Update (or .UpdateBatch), then will it happily restore those records with the primary keys intact, or will it complain/fall over?

    If no-one knows, I'll give it a try, but I thought I'd ask first....!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  4. #4
    Ribbit... Eric.Coleman's Avatar
    Join Date
    Jun 2001
    Location
    In your basement
    Posts
    1,268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, this doesn't make sense..

    If each row has it's own ID, thanks to the auto-id function, there shouldn't be a problem.

    Each row still has an id, so just make sure you specify a value for it when restoring the database, and you won't have a problem.

    Even though a column is set on auto_increment or whatever, you can still specify and ID afaik.
    Eric Coleman
    We're consentratin' on fallin' apart
    We were contenders, now throwin' the fight
    I just wanna believe, I just wanna believe in us

  5. #5
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the meantime, I've tested the XML persisting-->deleting-->reloading-->updating idea and it doesn't work - it doesn't recreate the deleted records. (So that's out the window!)

    I then thought that I'd check out the possibility of inserting specific values into an autonumber/autoincrement field (as I'd pondered in my original post)...

    Eureka! - after scouring the SQL Server Books Online help files, I came across the Transact-SQL command "SET IDENTITY_INSERT", which allows you to enable/disable the mechanism which prevents you from specifying the value of an autonumber/autoincrement field!

    Hence, this example does what I want:
    Code:
    SET IDENTITY_INSERT [Group] ON;
    INSERT INTO [Group](ID, Name) VALUES (14, "test");
    SET IDENTITY_INSERT [Group] OFF;
    (Hurray!!! )

    Hope this information comes in handy for someone else someday!

    Zaire - I'd discovered this before I'd read your post, but the whole point of all this was that you usually can't do what you were suggesting (which is exactly what I wanted to do)! However, it would appear that in MS-SQL you can, with the help of that command/switch!! Thanks for your response anyway!


    Case closed!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  6. #6
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I had actually figured out how we do it

    Identity and auto-numbers are different in our system. Anytime an ID is to be used cross-system, it is in a separate field and is enumerated by the "System" (since AWS means nothing to you guys ). Therefore, for us, there is no issue

    J
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Jeremy W.
    Identity and auto-numbers are different in our system. Anytime an ID is to be used cross-system, it is in a separate field and is enumerated by the "System" (since AWS means nothing to you guys ).
    "cross-system"? You mean as a foreign key, to link records together?

    What's AWS?

    How does your system's backup/restore facilities work? Does it spit out a file for you to save, which you can then supply later at restore? (This is the way I'm going to do mine, so that users are empowered to look after their own data)

    Or does it do something else?
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Kinda foreign key thing, but the databases aren't tied directly together (data abstraction layer or whatever it's called).

    AWS: Automated Workflow System

    It's the massive Enterprise system we're building here at work.

    The backup/restore is something we are actually working on. We've realised that we were so smart in our planning that the backup/restore will be fine. In fact the only true "dependency" that exists is the user ID. So long as we have the valid uid, we can move an entire subset of data to a disparate network/system and it'll work, no biggie.

    We'll likely do database-based backups as opposed to XML ones. Not sure, but we can use SQL's backup utility and DTS services to automatically put certain subsets of data into archive. If we wanted to allow users to download them (which we don't) we might use files.

    Never know though
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok! Cheers!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •