SQL Server doh!

OK, another stupid thing I did today.

I had to reinstall SQL Server 2000 on a machine that had been rebuilt and had the existing SQL database files on it.

The database files for master, distribution, and my user databases had been copied onto this new machine into E:\mssql\data\MSSQL\DATA, and I wanted to re-install SQL Server and use those existing databases as is, so that the replication I had already set up could just continue (the server name and everything else was the same for this new machine).

When I went to install SQL Server, I specified a folder for the data files: E:\mssql\Data, thinking that it would go in that folder.

No, in fact the SQL Server set appends \MSSQL\DATA to whatever folder you specify, and my existing database files were overwritten as they were in \mssql\data\mssql\data (that is, the system database files like master and distribution). Luckily I could ask the hardware guy who set up the new box for the disk image he had retained. He laughed at me. Oh well.

I could have simply attached the existing database files for the user database, but I would have had to recreate the publication and the two subscribers that I don't have direct access to would have had to reinitialize, and that would have been bad. And slow.

The moral of the story: always make more backups than you think you need.

Or maybe: the SQL Server Installer package could have made a backup of the database files it overwrote. Or notified me that it was going to overwrite them. Or something. Please, save me from my own stupidity.

I feel like such a User. :)

Mike