Can't restore to custom data folder

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Can't restore to custom data folder

Author

Topic

sgilmour
Starting Member

1 Post

Posted - 2008-06-03 : 23:35:39

I am having a problem with a client's MS SQL Server 2005. When doing a restore, SQL will not allow me to restore to the database mdf and ldf files outside the SQL root folder (C:\Program Files\Microsoft SQL Server). I want the data and log files to be on a separate disk with plenty of disk space. When I created the database, SQL allowed me to create the mdf and ldf files in the E:\Data SQL Server\Data folder. But when I perform the restore SQL reverts to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. I am specifically redirecting the mdf and ldf files to the custom folder by clicking on the button (...) to change the folder.

I have tried creating a new SQL Server instance and uninstalling and reinstalling SQL Server to no avail.

How can I force the restore task to restore my database to the custom data folder location?

Thanks

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-06-04 : 02:05:39

Try RESTORE DATABASE statement WITH MOVE clause.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-04 : 09:01:48

You can't restore with MDF and LDF files. First of all you have to backup database and use that file while restoring ,assign data file and log files to separate drives. You can attach MDF and LDF with sp_attach_db but the sql services has to be stopped if it is being used.

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-05 : 14:59:14

quote:
Originally posted by sgilmour

I am having a problem with a client's MS SQL Server 2005. When doing a restore, SQL will not allow me to restore to the database mdf and ldf files outside the SQL root folder (C:\Program Files\Microsoft SQL Server). I want the data and log files to be on a separate disk with plenty of disk space. When I created the database, SQL allowed me to create the mdf and ldf files in the E:\Data SQL Server\Data folder. But when I perform the restore SQL reverts to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. I am specifically redirecting the mdf and ldf files to the custom folder by clicking on the button (...) to change the folder.

I have tried creating a new SQL Server instance and uninstalling and reinstalling SQL Server to no avail.

How can I force the restore task to restore my database to the custom data folder location?

Thanks

No need to uninstall and reinstall for restore the DB. You just need to manually mode mdf and ldf files from existing to target folder. Before move take the Backup and then stop the SQL Engine services.

After move Start SQL engine services and Agent and check the DB health.

That's it you are done.....

Manoj
MCP, MCTS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-05 : 22:44:50

Don't need stop sql to move db files, just detach the db.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-06 : 00:16:15

[/quote] No need to uninstall and reinstall for restore the DB. You just need to manually mode mdf and ldf files from existing to target folder. Before move take the Backup and then stop the SQL Engine services.After move Start SQL engine services and Agent and check the DB health.

That's it you are done.....

Manoj
MCP, MCTS
[/quote]

Strange, Why you wanna stop SQL server just to restore 1 database ?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-06 : 09:26:38

quote:
Originally posted by sodeep
Strange, Why you wanna stop SQL server just to restore 1 database ?

No doubt, all I do is a ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Before the backup and before the restore. Of course the backup or restore statements are followed with a ALTER DATABASE <dbname> SET MULTI_USER

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-06 : 16:05:52

Strange, Why you wanna stop SQL server just to restore 1 database ?
[/quote]
How you could play with live DB. you want to move DB's Data and log file. There may be chances of currept of the DB.

Manoj
MCP, MCTS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-06 : 23:38:16

Still remember detach db? That's why don't need stop sql.

oorja
Starting Member

1 Post

Posted - 2008-06-26 : 09:55:09

Are you trying to test restore running the application in IDE.Try to run the application from the project folder without IDE and see if it does restore.I had the same problem and I found out that when i run the exe file it does the restore but if run in IDE restore would fail.

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 11:25:02

RESTORE FILELISTONLY FROM DISK = '[BACKUP FILE NAME]'
RESTORE DATABASE [DBMAME]
WITH REPLACE,
MOVE 'LOGICAL DATA FILE NAME' to 'PATH TO RESTORE WITH NEW NAME.MDF',
MOVE 'LOGICAL LOG FILE NAME' to 'PATH TO RESTORE WITH NEW NAME.LDF'
The WITH REPLACE clause will overwrite the database in place.
or you can remove it if you are restoring the backup to a NEW NAMe and diff location OR NEWNAMe and SAME location with new MDF and LDF Names
AFTER the Restore is complete, change the Logical Name of the Database Files

ALTER DATABASe [DBNAME]
MODIFY FILE (NAME='logical_file_name',NEWNAME='New_logical_file_name')

Maninder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources