Backup and restore to the same machine

We are using SQL Server 2005.
I did a full back up of myDatabase. I would like to restore it to a
different database name on the same machine (I still want the original
database to be there), can I do that ?
On the Restore database, under the "To database" I typed in "myNewDatabase",
and on the "From device" I selected the full backup that I made.
When I try to restore it, it gave me an error "The file c:\program
files\...\myDatabase.mdf" cannot be overwritten. It is being used by
database 'myDatabase'. (Microsoft.SQLServer.Smo).
So, does it mean that even though I restore it to a different database name,
I can not restore the database in the same machine ?
Thank you.
fniles,
You also need to use the BACKUP feature to MOVE Filename TO
NewPhysicalFileName.
If you are using the SQL Server Management Studio to do this, on the RESTORE
dialog, click on the OPTIONS tab. You can give new file names to the
restoring mdf and ldg there.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:eq5y9tVTIHA.5980@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in
> "myNewDatabase", and on the "From device" I selected the full backup that
> I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database
> name, I can not restore the database in the same machine ?
> Thank you.
>
|||It can be done .. do this:
1) Create a new database called 'myDB2'.
2) Right click on the myDB2, and go to Restore.
3) Select the From Device to the BAK file you created from myDatabase.
4) Under options, select overwrite.
5) Change the location of the data file and log file to that of myDB2. So
it can over write the files for this database to match what is in your backup.
That should work for yaa ;-).
Or you can run the following script:
RESTORE DATABASE [myDB2]
FROM DISK = 'D:\MSSQL\Backup\myDatabase.bak'
WITH FILE = 1,
MOVE N'myDatabase_dat' TO 'D:\MSSQL\DATA\myDB2_dat.mdf'
MOVE N'myDatabase_log' TO 'E:\MSSQL\LOG\myDB2.ldf',
NOUNLOAD, REPLACE, STATS = 10
After creating the database
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||Hi
You will need to use the move option for the restore command see example E at
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
John
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||First line should have said: ... use the RESTORE feature to MOVE Filename TO
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OMqsA2VTIHA.1164@.TK2MSFTNGP02.phx.gbl...
> fniles,
> You also need to use the BACKUP feature to MOVE Filename TO
> NewPhysicalFileName.
> If you are using the SQL Server Management Studio to do this, on the
> RESTORE dialog, click on the OPTIONS tab. You can give new file names to
> the restoring mdf and ldg there.
> RLF
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eq5y9tVTIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||Hi
I should have added use RESTORE FILELISTONLY to get the logical file names
of you don't know what they are, or look at the existing database. Logical
file names do not have to be different between databases (and in this case
will not unless you change them later!)
John
"fniles" wrote:

> We are using SQL Server 2005.
> I did a full back up of myDatabase. I would like to restore it to a
> different database name on the same machine (I still want the original
> database to be there), can I do that ?
> On the Restore database, under the "To database" I typed in "myNewDatabase",
> and on the "From device" I selected the full backup that I made.
> When I try to restore it, it gave me an error "The file c:\program
> files\...\myDatabase.mdf" cannot be overwritten. It is being used by
> database 'myDatabase'. (Microsoft.SQLServer.Smo).
> So, does it mean that even though I restore it to a different database name,
> I can not restore the database in the same machine ?
> Thank you.
>
>
|||"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23$DDE5VTIHA.5524@.TK2MSFTNGP05.phx.gbl...
> First line should have said: ... use the RESTORE feature to MOVE Filename
> TO
>
And here I was thinking that was a nifty new feature I was going to try out.
(I actually could think of cases where it would be useful.)

> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OMqsA2VTIHA.1164@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html