Export Author: htl258 (Tony) -- Date: 2010-06-2621: 51: 30 -- Version: MicrosoftSQLServer2008 (RTM)
Feature -- Author: htl258 (Tony) -- Date: 2010-06-26 21:51:30 -- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copy
----------------------------------------------------------------------------------
-- Author: htl258 (Tony)
-- Date: 2010-06-26 21:51:30
-- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
-- Blog: (repost and retain this information)
-- Subject: SQL queries and modifies the logical file name of a database. Example of a mobile database storage path
----------------------------------------------------------------------------------
USE mydb
GO
-- 1. query the logical File Name of the current database
SELECT FILE_NAME (1) AS 'file Name 1', FILE_NAME (2) AS 'file Name 2 ';
-- Or use the following statement to query:
-- SELECT name FROM sys. database_files
/*
File Name 1 File Name 2
---------------------------------------
Mydb mydb_log
(One row is affected)
*/
-- 2. Modify the logical name of the data file or log file
Alter database [mydb]
Modify file (NAME = mydb, NEWNAME = new_mydb)
Alter database [mydb]
Modify file (NAME = mydb_log, NEWNAME = new_mydb_log)
-- Query the changed name:
SELECT FILE_NAME (1) AS 'file Name 1', FILE_NAME (2) AS 'file Name 2 ';
/*
File Name 1 File Name 2
---------------------------------------
New_mydb new_mydb_log
(One row is affected)
*/
-- 3. If you want to MOVE the data file or log file to a new location, MOVE the file to the corresponding location OFFLINE first, and then ONLINE
-- For details, refer to the following steps:
-- 3.1 query the current storage path of the data file or log file:
SELECT physical_name FROM sys. database_files
/*
Physical_name
Bytes -------------------------------------------------------------------------------------------
D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/mydb. mdf
D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/mydb_log.ldf
(2 rows affected)
*/
-- 3.2 mobile path
USE [master]
GO
-- 3.2.1 close all processes
DECLARE @ str VARCHAR (500 );
SET @ str = '';
SELECT @ str = @ str + 'Kill '+ RTRIM (spid) +'; '+ CHAR (13) + CHAR (10)
FROM master. dbo. sysprocesses
WHERE dbid = DB_ID ('mydb ');
EXEC (@ str );
GO
-- 3.2.2 set the database to go offline
Alter database [mydb] SET OFFLINE
GO
-- 3.2.3 move the data file and log file (to ensure data security, we recommend that you move the file to copy and delete the source file after the copy operation is successful)
EXEC master.. xp_mongoshell 'move "D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/mydb. mdf" "E:/DBTEST" ', NO_OUTPUT
EXEC master.. xp_mongoshell 'move "D:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/mydb_log.LDF" "E:/DBTEST" ', NO_OUTPUT
GO
-- 3.2.4 set a new storage path
Alter database [mydb] modify file (NAME = new_mydb, FILENAME = 'e:/DBTEST/mydb. mdf ')
Alter database [mydb] modify file (NAME = new_mydb_log, FILENAME = 'e:/DBTEST/mydb_log.ldf ')
GO
-- 3.2.5 set the database to be online
Alter database [mydb] SET ONLINE
GO
-- 3.2.6 query the new path
USE mydb
GO
SELECT physical_name FROM sys. database_files
/*
Physical_name
------------------------------
E:/DBTEST/mydb. mdf
E:/DBTEST/mydb_log.ldf
(2 rows affected)
*/
,