Rolling Back a database

  • Hi folks

    Got a question.

    I've a test/development server with SQL Server 2000 installed; due to the way we use it, I have to be able to switch the same database to different versions of our product in fairly short order.

    In Oracle, I handle this by taking cold copies of the datafiles, redo logs & controlfiles when the database has a given version of the product's DB component installed on it, then using those copies to overwrite the existing datafiles, redo logs & controlfiles when I need to reset the product from another version and/or codesream back to the old one (for testing of nw releases etc.).

    However, I'm still quite new to SQL Server, and am not sure whether just overwriting the datafiles & transaction logs with the older copies like this will work.

    Can anybody throw some light on the question, please? I'd be MOST grateful for some advice & guidance on the matter!

    Thanx

    Dave.

  • No, replacing the files using OS commands won't work.

    However, you could use sp_detach_db to free up the files and then copy them to a named directory (20070328 for example)

    When you want to restore that database, you would use sp_attach_db.

    another option is to do a database backup, store it into your named directory and then restore the backup when you need it.

     

     


    Regards,

    Carlos

  • Carlos

    Many thanx for that - this at least gives me a point of attack. I'll start digging along these lines.....

    Regards

    Dave.

  • Assuming:

    - 1 instance of SQL server installed on the server

    - An "application" uses one or more user defined databases

    - You have N applications to support, where you only want the databases of one application attached/available at any given time

    Then:

    - (Optional) Store the files for the databases all in the same related folder, e.g. C:\App1\DBFiles, C:\App2\DBFiles, etc.

    - Create a set of sp_attach_db and sp_detach_db scripts for each application, to attach and detach all applicable databases from where they're stored

    Issues:

    - If you have SQL Agent jobs, DTS packages, logins, or other "database artifacts" that are not stored within the user databases, then they have to be managed separately from the databases

    - Could you just configure N named instances of SQL on the server, and only have one running at a time? (The applications would have to be configured to connect to their respective SQL instance.)

    - If no two applications use/reference a database with the same name, except for performance reasons why not have all the databases attached all the time? (But if, say, App1 and App2 both use a database named "Fred", this won't work.)

    A LOT depends on your application, environment, and what you're actually trying to achieve. Good luck!

    Philip

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply