February 18, 2009 at 4:59 am
In SQL Server 2005, i am modifying a database name and moving the files via the below command.
alter database test_DB_rest
modify name = test_DB
GO
Alter database test_db
modify file
(
name = test_DB,
Filename = N'T:\test\test_db.mdf'
)
GO
Alter database test_db
modify file
(
name = test_DB_log,
Filename = N'T:\test\test_DB_log.ldf'
)
once this is run i get the following message from SQL Server:
The file "test_DB_log" has been modified in the system catalog. The new path will be used the next time the database is started.
Do i now have to restart the MSSQL service ? Is there a way to rename and move a database without having to restart the database service? I have to do this via automated job and cant restart the service.
February 18, 2009 at 5:22 am
Are you aware that ALTER DATABASE .. MODIFY FILE does not actually move the files? It just changes the metadata for the database saying where the files are. Once you've run that you need to take the DB offline, move the files and then bring the DB online again.
From Books Online:
To move a data or log file as part of a planned relocation, follow these steps:
Run the following statement.
ALTER DATABASE database_name SET OFFLINEMove the file or files to the new location.
For each file moved, run the following statement.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )Run the following statement.
ALTER DATABASE database_name SET ONLINE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 11:32 am
Cool, careless of me to miss that in BOL. thanks.
Ok. so what im doing now is this:
alter database test_DB
set offline
Go
alter database test_DB
modify file
(
name = test_DB,
Filename = N'C:\DatabaseRestoreTest\Files_location\test_DB_temp.mdf'
)
GO
alter database test_DB
modify file
(
name = test_DB_log,
Filename = N'C:\DatabaseRestoreTest\Files_location\test_DB_temp_1.ldf'
)
GO
I then physically copy the files via windows explorer. when i try to set the database to online after this i get this error:
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "C:\DatabaseRestoreTest\Files_location\test_DB_temp.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "C:\DatabaseRestoreTest\Files_location\test_DB_temp_1.ldf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 945, Level 14, State 2, Line 2
Database 'test_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
If i alter the database again to point to the original location the error goes away. any ideas?
February 18, 2009 at 11:57 am
winston Smith (2/18/2009)
Cool, careless of me to miss that in BOL. thanks.Ok. so what im doing now is this:
alter database test_DB
set offline
Go
alter database test_DB
modify file
(
name = test_DB,
Filename = N'C:\DatabaseRestoreTest\Files_location\test_DB_temp.mdf'
)
GO
alter database test_DB
modify file
(
name = test_DB_log,
Filename = N'C:\DatabaseRestoreTest\Files_location\test_DB_temp_1.ldf'
)
GO
I then physically copy the files via windows explorer. when i try to set the database to online after this i get this error:
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "C:\DatabaseRestoreTest\Files_location\test_DB_temp.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "C:\DatabaseRestoreTest\Files_location\test_DB_temp_1.ldf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Msg 945, Level 14, State 2, Line 2
Database 'test_DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
If i alter the database again to point to the original location the error goes away. any ideas?
You must grant FULL permissions to the Service account (group actually) on the destination Folder!
* Noel
February 18, 2009 at 1:11 pm
winston Smith (2/18/2009)
Msg 5120, Level 16, State 101, Line 2Unable to open the physical file "C:\DatabaseRestoreTest\Files_location\test_DB_temp.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".
Google tells me that Operating System Error 5 is "Access is denied."
Translation - the SQL service account does not have permission on the folder where the files are now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 1:48 pm
I cant test now as i dont have my laptop home with me, but thats strange, as the folder that i created the datbase in was created at the same time, by me, as the folder im trying to move the files to.
when the database was created it doesnt have this problem, only on moving the files. il try explicitly giving the sql server account full permissions on the folder regardless and see if that helps or at least throws up some more helpful error. thanks.
il let folks know how it goes, so as to keep the thread complete.
February 19, 2009 at 1:42 am
On my laptop the sql server service was running under Network Service. I know not its not the way it should be, but its not a live production system, just a playground. anyway i created a new local account, granted added it to the admin group and granted it admin permissions on my DB.
I can now indeed bring the database online.
i still find it strange that although i can create a database and its files using Network Service as the service account, i cannot move files and then set the database online again, especially since the before and after folders for the files both have same security settings and permissions.
il start removing some privileges now on my local account to see whats the minimium i can get away wtih for this operation. thanks again folks for the help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply