Collections:
Moving Database Physical Files to New Locations in SQL Server
How to move database physical files in SQL Server?
✍: FYIcenter.com
If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example:
ALTER DATABASE FyiCenterComData SET ONLINE GO USE FyiCenterComData GO CREATE TABLE Links (Name NVARCHAR(32)) GO ALTER DATABASE FyiCenterComData SET OFFLINE GO
Now it is safe to move the database physical files to a new location:
1. Run Windows Explorer 2. Create a new directory: c:\temp\data 3. Drag and drop c:\temp\FyiCenterData.mdf to c:\temp\data 3. Drag and drop c:\temp\FyiCenterLog.mdf to c:\temp\data
Go back to the SQL client program and run:
ALTER DATABASE FyiCenterComData MODIFY FILE (NAME = FyiCenterData, FILENAME = 'C:\temp\data\FyiCenterData.mdf') GO The file "FyiCenterData" has been modified in the system catalog. The new path will be used the next time the database is started. ALTER DATABASE FyiCenterComData MODIFY FILE (NAME = FyiCenterLog, FILENAME = 'C:\temp\data\FyiCenterLog.ldf') GO The file "FyiCenterLog" has been modified in the system catalog. The new path will be used the next time the database is started. ALTER DATABASE FyiCenterComData SET ONLINE GO USE FyiCenterComData GO SELECT type_desc, name, physical_name, size FROM sys.database_files GO type_desc name physical_name size ROWS FyiCenterData C:\temp\data\FyiCenterData.mdf 1280 LOG FyiCenterLog C:\temp\data\FyiCenterLog.ldf 128 SELECT name, type_desc FROM sys.tables GO name type_desc Links USER_TABLE
As you can see the physical files are moved properly, and the "Links" table is still there.
⇒ READ_ONLY/READ_WRITE - Database Update Options in SQL Server
⇐ OFFLINE - Taking a database offline in SQL Server
2016-11-20, 1708🔥, 0💬
Popular Posts:
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...