ANBOB™

平时大家不太注意库逻辑文件名，查每个库的sysfiles表都有，一般是两个文件，一个数据文件，一个日志文件

create database oa;

这样建的库 数据文件名为 oa_data 对应一个物理文件名如d:\msql\data\oa.mdf,日志文件 oa_log对应物理文件名为d:\msql\data\oa.ldf,但你如果用这个库做了个备份，再用那个备份还原一个oa_test 数据库，那逻辑文件名默认不是oa_test了而还是oa_data\oa_log这是从备份集中得来的，下面例子是把这个sqlserver服务器上所有的库逻辑文件名都改成和数据库一致的。

开始
DECLARE
@dataname VARCHAR(100),  @logname  VARCHAR(100),  @dbname   VARCHAR(100),@sqlexec varchar(2000)

DECLARE dbs_cur CURSOR  FOR
SELECT   name
FROM     master..sysdatabases
WHERE    dbid > 4
ORDER BY name;

— drop table db_info
if (not exists (select * from master..sysobjects where NAME=’DB_INFO’))
begin
create table DB_INFO
(dbname varchar(100)
,dataname varchar(100)
,logname varchar(100)
)
end;

DELETE master..DB_INFO;

OPEN dbs_cur;

FETCH NEXT FROM dbs_cur INTO @dbname;

WHILE (@@FETCH_STATUS = 0)
BEGIN

set @sqlexec=’insert into master..DB_INFO
select ”’+@dbname+”’,max(case when fileid=1 then name end)as datafile, max(case when fileid=2 then name end)as logfile
from ‘+@dbname+’..sysfiles’

exec (@sqlexec)

/*
exec (‘USE  ‘+ @dbname)
SELECT @dataname=File_name(1)
SELECT @logname=File_name(2)

IF upper(@dataname) <> upper(@dbname)
BEGIN
–alter database icme2_xian modify file (name=@dataname,NEWNAME=@dbname);
PRINT @dbname + ‘—datafile:’ + @dataname
END
IF lower(@logname) <> lower(@dbname + ‘_log’)
BEGIN
–alter database icme2_xian modify file (name=@logname,NEWNAME=@dbname + ‘_log’)
PRINT @dbname + ‘—log_file:’ + @logname
END
*/

FETCH NEXT FROM dbs_cur  INTO @dbname
END

CLOSE dbs_cur

DEALLOCATE dbs_cur

declare dbinfo_cur cursor for
select * from master..DB_INFO where lower(dataname)<>lower(dbname+’_data’) or lower(logname)<>lower(dbname+’_log’)

OPEN dbinfo_cur;

FETCH NEXT FROM dbinfo_cur INTO @dbname,@dataname,@logname
WHILE (@@FETCH_STATUS = 0)
BEGIN

IF lower(@dataname)<>lower(@dbname+’_data’)
BEGIN

set @sqlexec=’alter database ‘+@dbname+’ modify file (name=’+rtrim(@dataname)+’,NEWNAME=’+@dbname+’_data)’;
print @sqlexec
exec (@sqlexec)

END
IF lower(@logname) <> lower(@dbname + ‘_log’)
BEGIN
set @sqlexec=’alter database ‘+@dbname+’ modify file (name=’+rtrim(@logname)+’,NEWNAME=’+@dbname+ ‘_log)’
print @sqlexec
exec (@sqlexec);
END
FETCH NEXT FROM dbinfo_cur INTO @dbname,@dataname,@logname
END
close dbinfo_cur
DEALLOCATE dbinfo_cur

结束

打赏

微信扫一扫，打赏作者吧～