Monday, August 21, 2006

What your Browser reveals about you

Here
I use a mix of Safari, Firefox 1.5.0.x, Camino, IE 6.0 and Opera, I would try and use Camino normally but sometimes it breaks web pages and I have to open them in Safari/Firefox/Opera. I only use IE when absolutely necessary for testing page compatibility and I had IE 7 installed and took it out because it hurt my eyes. Think Opera V4 with bad icons....

Tuesday, August 08, 2006

Automating SQL 2005 Express Edition Backups

SQL2005 Express Edition w/ Management Studio Express looks like it is a very nice DB and management console, it falls down in one place (that I can see) and that is it is missing the maintenance Wizard that was found on SQL 2000.

Now I cant complain as it is a free application and a small stored procedure can restore most of the functionality as regards backups.

Not all the procedures that are required are activated by default and it is very simple to re-activate them. simply copy and paste this code into a text editor and save it as c:/enableprocs.sql

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now execute this command from a "run" prompt
sqlcmd -S .\SQLExpress -i c:\enableprocs.sql

this enables the Ole Automation Procedures and the XP_CMDshell.

Get the expressmaint.sql script here

extract the script and save the .sql file to c:\expressmaint.sql

execute this command

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

This installs the expressmaint stored procedure which is necessary for this backup method.

PARAMETERS

@database Y NONE
The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases and ALL_SYSTEM which will process all system databases
@optype Y NONE

The type of maintenance operation to be performed. Valid values are

  • DB - Full Database Backup
  • DIFF - Differential Database Backup
  • LOG - Log Backup
  • CHECKDB - Database Integrity Check
  • REINDEX - Rebuild all indexes
  • REORG - Reorganize all indexes
@backupwith N NULL
Specify additional backup options as documented in BOL for the BACKUP WITH command
@backupfldr N NULL
The base folder to write the backups to. Sub folders will be created for each database
@verify N 1
Indicates whether to verify the backup file.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@verifywith N NULL
Specify additional verify options as documented in BOL for the VERIFY WITH command
@dbretainunit N NULL
The unit of measure for the @dbretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old backup files are kept
@dbretainval N 1
The time period or number of copies of old backups to keep
@report N 1
Indicates whether to produce a report of the maintenance carried out.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@reportfldr N NULL
The folder where maintenance reports are written to if @report = 1
@rptretainunit N NULL
The unit of measure for the @rptretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old reports are kept
@rptretainval N 1
The time period or number of copies of old reports to keep
@checkattrib N 0
Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent deletion of files that have not been backed up onto tape.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@delfirst N 0
Indicates whether to delete old backups prior to doing the current backup. This is not advisable but can be useful if disk space is limited.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@debug N 0
Indicates whether print out debug information such as the commands generated and the contents of the temporary tables used in the procedure.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
This is the script that I use to backup all user databases, save it as  fullbackup.sql in c:\
exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'd:\backups',
@reportfldr = 'd:\reports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 1,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

If you look at the parameters you will see that it backups all user databases, it puts the backups in d:/backups and the reports in d:/reports
it also retains reports and backups for 1 week.

Then just set up a scheduled task in windows to run
sqlcmd -S .\SQLExpress -i c:\fullbackup.sql
on whatever schedule you like. I recommend daily backups if not every 12hrs.