Do you need to back up before you can restore?

[mflancour]

I have 2 copies of the same database. DB1 and DB2. I did a full backup of db2 and now want to restore it to db1 (to bring db1 back up to date). Since db1 has never been backed up, do I need to do that before it will let me restore to it? I ask because when I try restoring now, it gives me a bunch of errors about not finding the backup file.

 

[ptheriault]

no you don't need to back up the database first. Can you post your errors?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

Msg 1834, Level 16, State 1, Line 3
The file 'E:\sqldata\Server1\DB1_PRIMARY.mdf' cannot be overwritten. It is being used by database 'OEN'.
Msg 3156, Level 16, State 4, Line 3
File 'OEN_Data' cannot be restored to 'E:\sqldata\Server1\DB1_PRIMARY.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 3
The file 'C:\sqldata\Server1\DB1_INDEX.ndf' cannot be overwritten. It is being used by database 'OEN'.
Msg 3156, Level 16, State 4, Line 3
File 'OEN_IDX' cannot be restored to 'C:\sqldata\Server1\DB1_INDEX.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 3
The file 'C:\sqldata\Server1\DB1_LOG.ldf' cannot be overwritten. It is being used by database 'OEN'.
Msg 3156, Level 16, State 4, Line 3
File 'OEN_Log' cannot be restored to 'C:\sqldata\Server1\DB1_LOG.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

 

[ptheriault]

you need to use the WITH MOVE option.

here is the syntax from BOL.

RESTORE DATABASE TestDB 
   FROM DISK = 'C:\AdventureWorks.bak'
   WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'
GO

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

I managed to work past the erorr. Seems the server needed to be bounced. Anyway, now I have a space issue.
The database is 15 gig, I have 6 gig free. I'd thought that using the replace function would help me with this, but apparently not. Just to be clear,
db1 = 15 gig, Server1 has 6 gig free
db2 = 15gig
I am restoring from db2 to db1 using "with recover, replace, move...

 

[ptheriault]

if you don't have 15 GB then you won't be able to restore that db. Even if you were to truncate all the data from that database and left the files at 15 GB you would still need 15GB to restore.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

Well, I have the 15g, it's just tied up in teh old version of the same database, so I dropped it. Just thought there was a way to "replace" it instead of going through the extra steps.