none
怎樣用sql語言實現將一個數據庫take offline RRS feed

  • 问题







  • 怎樣用sql語言實現將一個數據庫take offline?
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2009年7月14日 20:27

答案

  •   set @commandstr = 'alter  DATABASE ' + @To_dbname
    你的变量类型是 char, 完成上面这句后, 已经填满了(char是自动补空格的), 所以下面这个是加不上去的, 拼出来的语句自然不对
                               set @commandstr=@commandstr+'set offline'
    2009年7月15日 4:45

  • 數據庫的take offline选项,个人比较喜欢用于,数据库物理文件的移动时用.

    看楼主的语句,是想实现断连接,再删除数据库.用以下脚本就行了

    declare @db_name sysname,@kill nvarchar(1000)
    select @db_name='SharePoint_AdminContent_73549c7f-82ae-4ecd-810a-66e1c1b2459c',@kill=''
    if db_id(@db_name) is not null
    begin
     select @kill=@kill+'kill ' +rtrim(spid)+char(13)+char(10) from sysprocesses 
     where dbid=db_id(@db_name)
     exec sp_sqlexec @kill --断连接
     set @kill='drop database ['+ @db_name+']'
     exec sp_sqlexec @kill
    end

    ROY WU(吳熹)
    2009年7月15日 16:47
    版主

全部回复

  • 已經找到sql語句
     
    alter database dbname

    set offline
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2009年7月14日 21:11
  • declare @To_dbname char(10)

    declare @commandstr char(100)

    set @To_dbname ='test'

    begin
      /***************** Take DB offline****************/
               
                 if exists(select name from master.dbo.sysdatabases (nolock) where name = @To_dbname)
       begin
      set @commandstr = 'alter  DATABASE ' + @To_dbname
                               set @commandstr=@commandstr+'set offline'
      exec sp_sqlexec @commandstr
      WAITFOR DELAY '00:00:10'
       end                       

                  /***************** Drop DB ****************/
     if exists(select name from master.dbo.sysdatabases (nolock) where name = @To_dbname)
       begin
      set @commandstr = 'DROP DATABASE ' + @To_dbname
      exec sp_sqlexec @commandstr
      WAITFOR DELAY '00:00:10'
       end


    I use the above sql statement to drop test .

    I parse the sql ,it is ok

    when I execute the sql ,then the db pubs is dropped,but there generate a error :

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'test'.
    Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF'.
    Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Data.MDF'.

    Actually,test database is dropped successully,why this error come out?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2009年7月14日 22:50
  • 拼接字符串不要声明成char类型,可以使用varchar或者nvarchar
    你可以执行select @commandstr看一下最终的字符串到底是什么
    实际上是'alter  DATABASE test                                                                                '
    字符串的结尾有大量的空格.

    而且你的数据库实际上是被drop掉了,并没有被offline
    SQL SERVER Engine Test
    2009年7月15日 3:18
    版主
  •   set @commandstr = 'alter  DATABASE ' + @To_dbname
    你的变量类型是 char, 完成上面这句后, 已经填满了(char是自动补空格的), 所以下面这个是加不上去的, 拼出来的语句自然不对
                               set @commandstr=@commandstr+'set offline'
    2009年7月15日 4:45
  •   set @commandstr = 'alter  DATABASE ' + @To_dbname
    你的变量类型是 char, 完成上面这句后, 已经填满了(char是自动补空格的), 所以下面这个是加不上去的, 拼出来的语句自然不对
                               set @commandstr=@commandstr+'set offline'

    那如果直接寫成:
    0

    declare @To_dbname char(10)

    declare @commandstr char(100)

    set @To_dbname ='test'

    begin
      /***************** Take DB offline****************/
               
                 if exists(select name from master.dbo.sysdatabases (nolock) where name = @To_dbname)
       begin
      set @commandstr = 'alter  DATABASE ' + @To_dbname+'set offline'
      

       exec sp_sqlexec @commandstr
      WAITFOR DELAY '00:00:10'
       end                       

                  /***************** Drop DB ****************/
     if exists(select name from master.dbo.sysdatabases (nolock) where name = @To_dbname)
       begin
      set @commandstr = 'DROP DATABASE ' + @To_dbname
      exec sp_sqlexec @commandstr
      WAITFOR DELAY '00:00:10'
       end


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2009年7月15日 14:04

  • 數據庫的take offline选项,个人比较喜欢用于,数据库物理文件的移动时用.

    看楼主的语句,是想实现断连接,再删除数据库.用以下脚本就行了

    declare @db_name sysname,@kill nvarchar(1000)
    select @db_name='SharePoint_AdminContent_73549c7f-82ae-4ecd-810a-66e1c1b2459c',@kill=''
    if db_id(@db_name) is not null
    begin
     select @kill=@kill+'kill ' +rtrim(spid)+char(13)+char(10) from sysprocesses 
     where dbid=db_id(@db_name)
     exec sp_sqlexec @kill --断连接
     set @kill='drop database ['+ @db_name+']'
     exec sp_sqlexec @kill
    end

    ROY WU(吳熹)
    2009年7月15日 16:47
    版主