6
01 SQL Server SQL Server 2008 SQL Server 6-1 SSIS 6-1 06 228 6-1 SQL Server ( master ) ( msdb ) SQL Server ( master ) master 6-1 DTS sysadmin 6-1 sysadmin 6-1
SQL Server 2008 SSIS SQL Server (dbo) master master master dbo msdb SQL Server Agent (fixed database role) (dbo) 6-1 SQL Server 2000 SQL Server 20 XML 2008 20 SQL Server SQL Server 6-1-1 SQL Server SQL Server T-SQL 6-5 SQL Server Profiler ( ) SQL Server SQL SQL Server 01 6-1 229
01 SQL Server 6-1 SQL Server 06 AdventureWorks2008 SMO (SQL Server Management Object) SMOServer SMO 6-2 230 6-2 SMO SQL Server SQL Server 6-3
6-3 231 SQL Server 2008 SSIS 01 6-1 SMO SMO SMO CopyAllTables True CopyAllTables Fales TableList 6-4
01 06 232 6-4 Unicode SQL Server SQL Server Profiler 6-5
SQL Server 2008 SSIS 01 6-1 6-5 SQL Server Profiler T-SQL SQL Server ( 2008 20 ) 1 Profiler 6-1-2 SSIS SQL Server master 1 For Integration Services 6-5 Profiler SQL Server 233
01 06 master A sp1 B exec sp1 6-1 CREATE DATABASE A Go USE A GO CREATE TABLE t(c1 nvarchar(6)) INSERT t VALUES('A') GO CREATE PROC sp1 AS SELECT * FROM t GO CREATE DATABASE B GO USE B GO EXEC sp1 234 6-1 2812 16 62 1 'sp1' master sp_ 6-2 USE master GO CREATE TABLE t(c1 nvarchar(6)) INSERT t VALUES('master') GO CREATE PROC sp_1 AS SELECT * FROM t GO USE A EXEC sp_1 6-2 master sp_
SQL Server 2008 SSIS t 'A' 'master' A sp_1 master A A sp_1 USE master exec sp_ms_marksystemobject 'sp_1' sp_ms_marksystemobject sys.objects is_ms_shipped SELECT is_ms_shipped FROM sys.objects WHERE name='sp_1' is_ms_shipped 0 sp_ms_ marksystemobject 1 master sp_ms_marksystemobject 6-2 master SMO SQL Server 6-6 01 6-1 6-1 6-2 A master 235
01 236 06 6-6 IfObjectExists Overwrite FileTask Skip Transfer All StoredProcedures False StoredProcedureList 6-1-3 SQL Server SQL Server Agent SQL Server SQL Server Management Studio T-SQL 6-7
SQL Server 2008 SSIS 01 SQL Server 6-3 SMO SQL Server 6-8 6-8 Transfer AllJobs False JobList EnableJobsAtDestincation 6-1 6-7 T-SQL 237
01 6-1-4 SQL Server SQL sa 6-4 SMO 6-9 06 238 6-9 CopySids SID ( ) SID IfObjectExist
SQL Server 2008 SSIS 6-10 6-10 Profiler SQL Server 2 (hash) How to transfer logins and passwords between instances of SQL Server http://support.microsoft.com/kb/246133/en-us How to transfer the logins and the passwords between instances of SQL Server 20 http://support.microsoft.com/kb/918992/en-us 2 password 01 6-1 239
240 01 06 C:\BookSamples\SSIS2008\Ch06_CopyDatabaseObjectTask\Scripts 6-1-5 SQL Server 3 ( 50000 ) SQL Server master (trigger) raiserror 6-3 USE master; GO EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'The item named %s already exists in %s.', @lang='us_english' -- EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N' %1! %2!' -- raiserror(60000,16,1,' ',' ') -- EXEC sp_dropmessage 60000 EXEC sp_dropmessage 60000,@lang='us_english'GO 6-3 T-SQL 50000 3 s p_a d d m e s s a g e m s-h e l p:// MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHT/s10de_6tsql/html/54746d30-f944-40e5-a7- f2d9be0fb9eb.htm
SQL Server 2008 SSIS SMO ErrorMessageList 6-11 6-11 IfObjectExixts us_english ( 13 18) master Use master SELECT * FROM sysmessages where error=60000 -- SQL 2000 -- SELECT * FROM sys.message where message_id=60000 -- SQL 2008 01 6-1 6-5 241
242 01 06 6-1-6 SQL Server SQL Server 2000/20 SMO SMO SMO SMO 6-6 SQL Server Northwind SQL Server ( Method DatabaseOnline) SourceDatabaseName 6-12 6-12
6-2 243 SQL Server 2008 SSIS DestinationDatabaseFiles T-SQL 6-1-1 SQL Server 4 Profiler 6-13 01 6-1 6-13 Profiler 6-2 DestinationDatabase Name DestinationDatabaseFile DestinationOverwrite 4 SQL Server CREATE
244 01 Action Copy ( ) Move ( ) Mothod DatabaseOffline ( ) DatabaseOnline ( ) SQL Management Object (SMO) SorceDatabaseName SourceDatabaseFile Mothod DatabaseOffline ReattachSrouce Database Mothod DatabaseOffline 6-2 ( ) 6-7 AdventureWorks.mdf.ldf.ndf 06 6-14
SQL Server 2008 SSIS 01 6-1 4 AdventureWorks Method DatabaseOffline DestinationDatabaseFiles SourceDatabaseFiles 5 Profiler 6-15 6-15 5 6-1 6-14 245
246 01 06 6-2 Management Studio SQL Server SSIS SQL Server 2008 Database Engine SQL Server Agent (job) (sysadmin) BIDS BIDS SQL Server 2000/20 SQL Server 2008 6-3 SSIS SQL Server msdb SQL Server Agent SQL Server Agent T-SQL Transact-SQL SQL Server Agent 6-3
SQL Server 2008 SSIS SQL Server.xp_delete_file 6-3 ( ) T-SQL SSIS SQL Server T-SQL 6-2-1 6-8 6-16 01 6-2 247
01 06 248 6-16 6-16 6-3 (simple) (full) (bulk-logged) 6-3
SQL Server 2008 SSIS 6-17 6-17 6-17 SQL Server 2008 T-SQL BACKUP 6-17 T-SQL T-SQL 6-4 6 Management Studio Database Engine 01 6-2 6 249
250 01 06 EXECUTE master.dbo.xp_create_subdir N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008' GO EXECUTE master.dbo.xp_create_subdir N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2008' GO BACKUP DATABASE [AdventureWorks2008] TO DISK = N'C:\Program Files\ Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008\ AdventureWorks2008_backup_2008_11_12_210909_3461984.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2008_backup_2008_11_12_210909_3452219', SKIP, REWIND, NOUNLOAD, STATS = 10 GO declare @backupsetid as int select @backupsetid = position from msdb..backupset where database_ name=n'adventureworks2008' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=n'adventureworks2008' ) i f @ b a c k u p S e t I d i s n u l l b e g i n r a i s e r r o r ( N ' ''AdventureWorks2008'' ', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\ MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008\AdventureWorks2008_ba ckup_2008_11_12_210909_3461984.bak' WITH FILE = @backupsetid, NOUNLOAD, NOREWIND GO BACKUP DATABASE [AdventureWorksDW2008] TO DISK = N'C:\Progra m Files\ Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2008\ AdventureWorksDW2008_backup_2008_11_12_210909_3559634.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW2008_backup_2008_11_12_210909_3559634', SKIP, REWIND, NOUNLOAD, STATS = 10 GO declare @backupsetid as int select @backupsetid = position from msdb..backupset where database_ name=n'adventureworksdw2008' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=n'adventureworksdw2008' ) i f @ b a c k u p S e t I d i s n u l l b e g i n r a i s e r r o r ( N ' ''AdventureWorksDW2008'' ', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\ MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2008\AdventureWorksDW2008_ backup_2008_11_12_210909_3559634.bak' WITH FILE = @backupsetid, NOUNLOAD, NOREWIND 6-4 T-SQL 6-18
SQL Server 2008 SSIS 01 6-18 I/O 6-2-2 6-19 6-19 6-2 6-18 251
01 6-19 6-16 T-SQL USE [AdventureWorksDW2008] GO DBCC CHECKDB(N'AdventureWorksDW2008') WITH NO_INFOMSGS 6-2-3 SQL Server Agent SQL Server Agent SQL Server SQL Server Agent 6-20 06 252 6-20 SQL Server Agent 6-20 T-SQL T-SQL EXEC msdb.dbo.sp_start_job @job_id=n'fb448f12-ff5d-49c9-b328-afd12b67436d' sp_start_job SQL Server ID 7 SQL Server Agent 7 20
SQL Server 2008 SSIS T-SQL SQL T-SQL T-SQL T-SQL SQL SQL ( PL-SQL) SQL T-SQL 6-21 6-21 T-SQL T-SQL SQL Server 6-2-5 msdb backupfile backupset backupfilegroup restorefile backupmediafamily restorefilegroup backupmediaset restorehistory 01 6-2 T-SQL 6-2-4 253
01 6-22 254 06 6-22 SQL Server SQL Server 6-22 T-SQL declare @dt datetime select @dt = cast(n'2008-10-16t09:21:00' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt GO EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2008-10-16t09:21:00' GO EXECUTE msdb..sp_maintplan_delete_log null,null,'2008-10-16t09:21:00' 6-2-6 SQL Server Agent 8 SQL Server Agent SQL Server Management Studio 6-23 8 SQL Server Agent NetSend
SQL Server 2008 SSIS 01 6-24 6-24 6-24 T-SQL EXECUTE msdb.dbo.sp_notify_operator @name=n'byron',@subject=n' ',@ body=n' ' 6-2 6-23 255
01 SQL Server Agent OperatorNotify SMTP... 6-23 SQL Server 20 Database Mail Profile SQL Server Management Studio DataBase Mail 6-25 06 256 6-25 DataBase Mail Profile
SQL Server 2008 SSIS 6-26 Database Mail SQL Server Agent 6-26 6-27 6-27 Agent Agent 6-2-7 9 9 01 6-2 257
01 6-28 06 6-28 258 10 ( ) (page split) 10 (fill factor) 100% 70% 30%
SQL Server 2008 SSIS Transact-SQL ( 6-28 T-SQL ) ALTER INDEX REBUILD ( T-SQL ALTER INDEX ) SORT_IN_TEMPDB = ON tempdb OFF ONLINE = ON SQL Server Management Studio 6-29 6-29 Management Studio 01 6-2 T-SQL 259
01 06 30% 30% 11 Management Studio sys.dm_db_index_ physical_stats HumanResources.Employee USE AdventureWorks2008; GO SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HumanResources. Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 6-5 AdventureWorks2008 HumanResources.Employee 260 6-30 6-30 6-2-8 ( ) 11 ms-help://ms.sqlcc.v10/ms.sqlsvr.v10.zh-cht/s10de_1devconc/html/a28c684ac4e9-4b24-a7ae-e248808b31e9.htm
SQL Server 2008 SSIS sys.indexes 6-9 ( 30% ) SQL T-SQL AdventureWorks2008 select avg(avg_fragmentation_in_percent) from sys.dm_db_index_physical_stats(db_id('adventureworks'),null,null,null, NULL) 15% 15% 30% 30% 6-31 6-31 01 6-2 261
01 6-31 SQL T-SQL percent SSIS 6-3 2 AdventureWorks2008 06 262 6-32 (LOB) image text ntext varchar(max) nvarchar(max) varbinary(max) xml 6-2-9 12 12 /
SQL Server 2008 SSIS 6-33 T-SQL USE [AdventureWorks2008] GO DBCC SHRINKDATABASE(N'AdventureWorks2008', 10, TRUNCATEONLY) DBCC SHRINKDATABASE NOTRUNCATE TRUNCATEONLY SHRINKDATA- BASE SHRINKDATABASE database_name 01 6-2 6-33 263
264 01 06 6-2-10 SQL Server SQL Server SQL Server ( ) 6-34 6-34
SQL Server 2008 SSIS table_name view_name WITH ALL WITH COLUMN WITH INDEX UPDATE STATISTICS UPDATE STATISTICS table_name view_name T-SQL use [AdventureWorks2008] GO UPDATE STATISTICS [HumanResources].[Department] WITH FULLSCAN GO use [AdventureWorks2008] GO UPDATE STATISTICS [HumanResources].[Employee] WITH FULLSCAN CREATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL) Transact-SQL Transact- SQL 6-2-11 01 6-2 UPDATE STATISTICS 265
01 BAK TXT 256 6-35 06 266 6-35 EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\ MSSQL10.MSSQLSERVER\MSSQL\Backup',N'.bak',N'2008-10-16T16:38:'
SQL Server 2008 SSIS Business Intelligence Development Studio(BIDS) Integration Services Management Studio 6-36 6-36 6-36 6-37 01 6-3 6-3 267
01 06 268
6-37 269 SQL Server 2008 SSIS 01 6-3
01 Management Studio 6-38 06 270 6-38 Management Studio 6-38 Integration Services Management Studio