You can change server-wide default settings by using facets and properties at server level or by modifying the model system database (which acts as a template for new databases created).
I don’t change much but here are the settings I do change to make life easier for myself.
Note: I fully understand that everything needs to be considered on a case by case basis – I’m just presenting these as possible ideas.
#1 – Backup locations – (In SSMS object explorer, right-click the server >> Facets >> Backup Directory)
When configuring my backup procedures, I like to setup two folders on each database server such as the below;
E:\SQL_Backups_Nightly\ | Used with nightly backup maintenance job.
E:\SQL_Backups_Adhoc\ | Used for manual backups/restores.
I then setup the latter as my default backup directory in the server facets pane.
This means that when I do manual backups/restores the dialog box will take me straight to this folder. I find that when restoring a database under pressure, trying to navigate to the default paths (which usually look like below) just adds unnecessary confusion.
\\Servername\e$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup
#2 – File Locations – (In SSMS object explorer, right-click the server >> Properties)
This is similar to the backup locations above. In Database Settings under Database Default Location I use the following values.
Again, they are just easier to find and if you ever have to write MOVE statements and such like, this will make it a lot easier.
For the record, it’s a good idea to have your log files on a separate physical disk to your data files, please see this article for a full explanation.
Continue reading →