Category Archives: SQL

Automated Backup of Bugzilla Database on Windows

Posted on by
1

Bugzilla isn’t really designed to be used on Windows, but once set up its fairly easy to administer.

One obviously important thing is backing up the database. Using the niftily built in mysqldump I used the following solution. Note this assumes default settings were used in the installation of Bugzilla.

First, add mysql to the windows path if it hasn’t been done so already, by running up a command prompt:

PATH=%PATH%;C:\Program Files\Bugzilla\mysql\bin\

I then use a command file to create the backup dump, and then append the date and time to the file name. Note I have also created a user (backup) on the bugzilla database with a limited set of privileges (Select, Lock Table, Show Databases, Event). 

rem commmand to dump the database to file
mysqldump -ubackup -pbackup Bugs > c:\bugsbackup\bz.sql

rem set date and time into useable file format
set _my_datetime=%date%_%time%
set _my_datetime=%_my_datetime: =_%
set _my_datetime=%_my_datetime::=%
set _my_datetime=%_my_datetime:/=_%
set _my_datetime=%_my_datetime:.=_%

rem rename file
ren "c:\bugsbackup\bz.sql" bz_%_my_datetime%.sql

This command file can of course be added to scheduled tasks and run as frequently as you want.

MSSQL – How To Output To A File

Posted on by
Reply

There is a very, very simple one liner within MySQL to dump the results of a query into a text file. Nothing so simple exists within MSSQL, but there are fairly easy workarounds. Using BCP, one can use the xp_cmdshell to pass the results into the desired file.

use <em>DatabaseName</em>
go

declare @FileName varchar(50)
declare @bcpCommand varchar(2000)

set @FileName = REPLACE('c:\temp\bcp\postcodes_'+CONVERT(char(8),GETDATE(),1)+'.csv','/','-')

set @bcpCommand = 'bcp "select left(postcode,4), count(*) from DatabaseName..TableName where customersequence = 0 group by left(postcode,4) order by count(*) desc" queryout "'
set @bcpCommand = @bcpCommand + @FileName + '" -U username -P password -c'

EXEC master..xp_cmdshell @bcpCommand

This dumps a load of postcode information, based on usage, into a csv file in c:\temp\bcp. What the query is doesn’t really matter, just the bcp commands to provide us with the results in a file of our choosing (.csv in this case).

The @FileName command simply appends the current date to the file in order to keep track of queries.

Defragging SQL indexes

Posted on by
Reply

Occasionally it will be necessary to defrag table indeces if you run SQL server. A useful little script.

USE dbname –Enter the name of the database you want to reindex

DECLARE @TblName varchar(255)
Declare @Indexname varchar(50)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE INdexCursor CURSOR FOR
SELECT     i.name AS IndexName
FROM         sysobjects o, sysindexes i
WHERE   (o.id = i.id and o.name = @tblName) AND (i.status = 18450 OR   i.status = 2097152)

OPEN IndexCursor

FETCH NEXT FROM INdexCursor INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN

DBCC INDEXDEFRAG (dbname, @tblname, @Indexname)

FETCH NEXT FROM INDexcursor INTO @Indexname
END

CLOSE IndexCursor

DEALLOCATE IndexCursor

FETCH NEXT FROM TableCursor INTO @TblName
END

CLOSE TableCursor

DEALLOCATE TableCursor