jwcolby
jwcolby at colbyconsulting.com
Thu Mar 11 14:32:33 CST 2010
I spent many hours searching for a free SQL Server backup tool (Idera) and writing stored procedures
to automatically back up all of my databases, mount them, unmount them etc., from C#.
Then I updated to SQL Server 2008 and it all broke. The Idera Free backup was a DLL and apparently
it is simply incompatible with SQL Server 2008. Sigh.
Luckily my other server still has 2005 installed so... I need to get all of my backups restored to
2005 so that they are available in case I need them, then write them back out using 2008 and its
wonderful compression. I have written C# code to do that, so once I get the backups restored I
should be able to re-backup using the latest and greatest and don't even need the Idera Free backup
- which is a good thing since it has since gone away entirely.
Lesson learned - free is only free until it's not.
So, I had never automated restoring the lot of backups in a directory. Never needed to. However
the Idera Free restore does that and I have that working on the 2005 server so... all I need is a
method of reading the files in a directory.
I go a googlin' and what pops up but Phil Factor. I LOVE Phil Factor to begin with. Just a cool
guy. Anyway within a few minutes I discover a largeish piece of code that looks like it will work.
Except that it needs the OLEDB stuff turned on in surface area config. Except that I've shut
down all of the extraneous SQL Server services (because this server normally just hosts my VMs). I
finally get the right service started, the OLEDB stuff enabled, turn off / on all the services one
last time and voila, TSQL code that hands me a recordset of file names in a query.
And on, and on, and on. With luck, before I go to bed tonight, I will have restored my backups to
the alternate server.
I'm a wondering now... can I backup a database on server A running SQl Server 2005 from Server B
running 2008? I guess I'll find out.
How to waste a day.
Ahhhoooooommmmm this is what I do for a living...
Ahhhoooooommmmm this is what I do for a living...
Ahhhoooooommmmm this is what I do for a living...
--
John W. Colby
www.ColbyConsulting.com