Restore into new DB - SQL Server Forums

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)

Restore into new DB

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 13:30:06

I need to restore a backup file from a database into a new database. I have used the RESTORE command with the MOVE option but it will not work.

I get the error "It is being used by database..."

Help is greatly appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 14:17:47

You need to rename the physical file, or save it to a new path:

RESTORE DATABASE myDB FROM DISK='Z:\MyBackup.bak'
WITH MOVE 'DataFile' TO 'C:\NewPath\NewDataFile.mdf',
MOVE 'LogFile' TO 'D:\NewPath\NewLogFile.ldf'

fralo
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 14:36:21

I have renamed the file and moved it to a different location and it doesn't work. Just to be sure I'm doing this right since I'm new at this, here's my syntax.

RESTORE DATABASE mynewDB from disk = 'file_name.bak'
with move 'Intranet_data' to '<data log path>',
move 'Intranet_log' to '<data log path>'

The paths are retrieved by doing this:

RESTORE FILELISTONLY
FROM DISK = 'file_name.bak'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 14:38:21

Are you still getting the same error? Are you actually running it with '<data log path>', because that won't work. Can you include the actual file names that you're trying to restore?

fralo
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 14:46:19

No, that was just for the purpose of posting a message.

Here's a closer version to what I'm actually running. I don't wanna put the actual the server names and such.

restore database test_db
from disk = '\\mysql\z$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_2009032403.bak'
with move 'ESO_Intranet_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ECSO\ESO_Intranet_Data.MDF',
move 'ESO_Intranet_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ESO_Intranet_Log.LDF'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 14:50:33

If you're still getting that error then it's because you have an active database already using ESO_Intranet_Data.MDF or ESO_Intranet_Log.LDF files in that folder. Try this:

restore database test_db
from disk = '\\mysql\z$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_2009032403.bak'
with move 'ESO_Intranet_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ECSO\test_db.MDF',
move 'ESO_Intranet_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_db_Log.LDF'

fralo
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 14:53:52

Ah, progress.

It now tells me to add the 'WITH REPLACE' option. But I'm unsure of the syntax.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 15:06:35

restore database test_db
from disk = '\\mysql\z$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_2009032403.bak'
with replace,
move 'ESO_Intranet_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ECSO\test_db.MDF',
move 'ESO_Intranet_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_db_Log.LDF'

Do you already have a database named test_db? Do you want to overwrite it? If not, then change the database name in the restore command, and optionally change the filenames as well.

fralo
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 15:24:22

Thanks for your help. That will do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources