Arthur Fuller
fuller.artful at gmail.com
Thu Jun 7 06:04:00 CDT 2007
Your general strategy is correct, IMO, but I would be tempted to take it a little further into the past, if possible. This depends on the state of your backups and available disk space. Think back to milestones of significant change and see if you have backups before and immediately after such changes. If so, then if I were you, I would start there, and build up a history in VSS that culminates in the current build. Among other things, this would enable you to re-create any past version relatively painlessly. In several situations in which I worked, this was a paramount concern, because clients 1, 2 and 3 had build x.yy while clients 4, 5 and 8 had build x+1.00. Should a customer call in with a bug report on any version, VSS enabled us to quickly recreate exactly the version we shipped and then duplicate the bug and then address it. Why is this important? The larger the customer, the more important it is, IMO. Your developer-code may have moved on from Vx to Vy, and in Vy fixed this particular bug. But the client doesn't want to migrate; he wants minimum ripples throughout the firm. He want this and only this bug fixed and *nothing* else changed. IME, the larger the client firm, the more this holds true. That among many other things is why VSS (or any other version control system) is so vital. So. Two possible situations pertain: do you have VS.NET <http://vs.net/> or not? Yes: even then, it depends on which version you have. Standard and Express lack features that the expensive versions have. With VS.Studio: 0. Ensure that VSS is installed and create at least one user besides Admin. 1. Create the database project in VS.NET. 2. Open Solution Explorer. 3. Make sure that your database is one of the Database References. If the reference does not already exist, right-click Database References and select New Database Reference to create one that points to your database. 4. In Server Explorer, expand the Data Connections node, then your database and keep expanding until you get to your list of stored procedures. 5. Right-click one sproc and choose Generate Create Script to Project. The program adds the script to the Create Script folder of your project. 6. Navigate to the list of tables in your database. Choose a table and right-click and select Generate Create Script to Project. (Note: the Generate Create Script command may not be available in your version of SQL 2005. If it is not available, you can generate scripts using the Script Wizard. See below.) 7. Now we add the project to VSS. From VS.NET, select File | Source Control | Add Project to Source Control. Log in on the next dialog. 8. VSS prompts you to add a project. Create a name and optionally add a comment. VSS creates a project and locks all the Create scripts you generated. Their locked status is indicated by a lock icon beside their names. From this moment on, you must check out a Create script before you can change it. To view a script from within VS.NET, right-click its name and select Open. This is a read-only view. To change a script: 1. Close the View window. 2. Right-click the script name, then choose Check Out. The program prompts you for a comment. 3. Make some change to the script. 4. Save the changed script and close the window. 5. Right-click the script name and choose Check In. VS.NET saves the new script in VSS. You now have two versions of the script in VSS. That's why it's important in Step 2 above to supply a comment that describes the change you made. 6. *This is important*: if you follow these steps exactly, the stored procedure *does not yet exist in the database*. To create it there, you must right-click its name and select Run to deploy it. However, it probably makes more sense to run the script and fix any errors before adding it to VSS. -------- Without VS.NET Use Management Studio to generate scripts for all objects in the database: 1. In Object Explorer, pick a database. 2. Right-click its name, then choose Tasks | Generate Scripts (not Script Database As | Create To). 3. The Script Wizard opens. Select a database. In the next step, you can select the scripting options. 4. On the next page, you can select which object types you want to script. 5. For each selected object type, you then get a page listing them and can select the ones you want to script. (You can even script CLR objects if any exist.) 6. Last, the wizard prompts you for a destination (file, clipboard or query window). 7. Now run VSS, create a project and add the scripts you just generated. hth, Arthur On 6/6/07, Robert L. Stewart <rl_stewart at highstream.net> wrote: > > Thanks Arthur. > Further info. > > We have a large db in place with multiple instances of it. > The web guy's bright idea was to take the time to put > each individual object in VSS then keep it up to date. > He got rather upset when I told him "No. It needs more > thought." What I cam up with was to check VSS and see if > an object existed. If not, add the original object and > then the new object. This would build the VSS repository > as we made changes. The idea was to be able to go back > to a previous version of an object if needed. > > Robert > > At 12:00 PM 6/6/2007, you wrote: > >From: dba-sqlserver-bounces at databaseadvisors.com > >[mailto:dba-sqlserver-bounces at databaseadvisors.com ] On Behalf Of Arthur > >Fuller > >Sent: Tuesday, June 05, 2007 11:15 AM > >To: dba-sqlserver at databaseadvisors.com > >Subject: Re: [dba-SQLServer] Source code version control > > > >I thought that I responded at length to you. It's not in my drafts. Maybe > I > >accidentally discarded it. I described the two ways of using it, one with > >VS.NET <http://vs.net/> and the other without. I'll see if I can dig it > up and re-send it. If > >not I'll redo it. > > > >Arthur > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >