March 28, 2007 at 9:12 am
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.
March 28, 2007 at 10:03 am
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.
Carlos
March 28, 2007 at 10:05 am
Carlos
Many thanx for that - this at least gives me a point of attack. I'll start digging along these lines.....
Regards
Dave.
March 28, 2007 at 3:17 pm
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