在SQLServer中自动产生移动数据库的TSQL语句

时间：2010-10-08 来源：leslie116

当Sqlserver上有很多Database，要想移动其数据文件的位置时，显得很麻烦，下面的SQL语句会自动生成移动Database文件的SQL语句．

代码 declare @db_name nvarchar(256)
,@type int
,@name nvarchar(256)
,@physical_name nvarchar(1000)

DECLARE master_files_cursor CURSOR FOR
select db_name(database_id),type,name,physical_name
from sys.master_files where database_id>6
order by database_id

OPEN master_files_cursor;
FETCH NEXT FROM master_files_cursor INTO @db_name,@type,@name,@physical_name
WHILE @@FETCH_STATUS = 0
   BEGIN
  print 'ALTER DATABASE '+@db_name+' SET OFFLINE;'
print 'GO'
--print '-- Physically move the file to a new location.'
--print ' In the following statement, modify the path specified in FILENAME to'
--print ' the new location of the file on your server.'
print 'ALTER DATABASE '+@db_name
print '    MODIFY FILE ( NAME = '+@name+', '
print '                  FILENAME = '''+@physical_name+''');'
print 'GO'
print 'ALTER DATABASE '+@db_name+' SET ONLINE;'
print 'GO'
FETCH NEXT FROM master_files_cursor INTO @db_name,@type,@name,@physical_name
END
CLOSE master_files_cursor
DEALLOCATE master_files_cursor

相关阅读更多 +