Adding Database Objects to Visual SourceSafe: Traditional Approach

Unfortunately, if you do not have Visual Studio .NET, it is not easy enough to manage the code of individual database objects with only the tools built into SQL Server and Visual SourceSafe. The process involves two steps:

1. Generate scripts from SQL Server.

2. Check in files to Visual SourceSafe.

Creating Scripts Using Script Wizard

You can use Management Studio to generate Create scripts of all objects in a database:

1. In Object Explorer, navigate to a database.

2. Open the context-sensitive menu and choose Tasks | Generate Scripts (not Script Database As | Create To).

3. The program will open the Generate SQL Server Script Wizard.

4. In the second step, select a database.

5. In the next step, you can set the options for the script generation:

   

6. On the next screen, you need to select the object types to be generated:

   

7. The program will then prompt you to select individual objects of every type that you have selected on the previous screen:

   

   Note 

   The Script Wizard in SQL Server 2005 supports CLR database objects as well. It will create code for attaching assemblies to the database and prompt you to select managed database objects that are based on them.

8. Finally, the program will prompt you for the destination of the script:

Based on your choice the Script Wizard will store script in a Unicode file, an ANSI file, on the Clipboard, or in Management Studio's Query window.

Creating Scripts Using TbDbScript

I've also created a tool that loops through database objects and scripts them into separate files—TbDbScript. It's written in VBScript and you can download it from http://www.TrigonBlue.com/tsql.net/download.htm. To run it, you must use Windows Script Host and cscript.exe. Execute the following from the command prompt:

      cscript TbDbScript.vbs .\ss2k5 sa password c:\sql\ Asset5 

The parameters are the server, login, password, destination of database files, and, optionally, the database name. Use the space character as a parameter delimiter. If you omit the last parameter, the program will script all nonsystem databases on the server. When scripting is finished, you will find database objects in the set of Create scripts in the folder named after the database (see Figure 17-3).

Figure 17-3: Database object scripts generated by TbDbScript

The tool also creates deployment scripts. They contain Create scripts grouped by type. You will read more about them in the "Deployment Scripts: Traditional Approach" section, in the next chapter.

It is true that the Script Wizard will perform similar actions, but there are several significant differences:

• The script generates individual database object Create scripts and deployment scripts at the same time.

• You can also schedule usage of TbDbScript, which may be very useful when the development team is not using Visual SourceSafe religiously—as when the team is making changes live to the development database.

• Generation does not require user intervention and therefore is less prone to errors. The resulting files are always the same.

• Every deployment script file begins with a Use database_name statement and they can even be deployed manually using Management Studio.

Putting Scripts to Visual SourceSafe Using TbDir2Vss.vbs

Now that Create scripts and deployment scripts are created, all you need to do is check them into the Visual SourceSafe database. You can do this manually with Visual SourceSafe Explorer. Simply add the working folder in or do it automatically with a little VBScript tool—TbDir2Vss.vbs. You can download the tool from:

http://www.TrigonBlue.com/tsql.net/download.htm.

To run it, you must use Windows Script Host and cscript.exe. You need to specify the location of the srcsafe.ini file, username, password, Visual SourceSafe project folder, and local folder:

      cscript TbDir2Vss.vbs "C:\Program Files\Microsoft Visual      Studio\Common\VSS\srcsafe.ini" admin password $/Asset/ c:\dbscripter\Asset\ 

The TbDir2Vss.vbs tool can also be scheduled along with TbDbScript.vbs to script databases and put them in Visual SourceSafe.