Restore from an existing database

Angel02
Angel02 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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.

Author

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
AneeshDatabase Consultant
Top Expert 2009

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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.
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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.
AneeshDatabase Consultant
Top Expert 2009

Commented:
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

Author

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

Author

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?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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

Author

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?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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. :)

Author

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?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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.

Author

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?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
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.

Author

Commented:
Please see attached.
EE-Screen1.jpg
EE-screen2.jpg
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
You don't already have a existing database named 'MyDevDatabase' (even blank), do you?

Author

Commented:
I had created a blank database 'MyDevDatabase'. I can delete it if needed and start from scratch in a different way.
IT Supervisor
Top Expert 2009
Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial