Backing up all SQL Server user databases to file

The ultimate lazy backup script. I wasn’t feeling to selective about which databases to backup, and I figured, it’d be best to back them all up, even that testing database I¬†occasionally¬†use for data import/export. Never know when I might need it again…

This sits inside a SQL Server Agent job, and runs nightly. Simple enough really.

All works as expected on SQL Server 2012.

DECLARE @Database varchar(max)
DECLARE @path varchar(max)
SET @path = concat(N'D:\BACKUP\',convert(varchar,getdate(),112),' ALL DATABASES BACKUP.bak')
DECLARE my_cursor CURSOR FOR
select name from sys.databases where database_id>4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BACKUP DATABASE @Database TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'SQL Server all user databases backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM my_cursor
INTO @Database
END
CLOSE my_cursor
DEALLOCATE my_cursor

I’m sure there’s a better way to do this, but there doesn’t appear to be a column in the sys.databases table which denotes a database as a system or user database. Instead, I’ve used the WHERE database_id > 4 – which removes master, model, msdb, and tempdb from the backup.

Server Recovery with Acronis

My boss forwarded me some emails from one of our client’s servers, dead disk, the usual. Get to the server, and it’s got nothing on the screen, typical… Reboot, no logical disks configured on the pov Promise RAID controller… Great.

Luckily for us, we run Acronis TrueImage on this client’s servers, which practically does a full disk image of the hard drives in the server. Again, luckily, because I don’t have to rebuild the server from scratch. YAY.

Paul, my boss, made the smart decision a while ago to install an Adaptec 1420SA RAID controller in this server, so hopefully, we won’t have any issues – I trust Adaptec, as I run a 2420SA at home with a RAID 10 array, and have never had any issues in the past 2 years or so of it running.

Getting to it, the process is simple. The server has an additional, non-RAID disk, which the Acronis images are stored on. Whack in the Acronis True Image Echo Server Universal Restore Boot CD (yes it’s a mouthful), and it all loads up nice and quick and we can begin the restore. Here’s where I am stuck, because, even though Acronis sees the RAID array, the universal restore still needs drivers, so Windows can see the array when it attempts to boot up. The problem here, is that I’m lazy, and I’m on my Mac, and Adaptec’s drivers are .exe’s… Luckily, I have VMWare with Windows 7 RC on it… Burn these drivers to a CD and we’re back up and running again.

The Acronis restore procedure is running now, around 40 minutes to restore some 50GB of data…

— edit, now 5 hours later —

This all worked nicely… NOT. I’ve been stuck in this little closet near on 7 hours now… I’ve been abused, and been told by a podiatrist how to do my job, and am totally over it. The nice bones in my body are still pushing through to get these guys *cough* idiots *cough* back online, so I’ll continue.

We keep restoring the image to the RAID array, probably done it about 5 times now… Everytime we’ve restored, we’ve received either a “missing NTLDR” or “disk read error” message after the POST screen disappears and it’s searching for operating systems… We’ve tried everything – resetting the BIOS, different partition sizes, different RAID controllers, and completely wiping the disks / formatting them… We’re at wits end… Last chance, or we’re blowing it away… This was supposed to be a nice little article about Acronis and how cool it is, but clearly, I’m not pushing that message anymore. I still love it, and it’s not to blame for what’s happening here, but I’m still going to hold it somewhat responsible…

We have an Adaptec 1420SA RAID controller installed in this server – Acronis sees the array on this perfectly fine, and we can restore to it, perfectly fine… Except, when you go to boot, it says ‘disk read error’ – we suspect this is because of the partition size. Loading the Windows Server install disk, we attempt to load the drivers for the card, but it doesn’t even let us – it sees the Promise onboard RAID controller, but refuses to acknowledge the presence of the Adaptec… I’m not sure why…

I’m totally over it. This is my last update on this story. Let’s just pretend it all worked, and come tomorrow, I’ll post something else that’s somewhat interesting.