在SQLServer中自动产生移动数据库的TSQL语句
当Sqlserver上有很多Database，要想移动其数据文件的位置时，显得很麻烦，下面的SQL语句会自动生成移动Database文件的SQL语句．
代码
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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
,@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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16436858/viewspace-675540/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
