超过 6 个月的 SQL Server DETACH 数据库

Question

我可以像这样检索 6 个月前创建的数据库列表:-

-- all databases over 6 months old
select name, crdate
from sys.sysdatabases
where crdate <= DATEADD(month, -6, GETDATE())
      AND name not in ('master','model','msdb','tempdb','distribution')

给出这样的结果:-

name        crdate
db1         2008-06-25 09:01:11.747
db2         2008-06-25 09:01:50.967

我可以像这样分离数据库:-

-- detach database
EXEC master.dbo.sp_detach_db @dbname = N'db1',
@keepfulltextindexfile = N'true'

我需要运行 sp_detach_db对于每个数据库，第一个查询返回。

这样做的最佳方法是什么？

Answer 1

您可以为任务使用游标:

declare cur cursor for
select name
from sys.sysdatabases
where crdate <= DATEADD(month, -6, GETDATE())
and name not in ('master','model','msdb','tempdb','distribution')

declare @name nvarchar(200)

open cur

fetch next from cur into @name

while @@FETCH_STATUS = 0
begin
    EXEC master.dbo.sp_detach_db @dbname = @name, @keepfulltextindexfile = N'true'
    fetch next from cur into @name
end

close cur
deallocate cur