I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I didn't see any options on the MSDE install that implements seperate data/log paths.
I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.
Any suggestions? Thanks in advance.When you install MSDE you can add settings to the setup.ini file that specify the TARGETDIR (where the binaries and log files go) and DATADIR (where the data goes).
If you need to move the locations of those after the fact,Books Online contains a topic called "Attaching and Detaching a Database" which explains how.
Terri|||Excellent, thanks for the information! I'll check out the Books online link.
I think part of the problem of finding the information that I'm sure must be in the documentation, is knowing the exact terminology used. "Attaching and Detaching" never made it into my search input box. Thanks again..|||If you haven't yet downloaded and installed Books Online you definitely should! I myself refer to it dozens of times each week. It's a terrific free SQL Server 2000 reference and really helps with synax as well as larger concepts.
And yes, it can certainly be a struggle to figure out which terms to look for!
Terri|||Hi,
Thanks again for the BOOKS ONLINE reference, I've used it a number of times already. Curiously, it hasn't been much help with my original problem. I did learn about detaching and reattaching databases, and that it one way to get the physical locations set for my user DB .mdf and .ldf files. I still haven't worked out how to get the model database to refelct these new assignments (thought there are a few more thngs to try).
While searching the BOOKS ONLINE, I did find the customizing MSDE install section. Unfortunatelly, it looks like there are no paramenters to the setup that will allow an installation with seperate drive locations for the data and log files. You can specify the location of the system data files, but the setup automatically adds the log file in the same location. I had considered reinstalling MSDE, or adding a new instance, but that doesn't seem to offer a solution.
I've run into a number of roadblocks when trying to relocate the assignments of the data or log files with the system BDs. Understandably, the system is quite restrictive about manipulating system files. If someone has established an instance of MSDE with sperate physical drive locations for the data and log files, I'd be interested in knowing how they did that.
Other than getting the model DB set up with the data files on one drive and log files on another (so that new USER DBs would follow that pattern), I'm not sure that there would be an advantage to this scheme anyway. I wouldn't think that the master DB data and log files get written to often enough to make a perfomance difference. If an application is creating SQL Server objects dynamically all the time, maybe; but not under normal use. I'd guess the same holds true for other system DBs.
Where it stands now, I can get all my user DBs set up the way I'd like; though I can't get the model DB to do this automattically for new user DBs. I can't get the system DB dat and log files on sperate drives, but that may not be important. I just wonder is MS set it up that way as a performance governor for MSDE?|||Brain,
You won't find much in Books Online about MSDE. MSDE, for all intents and purposes is just SQL Server without a number of the tools (Enterprise manager, etc), and 'hobbled' to certain database sizes etc.
You've got a few options to move your database files around:
1) detach and reattach with the files in the desired locations. This takes your DB offline for a few minutes while you make the change. Check out Books Online for sp_attach_db and sp_detach_db
(this one's a bit tricky/ugly).
2) ALTER the database to specify the creation of NEW files. Look into ALTER DATABASE (add and remove file options). This allows you do to everything while the db is up and running.. but can present some issues where you'll need to tidy things up.
3) Do a backup of your database, then restore it over itself with the files MOVED to where you want them. This might be the best option. And will look something like:
-- backup the db
BACKUP DATABASE myDatabaseName TO DISK = N'C:oink.bak'
-- create a device to 'wrap' your backup and details:
EXEC sp_addumpdevice 'disk', 'mydevice','C:\oink.BAK'
-- Now... just execute this:
RESTORE FILELISTONLY FROM mydevice
this shows you all of your logical file names as well as the actual paths to those files.
map those into something like the following:
RESTORE DATABASE
[myDatabase]
FROM
[mydevice]
WITH
RECOVERY, -- make it work when it comes back online
REPLACE, -- overwrite the existing database if it's there
STATS = 25,
MOVE 'logicalfilename' TO 'G:\SQLData\ProductCatalog_Data.MDF'
,MOVE 'logicalfilename' TO 'F:\SQLData\ProductCatalog_Log.LDF'
EXEC sp_dropdevice mydevice --,'delfile'
the logicalfilenames above correspond to the logical name returned in the resultset you get when you do RESTOREFILELISTONLY
hth
