SQLSERVER数据库升级脚本图文步骤

只能远程协助的方式。我特意做了一个脚本，用电话指导客户在ssms里执行一下脚本就可以了

1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[ct_outercard]表比1.0的多了6个字段，其他所有表都一样

还有存储过程增加了很多，其他都没有改变

首先，先在公司的服务器数据库上生成存储过程脚本，数据库是1.1版本的，下面的图片里没有说明的，都是默认设置，下一步即可

选中数据库-》右键—》任务-》生成脚本

当然，如果你的数据库里有自定义函数的话，也可以勾选函数，如果我们的数据库没有函数，所以。。。

保存到新建查询窗口

这一步做完了，然后编写下面的sql脚本

--升级gpos1.0到gpos1.1数据库的升级脚本 2013-7-4
use [gposdb]
go
------------------删除所有存储过程-------------------
--select * from sys.procedures

declare @sql varchar(4000)
set @sql=''
select @sql=@sql+'drop proc '+name+';   ' from sys.procedures
--print @sql
exec(@sql)

--------------------------------在[ct_outercard]表添加6个字段-------------------------------
alter table [dbo].[ct_outercard] add [i_limittranscurrcount] int  not null constraint [df_ct_outercard_i_limittranscurrcount]  default ((0))
alter table [dbo].[ct_outercard] add [i_limittranstype] [int] constraint [df_ct_outercard_i_limittranstype]  default ((0))
alter table [dbo].[ct_outercard] add [de_limittranstotal] [decimal](18, 2) not null constraint [df_ct_outercard_de_limittranstotal]  default ((0))
alter table [dbo].[ct_outercard] add [de_limittranscurrtotal] [decimal](18, 2) not null constraint [df_ct_outercard_de_limittranscurrtotal]  default ((0))
alter table [dbo].[ct_outercard] add [i_limitcarno] [int] not null constraint [df_ct_outercard_i_limitcarno]  default ((0))
alter table [dbo].[ct_outercard] add [d_limitdate] [datetime] not null constraint [df_ct_outercard_d_limitdate]  default (getdate())
--------------------------------------------------------------------------------------------------------------
--把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面
---------------------------创建gpos1.1的所有存储过程---------------------------------------------
use [gposdb]
go
/****** 对象:  storedprocedure [dbo].[report_greasersalestat]    脚本日期: 07/04/2013 13:27:09 ******/
set ansi_nulls off
go
set quoted_identifier off
go

create proc [dbo].[report_greasersalestat]
@startdate datetime,
@enddate datetime,
@action int --0为交易记录，1为班次记录

   
            insert into #tmpcardamoutstat
                (
                    vc_oc_cardno,

            set @i=@i+1
        end

        truncate table #tmpcards
        insert into #tmpcards(vc_oc_cardno)
        select vc_oc_cardno from ct_outercard where isnull(vc_oc_company,'')=''
        set @j=1
        select @cardcount=count(*) from #tmpcards
        while @j<=@cardcount
        begin
            select @vc_oc_cardno=vc_oc_cardno from #tmpcards where  indexid=@j
            insert into #tmpcardamoutstat
                (
                    vc_oc_cardno,
       

        insert into #tmpcardamoutstat
            (
                vc_oc_cardno,
                companyname,
                vc_oc_username,
                startamount,
                fillmoney,
                consumesumvol,
                consumemoney,
                sumconsumesumvol,
                sumconsumemoney,
                sumfillmoney

            )
        select
            null,
            null,
            '客户卡小计',
            sum(startamount),
            sum(fillmoney),
            sum(consumesumvol),

        truncate table #tmpcards
        insert into #tmpcards(vc_oc_cardno)
        select vc_ic_cardno from ct_inhousecard where isnull(vc_ic_cardno,'')<>''
        set @j=1
        select @cardcount=count(*) from #tmpcards
        while @j<=@cardcount
        begin
            select @vc_oc_cardno=vc_oc_cardno from #tmpcards where  indexid=@j
            insert into #tmpcardamoutstat
                (
                    vc_oc_cardno,
                    companyname,
                    vc_oc_username,
                    startamount,
                    fillmoney,
                    consumesumvol,
                    consumemoney,
                    sumconsumesumvol,
                    sumconsumemoney,
                    sumfillmoney

                )
            select
                @vc_oc_cardno,
                '员工卡',
                isnull((select vc_ic_username from ct_inhousecard where vc_ic_cardno=@vc_oc_cardno),''),
                isnull((select top 1 de_fd_amount from ct_fuelingdata where vc_fd_cardno=@vc_oc_cardno and (d_fd_datetime<=@startdate) order by d_fd_datetime desc),0),
                isnull((select sum(de_a_appendamount) from ct_append where vc_a_cardno=@vc_oc_cardno and (d_a_appenddatetime between @startdate and @enddate)),0),
                isnull((select sum(de_fd_volume) from ct_fuelingdata where vc_fd_cardno=@vc_oc_cardno and (d_fd_datetime between @startdate and @enddate)),0),
                isnull((select sum(de_fd_amount) from ct_fuelingdata where vc_fd_cardno=@vc_oc_cardno and (d_fd_datetime between @startdate and @enddate)),0),
                isnull((select sum(de_fd_volume) from ct_fuelingdata where vc_fd_cardno=@vc_oc_cardno),0),
                isnull((select sum(de_fd_amount) from ct_fuelingdata where vc_fd_cardno=@vc_oc_cardno),0),
                isnull((select sum(de_a_appendamount) from ct_append where vc_a_cardno=@vc_oc_cardno),0)
            set @j=@j+1
        end

        insert into #tmpcardamoutstat
            (
                vc_oc_cardno,
                companyname,
                vc_oc_username,
                startamount,
                fillmoney,
                consumesumvol,
                consumemoney,
                sumconsumesumvol,
                sumconsumemoney,
                sumfillmoney

            )
        select
            null,
            null,
            '员工卡小计',
            sum(startamount),
            sum(fillmoney),
            sum(consumesumvol),
            sum(consumemoney),
            sum(sumconsumesumvol),
            sum(sumconsumemoney),
            sum(sumfillmoney)
        from
            #tmpcardamoutstat
        where
            companyname='员工卡'
        ---计算员工卡汇总结束---
    end

    ----计算总汇总开始---
    insert into #tmpcardamoutstat
        (
            vc_oc_cardno,
            companyname,
            vc_oc_username,
            startamount,
            fillmoney,
            consumesumvol,
            consumemoney,
            sumconsumesumvol,
            sumconsumemoney,
            sumfillmoney

        )
    select
        null,
        null,
        '总计',
        sum(startamount),
        sum(fillmoney),
        sum(consumesumvol),
        sum(consumemoney),
        sum(sumconsumesumvol),
        sum(sumconsumemoney),
        sum(sumfillmoney)
    from
        #tmpcardamoutstat
    where
        (vc_oc_username='客户卡小计' or vc_oc_username='员工卡小计') and vc_oc_cardno is null
    update #tmpcardamoutstat set endamount=startamount+fillmoney-consumemoney
    ---计算总汇总结束---
    select * from  #tmpcardamoutstat

    drop table #tmpcards
    drop table #tmpcompanys
    drop table #tmpcardamoutstat
go

--其他存储过程省略。。。。。。。。。。。

然后把这个脚本发给客户，让客户在ssms里执行一下就可以了

当然如果某些表的主键更改了也很简单，使用alter table alter column语句修改一下就可以了

如有不对的地方，欢迎大家拍砖o(∩_∩)o