ݿ޸ĵ
һ ݿ޸
 ݿ޸
 BCPݵĵ뵼
ġ ݿ޸
塢 ݿ޸Ҫע
 ݿ𻵵ԭ



*************************************************************************************************************************
***************************ĵݿ cspos_v6 Ϊ汾öӦݿ滻********************************
*************************************************************************************************************************




һ ݿ޸

--1ʹöӦ汾ݿⰲװ򣬰װͻݿͬݿ⣬ִ䣺
use master
go
sp_configure 'allow', 1
go
reconfigure with override
go

/*  ݿ״̬Ϊģʽ*/
update sysdatabases set status = 32768 where name = 'cspos_v6'

/*SQL2005SQL2008ݿ״̬Ϊģʽ*/ 
ALTER database  cspos_v6 SET EMERGENCY

go


--2ֹͣsql serverɾlogļÿͻ𻵵ļԭļsql serverʵ䣺
--޸ݿ־ļĿ¼ļһ£
DBCC REBUILD_LOG ('cspos_v6', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\cspos_v6_Log.LDF' )
go
/* SQL2000 ݿ״̬Ϊģʽ*/
update sysdatabases set status = 0 where name = 'cspos_v6'

/*SQL2005SQL2008ݿ״̬Ϊģʽ*/ 
ALTER database  cspos_v6 SET online
go

sp_configure 'allow', 0
reconfigure with override
go



 ݿ޸
USE master
Go

--ûģʽ

EXEC sp_dboption 'cspos_v6', 'single user', 'TRUE'
go
--ؽ˺ɫʾ֣˵ݿдڴҪ޸,ɽжν޸
DBCC CHECKDB ('cspos_v6',repair_fast)                    --޸
DBCC CHECKDB ('cspos_v6',repair_rebuild)                 --һ޸
DBCC CHECKDB ('cspos_v6',repair_allow_data_loss)         --߼޸
Go


--ٴݿ飬ؽû˺ɫʾ֣˵޸ɹ
DBCC CHECKDB ('cspos_v6')
Go
--˳ǰִ䷵صûģʽ
EXEC sp_dboption 'cspos_v6', 'single user','FALSE'
Go



BCPݵĵ뵼
--ע⣺BCPǰݿԭ𻵵ݿ ṹһ(ֶαһ£粻һ»ᵼ¶Ӧݱ޷)
--ͨV6ݿΪУҪݿ⣬cspos_v6޸ΪӦݿƼ
--ʹǰָ

--1.D̽TESTDBĿ¼ڲѯѡͨݿУ(-U  û-P  Ӧû -S Ӧķ)
use cspos_v6
select 'bcp cspos_v6..'+name + ' out '+'d:\testdb\'+name+'.txt -c -Usa -SСд -P' FROM SYSOBJECTS WHERE TYPE = 'U' 
ORDER BY NAME
--ѲѯĽȫ½һıļȡΪ.batѽƽȥ棬ѸļdĿ¼¡

--2.ڲѯѡͨݿУ 
use cspos_v6
select 'bcp cspos_v6..'+name + ' IN '+'d:\testdb\'+name+'.txt -c -Usa -SСд -P ' FROM SYSOBJECTS WHERE TYPE = 'U' 
ORDER BY NAME

--ѲѯĽȫ½һıļȡΪ.batѽƽȥ棬ѸļdĿ¼¡

--3.С.batע⣺ļ˫УݿеݻᵹTESTDBĿ¼С

--4.ɾԭݿ⣬ʹݿⰲװ½µݿ⡣

--5.ڲѯѡͨݿ:

/*ڲдɾ¼Եݽϴʱʹ truncate table ٶȿ*/
use cspos_v6
select 'truncate  table  '+name FROM SYSOBJECTS WHERE TYPE = 'U' 
--ȻѷصĽƣ½һѯڣѸƵճУ
--6.С.bat ɹͻָݿˣ




ġݿ޸
--ݿϴʱÿ޸ʱȽϾãڼݿʱ¼³ıԵݱ޸
--޸䣬滻ΪҪ޸ݱ
DBCC CHECKTABLE(table_name, REPAIR_REBUILD )
DBCC CHECKTABLE(table_name, repair_allow_data_loss )
--ؽ
DBCC DBREINDEX(table_name)

--ؽƶݿб
use cspos_v6--ݿ޸ݿи
go

declare @tablename varchar(100)
declare  test_cur cursor for
select name from sysobjects 
where type ='U'
open test_cur
fetch test_cur into @tablename
while @@fetch_status=0
begin   
   DBCC DBREINDEX (@tablename)   
   fetch test_cur into @tablename
end

close test_cur
deallocate test_cur
go


--ݿ޸гֱ(Ψһ)𻵣ִ޸ȻҪֹ
ñɾȻɾظݺؽ(Ψһ)




塢ݿ޸Ҫע
--1޸ݿ֮Ҫidentityеݱidentityе޸ֿͻ۵
--﷨
        DBCC CHECKIDENT(table_name,RESEED)

--дԶе  ֱִ伴ɣҪָݿ
--޸Զidentity䣺

use cspos_v6    --ݿ޸ݿи
go
declare @tablename varchar(100)
declare  test_cur cursor for
select object_name(id) from syscolumns 
where status=128
open test_cur
fetch test_cur into @tablename
while @@fetch_status=0
begin   
   DBCC CHECKIDENT (@tablename, RESEED)   
   fetch test_cur into @tablename
end
close test_cur
deallocate test_cur
go



--2޸֮ȱݿ⣬֤ͻݿ⻷
--3ԭͻİ汾ͻݿ⣬޸𻵵Ĵ洢̺ʹ
--4ݿĽвԣǰ̨ۡݡݣԼĲѯ
--5û󣬽޸õݿⷢͻ


ݿ𻵵ԭ
1.ɾ־;   
2.Ӳ(HD)(HD ߼  ) , ־ļд;   
3.Ӳ̵Ŀռ䲻,־ļ;    
4.ԭ
5.Ƿػ

