我使用 TSQL 分离数据库,方法如下:
EXEC sp_detach_db @dbname = 'my_db'
我随后使用PHP重命名物理文件。我能够重命名mdf文件,但无法重命名ldf文件!我甚至尝试了一个dos命令REN
,但这对ldf文件也不起作用!
我想问一下,物理日志文件是否有什么特殊之处,导致它无法被重命名?
有没有更好的方法来完成这个任务?
谢谢大家
我使用 TSQL 分离数据库,方法如下:
EXEC sp_detach_db @dbname = 'my_db'
我随后使用PHP重命名物理文件。我能够重命名mdf文件,但无法重命名ldf文件!我甚至尝试了一个dos命令REN
,但这对ldf文件也不起作用!
我想问一下,物理日志文件是否有什么特殊之处,导致它无法被重命名?
有没有更好的方法来完成这个任务?
谢谢大家
将数据库分离、重命名文件,再次附加。
"ALTER DATABASE (your database) MODIFY FILE" 命令只会重命名逻辑名称。本文介绍如何使用 xp_cmdshell 也重命名物理文件:http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/
请注意以下事项:
1. xp_cmdshell 将在 SQL Server 进程运行的用户下执行,并可能没有所需的文件系统权限来重命名数据库文件。
2. 出于安全原因,请记得禁用 xp_cmdshell。
以下是根据上述博客文章进行重命名的示例。它将用数据库 NewMyDB 替换数据库 MyDB。原始的 MyDB(重命名为 MyDB_OLD)将被保留为分离状态。
-- Enable xp_cmdshell:
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
-- Get physical file names:
declare @MyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB')
declare @MyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB_log')
declare @NewMyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB')
declare @NewMyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB_log')
declare @Command nvarchar(500)
declare @Sql nvarchar(2000)
IF (EXISTS (select * from sys.databases where name = 'NewMyDB')
AND EXISTS (select * from sys.databases where name = 'MyDB'))
BEGIN
USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE NewMyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Set new database name
ALTER DATABASE MyDB MODIFY NAME = MyDB_OLD
ALTER DATABASE NewMyDB MODIFY NAME = MyDB
-- Update logical names
ALTER DATABASE MyDB_OLD MODIFY FILE (NAME=N'MyDB', NEWNAME=N'MyDB_OLD')
ALTER DATABASE [MyDB] MODIFY FILE (NAME=N'NewMyDB', NEWNAME=N'MyDB')
EXEC master.dbo.sp_detach_db @dbname = N'MyDB_Old'
EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
-- Rename physical files
SET @Command = 'RENAME "' + @MyDBOriginalFileName + '" "MyDB_OLD.mdf"'; PRINT @Command
EXEC xp_cmdshell @Command
SET @Command = 'RENAME "' + @MyDBLogOriginalFileName + '" "MyDB_OLD_log.mdf"'; PRINT @Command
EXEC xp_cmdshell @Command
SET @Command = 'RENAME "' + @NewMyDBOriginalFileName + '" "MyDB.mdf"'; PRINT @Command
EXEC xp_cmdshell @Command
SET @Command = 'RENAME "' + @NewMyDBLogOriginalFileName + '" "MyDB_log.mdf"'; PRINT @Command
EXEC xp_cmdshell @Command
-- Attach with new file names
declare @NewMyDBFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB', 'MyDB')
declare @NewMyDBLogFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB_log', 'MyDB_log')
SET @Sql = 'CREATE DATABASE MyDB ON ( FILENAME = ''' + @NewMyDBFileNameAfterRename + '''), ( FILENAME = ''' + @NewMyDBLogFileNameAfterRename + ''') FOR ATTACH'
PRINT @Sql
EXEC (@Sql)
ALTER DATABASE MyDB SET MULTI_USER
END
-- Disable xp_cmdshell for security reasons:
GO
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE
语句来完成 - 就像这样:ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_file_name,
FILENAME = ' new_path/os_file_name_with_extension ' )
您需要单独修改每个文件,例如,如果您有多个数据文件,您需要修改每个文件。
详细信息请参阅Technet文档。
重命名 SQL Server 物理数据库文件
最简单的方法是:
SQL Server
。OldDatabaseName
" 替换为你想要将其名称更改为的数据库的新名称 ("NewDatabaseName
")。替换所有的 NewDatabaseName
为你想要为数据库设置的新名称。use OldDatabaseName
ALTER DATABASE OldDabaseName MODIFY FILE (NAME='OldDatabaseName', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName.mdf');
ALTER DATABASE OldDatabaseName MODIFY FILE (NAME='OldDatabaseName_log', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName_log.ldf');
ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName, NEWNAME = NewDatabaseName);
ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName_log, NEWNAME = NewDatabaseName_log);
然后右键单击 OldDatabaseName
,选择 Tasks
,然后选择 Take Offline
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\...
),将它们重命名为您在第2步中指定的NewDatabaseName
。请记住检查这些文件的绝对路径是否适用于您的计算机。Microsoft SQL Server Management Studio
。右键单击 OldDatabaseName
,选择 Tasks
,然后选择 Bring Online
。OldDatabaseName
重命名为NewDatabaseName
。完成啦 :-)ALTER DATABASE NewDatabaseName SET OFFLINE
,然后在执行 ALTER DATABASE NewDatabaseName SET ONLINE
之前进行文件名更改。 - cusman我偶然发现了这个旧帖子,但是没有一个答案完全符合我的要求,而且我不知道为什么忽略了Sverre的回答。由于我需要清理很多可怕的不一致的低版本SQL服务器数据库文件位置并合并磁盘/文件夹结构,所以我整理了这个代码。希望能对某些人有所帮助。
-- create a script that generates necessary commands to relocate a set of databases from
-- one location to another and some other stuff
-- 1) offline database
-- 2) alter databases modify files
-- 3) generates OS file move commands
-- 3.5) includes renaming the files to match database AND file type/enumeration
-- 4) online databaases databases using create database for attach commands
--== declare some configuration variavbles ==--
DECLARE @newpath SYSNAME = 'G:\DefaultInstance\QA'
DECLARE @databasewildcard SYSNAME = '%_NEW'
--== start with a commands table ==--
IF OBJECT_ID('tempdb..#commands') IS NOT NULL DROP TABLE #commands
CREATE TABLE #commands ([id] INT IDENTITY, [command] VARCHAR(MAX))
IF OBJECT_ID('tempdb..#excludedatabases') IS NOT NULL DROP TABLE #excludedatabases
CREATE TABLE #excludedatabases ([id] INT IDENTITY, [database] SYSNAME)
INSERT INTO #excludedatabases ([database]) VALUES ('exceptiondatabase_NEW')
--== insert commands : offline databases ==--
INSERT INTO #commands ([command])
SELECT
--'EXEC master.dbo.sp_detach_db @dbname = N' + QUOTENAME(sd.[name],'''')+ ';'
'USE [master]; ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
FROM sys.databases sd
WHERE sd.[name] LIKE @databasewildcard
AND sd.[name] NOT IN (SELECT
ed.[database]
FROM #excludedatabases ed)
--== insert commands : modify logical physical file name ==--
INSERT INTO #commands ([command])
SELECT
'ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' MODIFY FILE (NAME = N' + QUOTENAME(smf.[name],'''') + ', FILENAME = N' + QUOTENAME(@newpath + '\'
+ CASE
WHEN smf.[type_desc] = 'ROWS' THEN sd.[name] + '_data_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.mdf'
WHEN smf.[type_desc] = 'LOG' THEN sd.[name] + '_log_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.ldf'
END
,'''') + ');'
FROM sys.master_files smf
JOIN sys.databases sd ON smf.[database_id] = sd.[database_id]
WHERE sd.[name] LIKE @databasewildcard
AND sd.[name] NOT IN (SELECT
ed.[database]
FROM #excludedatabases ed)
--== insert commands : generate OS move commands ==--
INSERT INTO #commands ([command]) VALUES ('/* --===== comment out these commands as a block - execute in file system =====--')
INSERT INTO #commands ([command])
SELECT
--'MOVE ' + QUOTENAME(smf.[physical_name],'"') + ' ' + QUOTENAME(@newpath + '\','"') AS [command]
'MOVE ' + QUOTENAME(smf.[physical_name],'"') + ' ' + QUOTENAME(@newpath + '\'
+ CASE
WHEN smf.[type_desc] = 'ROWS' THEN sd.[name] + '_data_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.mdf'
WHEN smf.[type_desc] = 'LOG' THEN sd.[name] + '_log_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.ldf'
END
,'"') AS [command]
FROM sys.master_files smf
JOIN sys.databases sd ON smf.[database_id] = sd.[database_id]
WHERE sd.[name] LIKE @databasewildcard
AND sd.[name] NOT IN (SELECT
ed.[database]
FROM #excludedatabases ed)
INSERT INTO #commands ([command]) VALUES ('*/ --===== comment out these commands as a block - execute in file system =====--')
--== insert commands : online databases ==--
INSERT INTO #commands ([command])
SELECT
'USE [master]; ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' SET ONLINE WITH ROLLBACK IMMEDIATE;'
FROM sys.databases sd
WHERE sd.[name] LIKE @databasewildcard
AND sd.[name] NOT IN (SELECT
ed.[database]
FROM #excludedatabases ed)
SELECT * FROM #commands