Question

How can I get a new FogBugz database?

How can I wipe the contents of my current FogBugz database?

How can I get an empty FogBugz database (no data rows) with the schema completely intact?

Answer 1

For FogBugz On Demand, (your FogBugz URL is something.fogbugz.com), you need to contact us to get your data reset.

Here's full steps for creating a MySQL schema. The youruser and yourpassword values can be gotten from your sConnectionString (see below):

CREATE DATABASE newfogbugz;
USE newfogbugz;
CREATE TABLE Version (ixVersion int);
GRANT ALL ON newfogbugz.* TO 'youruser'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL ON newfogbugz.* TO 'youruser'@'localhost' IDENTIFIED BY 'yourpassword';

Then, go into your registry and change the Database name in your sConnectionString.

When you refresh FogBugz in the interface, it'll tell you that the version is 0 and it needs to be some number north of 700. Click the OK link and it should create a full schema for you.

In SQL Server, the process is basically the same. If you need explicit directions, put a comment on this question and it'll prod me to give a more complete answer.

If you need a new, empty Access database, first, reconsider using Access. Second, go into your FogBugz install's accessories folder and look for EmptyBugz.mdb.

Answer 2

FogBugz will create its own schema if pointed at an empty database with one table in it:

CREATE TABLE Version (ixVersion int)

If FogBugz is pointed at such a database it will create all the necessary tables and fill them with default data.

Sometimes (particulary when you want to copy data into this table) you just want the schema and not all of the default rows that FogBugz needs to run (for instance default users, projects, areas, etc).

You can run this sql script against a database and it will remove all the data in the database, but keep the schema intact.  Please be careful and do not run this script against a FogBugz database with any data you want to keep.

truncate.sql

If your tables are not owned by 'dbo' when you move your FogBugz database you may find that the tables cannot be seen because of security restrictions.

This script will change the owner of all FogBugz tables to dbo.  (Replace fogbugz in the script with the name of the FogBugz user)

owner.sql

You may also be able to use the following code:

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+ 
ltrim(u.name) + '.' + ltrim(s.name) + '''''' <br />&nbsp; + ', @newowner = dbo'')'
FROM sysobjects s, 
sysusers u
WHERE s.uid = u.uid AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name not like 'INFORMATION%'
order by s.name

For MySQL, here's a script that will spit out truncate statements for all tables in a given database:

SET @new_database = 'fogbugz1';


SELECT CONCAT(
  "SELECT '",TABLE_NAME, "'; ",
  " TRUNCATE ",@new_database,".", TABLE_NAME,";") AS Statement 
FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = @new_database 
    GROUP BY TABLE_NAME