Get list of data locations for SQL databases

Select name, physical_name from sys.master_files

Rename a SQL server

Check the server name:

Select @@ServerName

For a server with a default instance:

sp_dropserver 'old_name'

GO

sp_addserver 'new_name', 'local'

GO

For a server with a named instance:

sp_dropserver <'old_name\instancename'>

GO

sp_addserver <'new_name\instancename'>, local

GO

Restart the SQL Server instance

List of SQL Server versions

http://www.sqlteam.com/article/sql-server-versions

Linked Servers in SQL

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

SQL Server log files

To view log files for SQL Server, the default location is Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

Moving data directories in SQL Server 2005

If you ever need to move the data directories in SQL Server 2005 after the install, here's how:

1. to set up a new location for new DBs only, open SQL Server Management Studio (SSMS) --> rightclick the instance --> Properties --> Database Settings --> change the default data and log directories.
2. for any user DBs already created, you can use 'detach and attach' in SSMS - http://msdn.microsoft.com/en-us/library/ms187858(v=SQL.90).aspx
3. for system databases, it's a bit more tricky but at least it's well documented, try this: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx

Easy!

Supply alternate Windows credentials to SSMS

If you need to log into SQL Server Management Studio with a different set of credentials to the one you are already logged into, it is possible to run SSMS from a CMD prompt with a different username:

>runas /user:DOMAIN\USERNAME “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe”

Type in the associated password when it prompts you and voila!

SQL Server install and File & Folder Compression

If you need to install SQL Server on a volume and have enabled File & Folder compression on it, the installer will not allow you to continue as a 'compressed volume does not guarantee sector-aligned writes which is needed to guarantee transactional recovery in some circumstances.' Got that? Good.

http://support.microsoft.com/kb/231347