Restore from an existing database

I am trying to create a new development database on SQL server version 2000 using 2005 instance by restoring from an existing Live database.
So I right-click -> restore.
To: MyDevDatabase
From: MyLiveDatabase

Now it says "Select the backup sets to restore".

The only option it gives is the back up of the Live database. If I select that and proceed, it throws an error saying "Database to be restored was named MyLiveDatabase. Reissue the statement by using the WITH REPLACE option..."

I found that I have to go to Options and check "Overwrite existing database". Is that right?

Which database will that overwrite, MyDevDatabase? And what does selecting a backup set mean? Is the Mydevdatabase going to be restored from the selected backup? Is this going to affect the Live database or its back up in any way?
Angel02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
I would *not* select "Overwrite Existing Database".

Go through the Restore GUI again and do *not* click on "Okay" at the end, but rather click on "Script" up top and then "Cancel" in the Restore GUI.  Post the generated SQL script code here and we can help track down the problem.
0
Angel02Author Commented:
Ok. here is the script

RESTORE DATABASE [MyDevDatabase] FROM  DISK = N'E:\Company SQL\Backups\MyLiveDatabase backup.BAK' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
0
Aneesh RetnakaranDatabase AdministratorCommented:
If you are planning to restore a 2005 database onto a 200 server, it wont work; you need to have a 2005 dev server for this
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Angel02Author Commented:
The Live database version is 2000. It is on 2005 instance. I am creating the Dev Database also on 2005 instance. What do you suggest?
0
nemws1Database AdministratorCommented:
okay, you're missing the "MOVE" statements as a part of this.  In the restore GUI, click on "Options" to the left and then select destinations for the "Rows Data" and "Log" file types under "Restore the database files as".

It's trying to overwrite the existing Data/Log files for your MyLiveDatabase (since the MOVE statements aren't there) and we don't want it to do that.

Try this and post your code again.
0
nemws1Database AdministratorCommented:
There must be another thread I'm not aware of here... I don't see any mention of SQL versions above.  If you're trying to copy a database that's already on your server, you shouldn't have any worries about versioning.
0
Aneesh RetnakaranDatabase AdministratorCommented:
If you are running the above steps you mentioned on the Developer server,  it wont overwrite the production database. if you are not sure on which server you are running, close the sql server management studio, and run it again by only connecting to your Dev database,  alternatively you can restore as a different database and at a later stage you can rename this after dropping the previous database
0
Angel02Author Commented:
Here is the new script. I just typed in E:\Company SQL\DataFiles\MyDevDatabase_Data.MDF under 'Restore database files as'
as I didn't think i would have any files to browse.

RESTORE DATABASE [MyDevDatabase] FROM  DISK = N'E:\Company SQL\Backups\MyLiveDatabase backup.BAK'
WITH  FILE = 1,  MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Data.MDF',
 MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Log.LDF',  NOUNLOAD,  STATS = 10
GO
0
Angel02Author Commented:
@Aneesh
I am working on the production server. It has a Live database. I am trying to create a development database on the same Production Server. I have named it differently as "MyDevDatabase". I am now trying to restore it from the Live Database so it has the data from the Live database.

Can you please clarify what you are suggesting?
0
nemws1Database AdministratorCommented:
This looks correct now.  Try running this.  It will *not* overwrite an existing database (we don't want that).

RESTORE DATABASE [MyDevDatabase] FROM  DISK = N'E:\Company SQL\Backups\MyLiveDatabase backup.BAK'
WITH  FILE = 1,
  MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Data.MDF',
  MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Log.LDF',
  NOUNLOAD,  STATS = 10
GO 

Open in new window

0
Angel02Author Commented:
OK. Thanks!

What does this mean?
"Select the backup sets to restore"

How will the new development database be affected by the backup set I select or vice- versa?
0
nemws1Database AdministratorCommented:
You can store multiple backups in a single file.  You can also have a FULL backup as well as progressional Transaction Log backups.  You can also split a single backup into multiple small files. That dialog allows you to select exactly what it is you want to restore.

If you have a single FULL backup in one file, there's not much to select. :)
0
Angel02Author Commented:
So this "Select the backup sets to restore" is a one time thing and the Development database will not be affected by that backup when it is updated in future, correct?
More importantly I hope the Development database does not create a backup or replace this Daily backup of the Live database. Can you please confirm?
0
nemws1Database AdministratorCommented:
1) Yes.

2) I want to say 'yes' but I don't know how you are doing your backups.  If you're using Ola Hallengren or doing something that is logical, then 'yes'.  If you're doing something really stupid, then no, you can blow away your daily live database backup, but that has *nothing* to do with what we've done here today or the 'RESTORE' command I confirmed earlier.
0
Angel02Author Commented:
I tried again with MOVE statements

RESTORE DATABASE [MyDevDatabase] FROM  DISK = N'E:\Company SQL\Backups\MyLiveDatabase backup.BAK'
WITH  FILE = 1,
  MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Data.MDF',
  MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabase_Log.LDF',
  NOUNLOAD,  STATS = 10
GO

But it is still throwing the error

"The database to be restored was named MyLiveDatabase. Reissue the statement by using the WITH REPLACE option..."

Did you mean to say I can check the Overwrite option now since the MOVE statements are added?
0
nemws1Database AdministratorCommented:
Nope.  Pretty much never select the Overwrite option (unless you know what you're doing).

I just ran the same command above and it worked perfectly for me.  Can you give me a screenshot?  I want to see if its reporting the DB error codes.
0
Angel02Author Commented:
Please see attached.
EE-Screen1.jpg
EE-screen2.jpg
0
nemws1Database AdministratorCommented:
You don't already have a existing database named 'MyDevDatabase' (even blank), do you?
0
Angel02Author Commented:
I had created a blank database 'MyDevDatabase'. I can delete it if needed and start from scratch in a different way.
0
nemws1Database AdministratorCommented:
Delete it and then run the restore command we've been working on.  It should go then.

We *could* have specified the "Overwrite", but I rarely do that.  I'd rather delete the empty Dev database and then do the restore.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
For dbs with large log files, overwrite ("WITH REPLACE") is very useful.  Also for dbs with large data files if IFI is not enabled (but it should be unless you have serious security reasons for not enabling it).

I strongly urge you to always use RESTORE statements for this, not the gui.  The command is a verified, documented and repeatable/consistent way to do restores.  Besides that, the gui is still flaky: it will hang sometimes and do nothing, other times the command will actually complete but the interface hangs, making you think the command isn't done yet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.