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 »

Quite a few people have asked me how to configure a secure means of transferring backup data. This tutorial outlines how to set up a Secure-FTP server using OpenSSH to encrypt the data with Windows 2003 Server, and how to set up a remote location to automatically upload backup data to the Secure-FTP server. The following method will work for any type of data.

Required:

• OpenSSH for Windows
• SCP Client, I will use WINSCP in this demonstration

Background:

1. Our Secure-FTP server will be set up on a Windows 2003 Server.
2. Our remote client who will send back up data to the Secure-FTP Server will be set up on a Windows XP workstation.

Our first step is to set up the Windows 2003 Server as a Secure-FTP server by downloading and installing OpenSSH for Windows.

1. Install the latest binary for Windows from the OpenSSH for Windows website. Be sure to install both the server and client components.
2. Configure the SSH server.
   1. Open a command prompt window and navigate to c:\program files\openssh
   2. Create a group file
      1. mkgroup –l >> .\etc\group
   3. Add users and create a passwd file
      1. First create any user accounts locally on the Windows Server via computer management. They do not need to be members of the Administrators group, default group membership is fine. Once you’ve created the accounts locally, adding the account names to the passwd file will enable them to log on using SSH. Do so by entering the following command for each user you would like to add to the passwd file.
      2. mkpasswd –l –u username > > .\etc\passwd
         Remember: the username above must be an existing windows login account.
   4. Create home directories for your user
      1. In the passwd file, you will notice that the user’s home directory is set as /home/username. Change the location by editing the passwd file with notepad or any other text editor. Only edit the second last entry (/home/username), by replacing it with any directory that you wish to act as that user’s home directory, using the following syntax:
         1. /cygdrive/c/path_to_the_folder
            *cygdrive/c represents the c drive
            **c:\temp\ would be /cygdrive/c/temp
         2. ***c:\home\doe_j would be /cygdrive/c/home/doe_j
   5. Reboot the server, and verify that the OpenSSH service is running.

Our second step is to set up Secure-FTP client software at the remote location, and automate the backup job. The steps below outline this process.

1. Download and install WINSCP from the WINSCP website.
2. Create an account on the Windows XP Workstation with Administrative privileges that a scheduled job can run as.
3. Log into the Workstation as this account (you will only need to do this once).
4. Create a stored session using the IP, port, username and password assigned for access to the Secure-FTP Server. This step is required to cache information about the connection on the client’s workstation. Without it, you cannot script automatic jobs with WINSCP to your Secure-FTP server. Connect to your stored session, then disconnect, and close WINSCP.
5. Write your batch file to automate connecting / uploading backup data to your server.
   1. winscp /console /script=backup.txt
6. Create backup.txt, and place it in the same directory as your batch file. backup.txt is outlined in the final section of this document.
7. Schedule your batch file to run using Windows task scheduler, as the account you’ve created to run WINSCP jobs, at the desired times and frequencies.

The third and final step is to create the text file mentioned above, which will be called upon in the batch file scheduled to run automatically, and upload data from the client to the Secure-FTP server. This is merely an example of what you can do with WINSCP, more detailed documentation can be found on the WINSCP website.

Backup.txt

*Copy and paste the following code into backup.txt

# Automatically answer all prompts negatively not to stall
# the script on errors
option batch on
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect using the username and password set up on the Secure-FTP Server, to the address of the Secure-FTP Server
# open user:password@example.com
open username:password@192.168.0.1
# Force binary mode transfer
option transfer binary
# Upload the backup data to current working directory
put c:\example\examplefile.txt
# Disconnect
close
# Exit WinSCP
exit
*End Code, do not copy and paste this line.

The Backup.txt script file is self explanatory. Insert the username and password of the account created on the Secure-FTP Server, and the address of the Secure-FTP Server. Also edit the path near the end of the script, to the local path and file name of the data you wish to upload from the WINSCP client. The script will open WINSCP, connect to the specified Secure-FTP Server, upload the data specified, disconnect, and close WINSCP.

5 Comments »