Tech News

Those of you looking for a simple backup setup for your SQL server 2005 databases are in luck! I’ve stumbled across this script, and modified it to work for me in my environment.

I am not suggesting that your entire SQL server backup strategy should be to create backup files from your databases nightly. However, a nightly backup scripted to an alternate location is absolutely a crucial component of your SQL server backup strategy.

Use SQL Server Agent to create and schedule a job containing the following code as the command portion of the only step the job will contain:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
– ONLINE
s_mf.state = 0

– Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

– Not master, tempdb or model
and db_name(s_mf.database_id) not in (’Master’,'tempdb’,'model’)
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ‘ – ‘ +
replace(replace(@DBName,’:',’_'),’\',’_')

exec (’BACKUP DATABASE [' + @DBName + '] TO DISK = N”c:\db backup\’ +
@DBFileName + ”’ WITH NOFORMAT, INIT, NAME = N”’ +
@DBName + ‘-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 100′)

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Edit the line which contains c:\db backup\ to point at the location where you would like the database backup files to be deposited.

Modify the line with datename(dw, getdate()) in it to change the output file name of your database backups. If you leave the above script unchanged, your database backup files will be named: Monday – dbname.bak, Tuesday – dbname.back etc. In my case, I chose to leave this naming convention for one particular reason. I schedule the SQL Server Agent job to run a few hours before I schedule a task in Windows to execute a batch file which copies the contents of my backup directory to a network file server. By keeping all of my files named using days of the week as the only delimiting factor, I know that my file server will never have more than 7 days worth of data saved onto it (since every Monday will overwrite every past Monday, and every Tuesday will overwrite every past Tuesday, etc).

2 Comments »

Starting my 3rd major IT job in the last 5 years, I find myself talking companies into investing into tools that I use on a regular basis time and time again.  Naturally, when I found out that my new employer did not have an imaging solution in place for me to use as a safety net prior to reconfiguring some critical server componants, I decided to go shopping.  This time however, what I found had a $0.00 price tag.

Clonezilla is available in two editions, Clonezilla live, and Clonezilla server.  Live is suitable for single machine backup and restores, and is completely self contained into one linux based bootable cd.  Clonezilla server edition can be used to clone multiple machines simutaneously (similar to Ghostcasts from Norton and Symantec’s products).

In my test environment, I was able to use Clonezilla server to image 4 machines at once (using multicast) with an image size of approx 6 GB.  The entire process took just over 12 minutes.

Check this great product (did I say that it was FREE?) here.

Features of Clonezilla

• Free (GPL) Software.
• Filesystem supported: ext2, ext3, reiserfs, xfs, jfs of GNU/Linux, and FAT, NTFS of MS Windows. Therefore you can clone GNU/Linux or MS windows. For these file systems, only used blocks in partition are saved and restored. For unsupported file system, sector-to-sector copy is done by dd in Clonezilla.
• LVM2 (LVM version 1 is not) under GNU/Linux is supported.
• Multicast is supported in Clonezilla server edition, which is suitable for massively clone. You can also remotely use it to save or restore a bunch of computers if PXE and Wake-on-LAN are supported in your clients.
• Based on Partimage, ntfsclone and dd to clone partition. However, clonezilla, containing some other programs, can save and restore not only partitions, but also a whole disk.
• By using another free software drbl-winroll, which is also developed by us, the hostname, group, and SID of cloned MS windows machine can be automatically changed.

3 Comments »