blocks|key|3168048|text|您忘记了提及数据库的名称(是“my”吗？)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3168049|ALTER+DATABASE+my+SET+SINGLE_USER+WITH+ROLLBACK+IMMEDIATE;

ALTER+DATABASE+my+SET+OFFLINE;

ALTER+DATABASE+my+MODIFY+FILE+
(
+++Name+=+my_Data,
+++Filename+=+'D:\DATA\my.MDF'
);

ALTER+DATABASE+my+MODIFY+FILE+
(
+++Name+=+my_Log,+
+++Filename+=+'D:\DATA\my_1.LDF'
);|code-block|syntax|javascript|3168050|现在，您必须手动将文件从它们的当前位置移动到D:\Data\+(如果您在修改文件命令中更改了它们，请记住手动重命名它们)+...然后，您可以使数据库重新联机：|3168051|ALTER+DATABASE+my+SET+ONLINE;

ALTER+DATABASE+my+SET+MULTI_USER;|3168052|这假设SQL+Server服务帐户对D:\Data\文件夹具有足够的权限。如果不是，您将在SET+ONLINE命令中收到错误。|3168053|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You forgot to mention the name of your database (is it "my"?).

<pre><code>ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE 
(
 Name = my_Data,
 Filename = 'D:\DATA\my.MDF'
);

ALTER DATABASE my MODIFY FILE 
(
 Name = my_Log, 
 Filename = 'D:\DATA\my_1.LDF'
);
</code></pre>

Now here you must manually move the files from their current location to D:\Data\ (and remember to rename them manually if you changed them in the MODIFY FILE command) ... then you can bring the database back online:

<pre><code>ALTER DATABASE my SET ONLINE;

ALTER DATABASE my SET MULTI_USER;
</code></pre>

This assumes that the SQL Server service account has sufficient privileges on the D:\Data\ folder. If not you will receive errors at the SET ONLINE command.

blocks|key|264216|text|可以在SQL+Server+Management+Studio中使用分离/附加选项。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|264217|检查这个：Move+a+Database+Using+Detach+and+Attach|offset|length|264218|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187858.aspx^0|0|5|13|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can use Detach/Attach Option in SQL Server Management Studio.

Check this: <a href="http://msdn.microsoft.com/en-us/library/ms187858.aspx">Move a Database Using Detach and Attach</a>

blocks|key|2954166|text|要向文件添加所需的特权，请添加以下本地用户并授予权限:+SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME，其中COMPUTERNAME和INSTANCENAME必须分别替换为计算机的名称和MSSQL实例的名称。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2954167|entityMap^0|23|C|2G|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

To add the privileges needed to the files add and grant right to the following local user: SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME, where COMPUTERNAME and INSTANCENAME has to be replaced with name of computer and MSSQL instance respectively.

blocks|key|2954196|text|这是将数据库和登录信息从一个实例传输到新实例的完整过程，包括编写登录脚本以及重新定位目标上的数据文件和日志文件。一切都使用元脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2954197|http://zaboilab.com/sql-server-toolbox/massive-database-migration-between-sql-server-instances-the-complete-procedure|offset|length|2954198|对于非现场的过程，很抱歉，但是脚本非常长。您必须：|2954199|使用原始SID和哈希password|2954200|Create脚本登录|unordered-list-item|2954201|
|2954202|脚本来备份数据库，使用metascripts|2954203|2954204|Create脚本来恢复数据库，使用重新metascripts|2954205|2954206|Run源和目标实例上生成的脚本传递重定位参数。|2954207|2954208|单击上面的链接查看详细信息并下载脚本。|2954209|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|39|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|F|3|G|5|6|7|1B|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1C|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1D|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|L|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|N|5|6|7|1G|8|@]|9|@]|A|$]]|$1|R|3|S|5|L|7|1H|8|@]|9|@]|A|$]]|$1|T|3|N|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1J|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|10|$11|$5|12|13|14|A|$15|C]]]]

This is a complete procedure to transfer database and logins from an istance to a new one, scripting logins and relocating datafile and log files on the destination. Everything using metascripts.

<a href="http://zaboilab.com/sql-server-toolbox/massive-database-migration-between-sql-server-instances-the-complete-procedure" rel="nofollow">http://zaboilab.com/sql-server-toolbox/massive-database-migration-between-sql-server-instances-the-complete-procedure</a>

Sorry for the off-site procedure but scripts are very long. You have to: 
- Script logins with original SID and HASHED password 
- Create script to backup database using metascripts 
- Create script to restore database passing relocate parameters using again metascripts 
- Run the generated scripts on source and destination instance. 
See details and download scripts following the link above.

blocks|key|260247|text|补充ALTER+DATABASE过程的一些注意事项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260248|1)您可以获取数据库的完整列表，其中包含MDF和LDF文件的逻辑名称和完整路径：|260249|+++USE+master+SELECT+name,+physical_name+FROM+sys.master_files|code-block|syntax|javascript|260250|2)可以通过CMD+move命令手动移动文件：|260251|移动“源”“目标”|260252|示例：|260253|md+"D:\MSSQLData"
Move+"C:\test\SYSADMIT-DB.mdf"+"D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move+"C:\test\SYSADMIT-DB_log.ldf"+"D:\MSSQLData\SYSADMIT-DB_log.ldf"|260254|3)您应该更改创建新数据库的默认数据库路径。默认路径是从Windows注册表获取的。|260255|您还可以使用T-SQL进行更改，例如，将默认目标设置为:+D:\MSSQLData|260256|USE+[master]

GO

EXEC+xp_instance_regwrite+N'HKEY_LOCAL_MACHINE',+N'Software\Microsoft\MSSQLServer\MSSQLServer',+N'DefaultData',+REG_SZ,+N'D:\MSSQLData'

GO

EXEC+xp_instance_regwrite+N'HKEY_LOCAL_MACHINE',+N'Software\Microsoft\MSSQLServer\MSSQLServer',+N'DefaultLog',+REG_SZ,+N'D:\MSSQLData'

GO|260257|摘自：http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html|offset|length|260258|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html^0|0|0|0|0|0|0|0|0|0|0|3|28|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1B|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1C|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1D|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1F|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1G|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|1H|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1I|8|@]|9|@$Y|1J|Z|1K|1|1L]]|A|$]]|$1|10|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17]]]]

Some notes to complement the ALTER DATABASE process:

1) You can obtain a full list of databases with logical names and full paths of MDF and LDF files:

<pre><code> USE master SELECT name, physical_name FROM sys.master_files
</code></pre>

2) You can move manually the files with CMD move command:

Move "Source" "Destination"

Example:

<pre><code>md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"
</code></pre>

3) You should change the default database path for new databases creation. The default path is obtained from the Windows registry.

You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData

<pre><code>USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO
</code></pre>

Extracted from: <a href="http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html" rel="nofollow">http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html</a>

Logical Name

<ul>
<li><code>my_Data</code></li>
<li><code>my_Log</code></li>
</ul>

Path:

<ul>
<li><code>C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA</code></li>
<li><code>C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA</code></li>
</ul>

FileName:

<ul>
<li><code>my.MDF</code></li>
<li><code>my_1.LDF</code></li>
</ul>

What would be the sql script to move these files to a new location: <code>D:\DATA</code>

Database is live so I would need to close existing connections.

Move SQL Server 2008 database files to a new folder location

备份数据库

日志文件

SQL Server

数据库

Windows

逻辑名称my_Datamy_Log路径：C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATAC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATAFile...

问将SQL Server 2008数据库文件移动到新的文件夹位置
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将SQL Server 2008数据库文件移动到新的文件夹位置EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将SQL Server 2008数据库文件移动到新的文件夹位置
EN