Category Archives: SQL Scripting

Setting the SQL Server Campatibility mode to 90 on all databases

OK, so I know I’m jumping the gun a little, I’ll be posting some stuff about how I moved over my databases to the SQL 2005 box later. I’ve just been working on this stuff all morning so I thought I’d upload the script I wrote quickly.
This script creates as an output a number of SQL commands that will change the compatibility modes of all databases on your server. That said, if you change the command in the cursor, it can be used to change anything on all databases.
Basically, you just run the script, copy the outcome and run that. This will allow you to remove any databases you don’t want or change anything you like before running the eventual script.
The script genrating script idea was one I pinched from Microsoft I’m afraid.

Click Here to get the Code

Run it then in the results pane you’ll see all the commands ready to be copied and pasted into the query window.
Nice! 🙂

Finding out the paths to SQL Database Data Files

 

Finding out the data and log file locations in SQL Server can sometimes be a little fiddly. With that in mind, I’ve been looking for a way to list the files used by each database. Whether for system or disaster recovery documentation, it can sometimes be handy to have this kind of info available to you.

Finding out where the mdf (database file) is can be pretty easy. Its held in the Master database somewhere. Its only when you want to check where both the mdf and ldf (transaction log file) are stored that it can get a bit tricky.

 

The way I’ve been doing it involves a cursor that creates a new line in a temporary database for each database you have on that particular server. Ordinarily to get where the database and log files are kept you’d use a query like the one below:

EXEC sp_helpdb ‘master’

 

What I’ll be doing is something that works similar to this but basically does it for all the databases in one command.

Get the name of the first database

Query the database for its file locations, writing them to the temporary database

Get the name of the second database…..

Etc

 

So, here goes with the actual code: 
 

Click Here to get the Code 

 

I don’t have any databases that use more than the primary filegroup so I’m afraid this won’t help users that may be doing that.

For us simple people however, this will give you a report that can be used in disaster recovery docs and the like for years to come. 🙂