Wednesday, October 6, 2010

How To Move SQL Server Databases To A New Location

Follow below steps for moving data or log files from one location to another location.

Step 1:  Run the following statement for pushing the database to Offline mode.

ALTER DATABASE database_name SET OFFLINE; 

Step 2:  Physically Cut from Source and Move the file or files to the new location.

Step 3:  Run below statement for each file move. 

ALTER DATABASEDatabase_Name
MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ); 

Step 4:  Run the following statement for bringing the database to Online from Offline Mode.

ALTER DATABASE database_name SET ONLINE; 

Step 5:  Once above steps completed for all the files, then verify the file change by running the below query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
How to move SQL Server databases to a new location. Moving the tempdb database. How to Move TempDB. How To Move TEMPDB Files. Moving the Tempdb and Master Database in SQL Server. TempDB is Full. Move TempDB from one drive to another drive. How to move TempDB to RAM in SQL Server 2005 or 2008 or 2012. How to Move TempDB in sql server 2012. How to Move TempDB in sql server 2008 R2. How to Move TempDB in sql server 2005. move databases. move data files in sql server. move data file. move log file.

0 comments:

Post a Comment