Angel02
asked on
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?
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?
ASKER
Ok. here is the script
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
GO
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
GO
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
ASKER
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?
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.
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.
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.
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
ASKER
Here is the new script. I just typed in E:\Company SQL\DataFiles\MyDevDatabas
as I didn't think i would have any files to browse.
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
WITH FILE = 1, MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabas
MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabas
GO
as I didn't think i would have any files to browse.
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
WITH FILE = 1, MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabas
MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabas
GO
ASKER
@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?
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?
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 ASKER
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?
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?
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. :)
If you have a single FULL backup in one file, there's not much to select. :)
ASKER
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?
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?
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.
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.
ASKER
I tried again with MOVE statements
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
WITH FILE = 1,
MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabas
MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabas
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?
RESTORE DATABASE [MyDevDatabase] FROM DISK = N'E:\Company SQL\Backups\MyLiveDatabase
WITH FILE = 1,
MOVE N'MyLiveDatabase_Data' TO N'E:\Company SQL\DataFiles\MyDevDatabas
MOVE N'MyLiveDatabase_Log' TO N'E:\Company SQL\DataFiles\MyDevDatabas
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?
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.
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.
ASKER
You don't already have a existing database named 'MyDevDatabase' (even blank), do you?
ASKER
I had created a blank database 'MyDevDatabase'. I can delete it if needed and start from scratch in a different way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.