[ Pobierz całość w formacie PDF ]
.This frees resources for focusing on the needs of clients for yourdatabases.Chapter ResourcesThere are two key resources for this chapter.First, a SQL Server database nam edChapter 02 illustrates m any of the design concepts used throughout this chapter.Second, a collection of T-SQL sam ple scripts illustrates coding techniques forcreating tables and working with the resources within a table.The Chapt er s T- SQL Sam ple ScriptsThe T-SQL sam ple collection for this chapter illustrates key design andim plem entation issues for scripting SQL Server database objects.All the sam plescripts that you see in this chapter are available on the book s com panion CD.The sam ples are all saved with the.sql extension, so you can open and run eachof them from Query Analyzer.As you learned in Chapter 1, Query Analyzer is agraphical tool that ships with Microsoft SQL Server 2000.As you read and run thesam ple scripts, you m ight find it helpful to learn m ore about the structure of theChapter02 database by browsing it with SQL Server Enterprise Manager, whichalso was discussed in Chapter 1.The Chapt er s Sam ple DatabaseThe script in this section creates a new version of the Chapter02 database.Subsequent T-SQL code sam ples will create additional tables in the database anddem onstrate techniques for working with tables.Prepare to create the Chapter02 database by starting Query Analyzer andconnecting to the SQL Server instance you are using.Log in as sa or with a userI D that belongs to the sysadm in fixed server role.This book drills down onsecurity explicitly in Chapter 7, where you will learn how to fine-tune databaseand user security settings.When users connect to a SQL Server database throughyour Visual Basic.NET applications, they m ust identify them selves through thesecurity accounts discussed in Chapter 7.Until that chapter, using a login thatbelongs to sysadm in will work for all sam ples.Copy or type the following T-SQL script into the Editor pane in Query Analyzer,and press F5 to run the script to create the database.Alternatively, you can openthe script directly from Query Analyzer: choose Open from the File m enu, andthen navigate to the location of the script.Notice that the first com m ent in thesam ple is  CreateSam pleDB  the nam e of the sam ple file.I use this conventionfor all the sam ples in the book to m ake it easier for you to locate and open themfrom Query Analyzer.Attaching a Database to a New SQLServer I nstanceI regularly read on the SQL Server newsgroups of folks asking howto attach a database to a server.These developers want to take adatabase and its objects developed on one server and run them onanother server.Their need can be as sim ple as copying a databaseapplication they are developing on their desktop to their laptop sothey can work on it while away from the office.Alternatively, theymay want to copy a database from headquarters or one branchoffice to one or more other branch offices.Although there are wizards for this kind of thing, it is nice to knowhow to program the administration of this kind of task for your owncustom solutions.This capability liberates you from the cannedwizard solution and gives you more flexibility in how you work withSQL Server.At its m ost elementary level, this can be as sim ple asattaching a pair of database files to a new server instance.In thecontext of this chapter, a com pleted version of the Chapter02database is on the book s CD.Therefore, you m ight care to copy aversion to another instance of SQL Server besides the one you useto test the samples for this chapter.The instance can be on anothercom puter or the same computer.Start to migrate the Chapter02 database by copying theChapter02_dat.m df and Chapter02_log.ldf files from the CD to theData folder for the SQL Server instance to which you want to attachthe com pleted database.After clearing the read-only attributesettings for the files, you can run the following script from QueryAnalyzer.The script attaches the chapter s two database files to thedefault instance of the SQL Server to which Query Analyzerconnects.By changing MSSQL to MSSQL$MYOTHERI NSTANCE, youcan attach the database files to a SQL Server instance namedMYOTHERI NSTANCE.You must copy your database files to the Datapath for the SQL Server instance in the sp_attach_db statem entbefore running the script.--AttachSampleDB--Run the script from the master database.USE master--Update the paths for the data and log files so they--are appropriate for your computer.EXEC sp_attach_db @dbname = N Chapter02 ,@filename1 =N c:\Program Files\Microsoft SQL Server\MSSQL\Data\Chapter02_dat.mdf ,@filename2 =N c:\Program Files\Microsoft SQL Server\MSSQL\Data\Chapter02_log.ldfThe initial USE statem ent in the script specifies the source database so that thesam ple runs from the SQL Server m aster database [ Pobierz całość w formacie PDF ]