Developing a site without backing up your work is like skydiving without a parachute. With Visual SourceSafe and task scheduling software, you can automate your code check-in process even when you're not around.

Every office has a horror story about a developer who lost several days worth of work because their workstation crashed. But this harrowing experience won't happen to you, right? You back up your development work every day using a tool like Visual SourceSafe™. (If you don't, then you definitely want to look into it.) But even if you do employ a source code library tool, you still have to manually check in or out the files you are developing. Wouldn't it be nice if every night all of your source code were checked in and back out of Visual SourceSafe for you? Then you wouldn't have to worry about remembering to do it yourself each night before you go home.       You can use the technique discussed here to automatically check source code in and back out every night of the week, refreshing the current project image in the version control system. My code can be morphed into many variations to check files in, out, or both in and out, and even choose which files get checked in and out. I will leave the customization to your imagination, as the possibilities are numerous. I'll show you how to automatically schedule a Visual Basic®-based program that generates object creation scripts for a SQL Server™ database and then checks them in and out of Visual SourceSafe automatically. I'll be using a few neat technologies here such as the SQL Distributed Management Objects (SQL-DMO) in SQL Server, the Visual SourceSafe object library, and the Windows NT® Schedule service's AT command.       First, let's discuss what this technique does for you, then I'll break it down into three main components: SQL Server database scripting code SourceSafe check-in/check-out code EXE Scheduling through the Windows NT Schedule service       So what do you need to build it? You need Visual Basic locally, a Visual SourceSafe-based database on your network, and a SQL Server database on your network. Also, you need a scheduling utility such as the Schedule service that comes with Windows NT or the SQL Server Agent. Visual SourceSafe Saves the Day       You might be familiar with Visual SourceSafe and its capabilities for controlling versions of source code, but did you know that you can programmatically manage Visual SourceSafe from Visual Basic? That's right—Visual SourceSafe has a COM interface that you can tap into from Visual Basic. In fact, you could write a Visual Basic-based application that emulates what Visual SourceSafe does through the Visual SourceSafe interface! You probably won't ever need to go that far, but the point is that it can be done. Visual SourceSafe is a great tool, but like any great tool, it only works if a developer remembers to use it. It can't automatically check files in and out for you. That is, unless you write a program to make it do just that.       Before taking a look at the code, let me tell you exactly what I'm trying to accomplish in this code sample. During the development cycle, developers often need to make changes to the database structure. But what do you do if you make several changes and then realize that you went along the wrong route? This is not a problem if you have saved your database scripts in Visual SourceSafe the night before. I'll show how you can schedule a regular backup of these database script files every night. I'll use the database scripts as the basis of my discussion, but you could expand this concept by checking in and out all of your Visual Basic, ASP, HTML, INI, and other source code files. Diving In       The first thing you need to do is create a Visual Basic-based application with no forms and a single code module. You won't need any forms because there won't be an interface for a user to interact with. After all, you are trying to check all of your code in and out automatically, without any user interaction. So when you open the Visual Basic-based project, you can remove the default form from the project and add a standard module. You could also use a class and create an ActiveX® server framework from the program, but in this case it wouldn't be as useful as simply creating an EXE that will run every night unattended.       By the way, now is as good a time as any to set the name of the module and project and save them on your workstation. I named my project and module DatabaseScripting; you can name them whatever you deem appropriate. Scripting the Database with SQL-DMO       What is SQL-DMO, and why do you need to use it? SQL-DMO is an ActiveX-based object interface to SQL Server that allows you to manage SQL Server programmatically. You'll need to tap into its features to script the SQL Server database through Visual Basic. The first step in this process is to reference its object library so you can manipulate it through its ActiveX interface in the standard code module. Do this by choosing the Project menu item from the Visual Basic main menu, then choosing the References menu item. The project's current and all available ActiveX references should appear. Find the Microsoft® SQLOLE Object Library in the list and check it as shown in Figure 1.

Figure 1: SQLOLE Object Library

Keep in mind that the SQL-DMO object library is actually called SQLOLE in the references list. These are just different names for the same object library. After you select the SQL-DMO object library, click the OK button. Now that you have linked to the SQL-DMO object library, set up some module-level constants and variables to accomplish the scripting goal.

Option Explicit

Private mobjDatabase As SQLOLE.Database

'——————————————————————————— '— SQL Server constants '——————————————————————————— '— The name of the SQL Server Public Const cSQLServer = "PAPANOTEBOOK" '— The SQL Server User ID/Password. Public Const cSQLServerUserName As String = "sa" Public Const cSQLServerPassword As String = "" '— The name of the database. Public Const cDatabaseName As String = "pubs" '— The scripting path for the database files. Public Const cScriptingPath As String = "c:\PubsScripts\"

This module-level code is pretty well documented, but let's go through it briefly anyway. Remember that you are going to manage SQL Server from Visual Basic. To do that, you need to know the name of the SQL Server (cSQLServer), the name of the database you want to script (cDatabaseName), and the user ID (cSQLServerUserID) and password (cSQLServerPassword) of a valid user. That gives you enough information to know which SQL Server you want to manipulate, but you also need to know where to put the script files (cScriptingPath). Nope, I didn't forget the variable mobjDatabase, which will be used to represent the pubs database throughout the code. Armed with this information, you are ready to proceed with the main scripting code. Connecting to SQL Server       When the Visual Basic project starts, the first section of code it will run is the Main subroutine. That is where you should begin your coding efforts by inserting a call to the main database scripting routine.

Public Sub Main() '——————————————————————————— '— Script all of the main database objects to '— separate files using SQL-DMO. '——————————————————————————— ScriptAllObjects End Sub

Later, you can insert more code to check files in and out of Visual SourceSafe, too.       Next, code the ScriptAllObjects subroutine as shown in Figure 2. First, declare and then create a new instance of the SQLOLE.SQLServer object so you can interface with the SQL-DMO object library. And since you cannot manage SQL Server if its service isn't started, check to see if it is. You can do this by evaluating the Status method of the SQLOLE.SQLServer object. If it is not running, issue the Start method and pass the name of the SQL Server and a valid user name and password combination to it. This starts the SQL Server service and then connects to it. If the service was already running, then issue the Connect method to connect to the SQL Server instance.       Now that you are connected to the machine running SQL Server, set your module-level variable (mobjDatabase) to your project's database (I'm using the pubs database as an example). Create the path where you will store the database scripting files by calling the CreatePath subroutine (which I'll cover later). Once the path is created, script the database objects by calling the various scripting routines. Finally, wrap up the scripting code by closing the SQL Server connection and destroying the object reference.       Let's go over some of the subroutines I skipped, starting with CreatePath.

Private Sub CreatePath() On Error Resume Next MkDir cScriptingPath End Sub

This routine's sole purpose is to create the path where you will store your database scripting files if it doesn't already exist. Here, I use the On Error Resume Next statement to ignore the error if the path already exists. The Dirty Work       Getting back to the meat and potatoes of the scripting code, let's start with a review of the ScriptTables subroutine (shown in Figure 3). First, you open a file called Tables.txt in the path just created. You'll write all of the DROP TABLE, CREATE TABLE, and CREATE TRIGGER SQL to this file. To do this, loop through all of the tables in the SQL-DMO pubs database object. Then script the tables' SQL one by one using the Script method, and save the SQL to the Tables.txt file using the Print statement.       As you can see, this code is rather simple and representative of the entire SQL-DMO object library. The remaining scripting routines are shown in Figure 4 and are very similar to the ScriptTables routine. Connecting to Visual SourceSafe       Now that you have all of the code to script the database objects to separate files, you'll need to save those files in Visual SourceSafe. Let's go back to the Main subroutine and add the stub to call the code that will check the files in and out of Visual SourceSafe. Here's the call to the CheckProjectInAndOut subroutine:

Public Sub Main() '——————————————————————————— '— Script all of the main database objects to '— separate files using SQL-DMO. '——————————————————————————— ScriptAllObjects '——————————————————————————— '— Check all of the database script files in and '— then back out of SourceSafe. '——————————————————————————— CheckProjectInAndOut End Sub

Before getting into the code for the CheckProjectInAndOut subroutine, there are two steps you must take: Make a reference to the Visual SourceSafe Object Library, like you did with the Microsoft SQLOLE Object Library. Establish the module-level constants that you will use to interact with Visual SourceSafe (like you did with the SQL Server scripting code). Make the reference first by choosing the Project menu item from the Visual Basic main menu, and then choosing the References menu item. The project's current and available ActiveX references should appear. Find the Microsoft Visual SourceSafe 6.0 Type Library in the list and check its checkbox, as shown in Figure 5. This allows you to take control of Visual SourceSafe from your Visual Basic code.       Now go back to the general declarations section of the code and add the module-level constants.

Figure 5: SourceSafe Type Library

'——————————————————————————— '— SourceSafe constants '——————————————————————————— '— The SourceSafe root project. Public Const cVSSRootProject As String = "$/" '— The SourceSafe database project. Public Const cVSSSubProject As String = "PubsScripts/" '— The SourceSafe INI file. Public Const cSourceSafeINI As String = "d:\program files \microsoft visual studio\common\vss\srcsafe.ini" '— The SourceSafe User ID/Password. Public Const cVSSUserName As String = "johnny" Public Const cVSSPassword As String = ""

First, declare the Visual SourceSafe root project (cVSSRootProject), which is always defined as $/. Then create a constant (cVSSSubProject) to represent the project where you will store the database scripts. Create a constant (cSourceSafeINI) to point to the Visual SourceSafe .ini file. This is important because the object library uses it to know where the Visual SourceSafe database resides. On your PC it is most likely found in C:\Program Files\Microsoft Visual Studio\ Common\Vss. Finally, define the user name (cVSSUserName) and password (cVSSPassword) that you will use to connect to the Visual SourceSafe database. Checking Code In and Out       Now let's take a look at the code that manages source code control. The first step in the source code control routine (shown in Figure 6) is to create an instance of the Visual SourceSafe database (SourceSafeTypeLib.VSSDatabase). Then open the connection to the Visual SourceSafe database by passing the .ini file's path, the user name, and the password to the Open method of the Visual SourceSafe database object.       Once you make the connection to the Visual SourceSafe database, set an object reference to the Visual SourceSafe project where you want to store the script files. Notice the On Error Resume Next statement in the middle of Figure 6; I do this in case the project does not yet exist. I wrote this code so that you do not have to set up the project in Visual SourceSafe first. This code will create the project for you if this is the first time you run it.       Once the object reference has been made, check to see if an error occurred. If one did occur, then you know that the project does not exist. You create the project in Visual SourceSafe using the NewSubProject method of the Visual SourceSafe database object. Then you have to make the reference to the project, as this was the whole reason you got into this code block. Next, add the scripting files to the project you just created in Visual SourceSafe.       Finally, turn error handling back on and check the scripting files into SourceSafe to save the current version, then check them back out again so you can manipulate them on your workstation. And don't forget to destroy your object references when you are done.       Now you need to make an executable out of the project. I called mine DatabaseScripting.exe. Schedule Your Scripts       The next step is automating the process by scheduling the executable file for regular execution. I'll use the Windows NT Schedule service, but you could use any scheduling utility, such as the SQL Server Agent. First, start the Schedule service. Then tell it to start automatically when the computer is booted. You can do both of these tasks through the Control Panel Services. Figure 7 shows the Schedule service set to start automatically.

Figure 7: Automatic Scheduling

Now go to a command prompt and schedule the DatabaseScripting.exe. Let's make it run every night at 11:00 pm. Simply enter the following command into your command window, making sure you substitute your computer's name and the correct path to your executable file where appropriate:

at \\YourComputerName 23:00 /every:m,t,w,th,f c:\temp\databasescripting.exe

This line of code schedules DatabaseScripting.exe to run on the PC named YourComputerName every weeknight at 11:00 pm. To check your schedule, run the AT command by itself. Your results should look similar to Figure 8.

Figure 8: Running the Backup Automatically

Where to Go from Here       That's it! You've just scheduled your database to be scripted to several files, and saved to Visual SourceSafe every night at 11:00 pm. I use this quite often to save my database scripts in addition to regular database backups. Another thing you can easily do is check in all of your Visual Basic code at night. This is often a good idea on multiple-developer projects. That way, all of your code is checked in at night and available to whoever checks it out first in the morning. This is especially helpful when a developer checks out some files and then is sick the next day. If the scheduling service ran the program the night before, everything will be checked in and available for other developers.       Another idea would be to recreate the Visual SourceSafe environment from Visual Basic. Actually, the source code for this idea is already on the Microsoft Web site. You can find and download it from the Visual SourceSafe Web site (http://msdn.microsoft.com/ library/devprods/vs6/ssafe/ssusexp/vssstartpage.htm). It is a great tool to use as a template for writing against the Visual SourceSafe object library.       Finally, one of the greatest features of the Visual SourceSafe object library is its ability to notify you when a file is checked back into the system. Imagine that you are waiting for a file that is checked out by another developer. You can programmatically tell Visual SourceSafe to notify you when it is checked back in. This can save a lot of time and trouble! Check out http://msdn.microsoft.com/library/techart/vssauto.htm for more information on trapping Visual SourceSafe events.       To wrap up, I've shown you how to traverse the SQL-DMO object library, script SQL to text files, create projects in Visual SourceSafe through code, check files in and out, and schedule commands with the Windows NT Schedule service. The code presented here is included in the code download (3KB), so feel free to explore its possibilities.

http://msdn.microsoft.com/library/devprods/vs6/ssafe/ssusexp/ssugegetting_started.htm and http://msdn.microsoft.com/library/psdk/sql/bkprst.htm

From the September 1999 issue of Microsoft Internet Developer.