Unanswered: facing problem in changing the PATH of data/Log file
My name is Navaid Arif and i am a new user of SQL Server DB. The project on which i am working in on SQL Server 2005 as a Production database. Recently, i came to know that the data and log files used in this database and stored on the default location i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. and i need to change this path to some other location with better storage.
I took a backup using following command:
backup database myProdDB
to disk='D:\Database Backup\Exported Data\myProdDB_backup20090307.bak'
with init, stats;
and tried to recreate the database by dropping and recreating it. And during recreating, changing the path of these data/log file and restore the data in this recreated database using:
restore database fun
from disk='D:\Database Backup\Exported Data\myProdDB_backup20090307.bak'
with replace, stats;
But facing error while dropping the database "DROP FAILED FOR DATABASE 'myProdDB'. (Microsoft.SqlServer.Smo)"
Can anyone help me in changing the PATH of these Data/Log files with NO DATA LOSS.
Hi , have you tried something like:
RESTORE DATABASE MyDB
FROM DISK = ‘E:\BackUpMyBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:\DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:\DataYourLDFFile.mdf’
make sure that no other sessions of the particular database is are running before trying to restore.
But, why do you want to restore the Database for changing the data and log path? You can dettach the Database then move the files to the desired location and attach it agian.
use the following TSQL
1) sp_detach_db <DB_NAME>
move the files
2) sp_attach_db <DB_NAME>,'New location of your data file'