Thursday, April 26, 2007

VB.Net String Manipulation

I was trying to cut the fixed length string to the given specification thought I will post this Vb code as a sample code snippet on my blog.

The output would look as shown below

Database Backup Solution

As I said in one of my articles, we usually work from remote and don't have a common database server for our development purposes. So one day what happened was one of my developer was doing some db object changes, like almost 20-25 changes. However after he did his changes he didn't backup it up (not a DBA). Next day he was supposed to send the script to me so that I make the same changes on my copy of database and distribute it to others so that every developer and myself are on the same version of database. But unfortunately one of his computer's disk crashed and he lost almost all development work. And database work was totally lost, nor we had any database backup to recover the work from. And most of the developers use SQL Express edition so they might not be well equiped with proper tool

Usually I have seen developers when working in organised manner they usually don't worry about the database server anyway its a DBA job to maintain it. But in the scenerio that I explained, developer is expected to do it himself and protect his work. However SQL Server does not allow physical file backup, so the next best option and the best practise is to backup the database using backup commands.

Here is a simple win batch file for the same,

/********** backup.bat **************************/

date/T >> BackupLog.txttime /T>> BackupLog.txt
osql -E -SSLYSLE-PC\GP_SQL2K5 -Q"BACKUP DATABASE TestDB TO DISK = 'C:\Girish_Patil\DB_Backup\DB_Backup_%random%.BAK'" -w4000 >> BackupLog.txt
time /T>> BackupLog.txtdate/T >> BackupLog.txt
echo "X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~" >> BackupLog.txt


The parameters are hardcoded, you can modify them as per your requirements. This script logs all the message to BackupLog.txt file if you want to analyze anytime if something goes wrong. I use %random% variable to generate random number and concatenate with backup file name so that there is no overwriting of backup files or any file name conflicts. Now, to automate it further just using Windows Scheduler to run this backup.bat file as scheduled job. Thats it your done with simple database backup solution that meets your purpose.

Wednesday, April 25, 2007

Database deployment - How to ?

I was working on one of the projects where I was doing database development and another two guys were working on .Net development. We did't had common DEV server to which we connect and do the work. So in such situation obviously I will have to give a new version of database to each of these developers. So obvious next way is to backup and restore, but I will have to teach the developer how to do it. So I came-up with new option and i.e. batch script. Below is the content of the batch file.

/****** RestoreDB.bat *********************/

::CD "C:\Girish_Patil\Deployment\Restore"
::restoreDB Ultra_V2 C:\Girish_Patil\Deployment\Restore\DB_Backup.BAK C:\Girish_Patil\Deployment\Restore SLYSLE-PC\GP_SQL2K5
echo off

if "%1"=="" goto usage

if "%2"=="" goto usage
if "%3"=="" goto usage
echo.echo Database is being restored pls. wait..

echo =================================

osql -E -S%4 -Q"if exists(select name from master..sysdatabases where name = '[%1]') drop database [%1]" -w4000 >> RestoreLog.txt

osql -E -S%4 -Q"RESTORE DATABASE [%1] FROM DISK = N'%2' WITH FILE = 1, MOVE N'ULTRA' TO N'%3\%1_Data.mdf', MOVE N'ULTRA_log' TO N'%3\%1_Log.ldf', NOUNLOAD, REPLACE, STATS = 10" -w4000

echo =================================
echo [%1] Database is restored and is ready for use !!!
goto end

echo usage: RestoreDB DBName BkpFilePath DbPhysicalFilePath SrvName



I takes 4 parameter
1=Database name to restore as,
2=DB Backup file name with path,
3=Where do you want the new database files to be located,
4=SQL Server name, with instance name.

In my senario the SQL Server used to be running locally. So this script is ment to work smoothly as is on the local server. However if you want to restore it on remote server, in that case you need to place the DB Backup file on the remote server and provide the path local to it.

Pls. mail/contact me if you need any thing more specific.

Missed opportunity

Look at this link, it says I have won some prize, unfortunately I didn't log to this site for quiet a long period and thats how I miss something.


This is the second month of our new forum contest. Now, anyone who has posted at least once anytime during the month is elgible to win in a random drawing. Below are the winners for June 2005.

To claim your prize, you MUST CONTACT ME, using the e-mail account you used when you registered with the forum. Once I get your e-mail, I will make the arrangements for you to receive your gift. If you don't contact me within 30 days, then you loose out.
Below are the winners, selected at random, along with the gift they have won:
gvphubli: ApexSQL Doc ($249) from ApexSQL Software
billbobcu: SQL Scribe Documentation Builder ($400) from A&G Software
jastone: Book: In the Trenches with SQL Server & .NET: Advanced Techniques & Real World Solutions ($150)

James B UK: DbNetGrid ($599) from DBNetLink
ryanFX: SQLZip ($500) from SQLZip Software
snoopy: myLittleAdmin for SQL Server ($490) from myLittleTools
-----------------------------Brad M. McGehee, MVPWebmasterSQL-Server-Performance.Com


So, keep posting and helping others every alternate day

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.