August 8, 2011 at 3:51 pm
Hello,
What happen if ...
on the same instance, I back up a database named AAA. Then I restore this backup on a new database on the same instance named BBB, but I don´t change the data files ?
The database AAA is being used permanently by an ERP software. Database BBB is used for testing purposes.
Thanks,
Juan
August 8, 2011 at 4:00 pm
the restore will fail. Use the with move cluase to move the physical files to a different location.
---------------------------------------------------------------------
August 8, 2011 at 4:14 pm
But the thing is that we have been doing that for a year....:blink:
The databases restored fine and I found out that we had 3 databases sharing the same data files. I am worried thinking that this could corrupt our ERP data or something ...
So if you said is not possible then what?
Last week I delete the test databases and left only the ERP database. But I was thinking that if those databases share the same container (data file) then I could extract the ERP info to reduce the datafile size... I am sorry if my knowledge is far away from the reality!
Thanks Again
Juan
August 8, 2011 at 4:30 pm
Are you kiddin me? The restore will be successful only if the data files and log files are moved to another location other than the location where the data files and log files are located for the database AAA. I have never heard of one data file shared by 3 separate database...
August 8, 2011 at 5:09 pm
what you describe is impossible.
If you have been using the GUI to do the restore it has renamed the files for you based on the database name.
In TSQL run sp_helpdb or alternatively' select * from master,sys.master_files', either will show you where all the database files are.
---------------------------------------------------------------------
August 8, 2011 at 5:28 pm
There is no way, one data file will be shared by multiple databases.
You can alternately run 'sp_helpdb dbname' to check the current logical and physical file name/[ath of the database.
August 9, 2011 at 12:42 pm
if you use the overwrite option during the restore then it will succeed, but the data will be overwritten. Thats the only way I could see the restore being successful. Since as everyone else has said a restore will fail if using the same existing data files.
Adam Durr
Remote DBA support
www.bluegecko.net
August 9, 2011 at 12:48 pm
Adam Durr (8/9/2011)
if you use the overwrite option during the restore then it will succeed, but the data will be overwritten. Thats the only way I could see the restore being successful. Since as everyone else has said a restore will fail if using the same existing data files.
It has to fail... or it's a hellishly big oversight from the ms team.
It could work if the other instance is offline, not if it is running.
August 9, 2011 at 12:54 pm
The thread originator claims that restoring the db with different name (BBB) using the data files of db (AAA) on same instance. Will the overwrite helps here ? What happens to DB (AAA). The restore will certainly fail 100%
August 9, 2011 at 12:58 pm
Never tried that. What happens when you run the test?
August 9, 2011 at 12:59 pm
My understanding of using the overwrite is the file is replaced with the restored database.
Adam Durr
Remote DBA support
www.bluegecko.net
August 9, 2011 at 1:00 pm
Adam Durr (8/9/2011)
My understanding of using the overwrite is the file is replaced with the restored database.
Yes, but will it do it if the db name is different from the original?
August 9, 2011 at 1:06 pm
Ninja's_RGR'us (8/9/2011)
Adam Durr (8/9/2011)
My understanding of using the overwrite is the file is replaced with the restored database.Yes, but will it do it if the db name is different from the original?
CREATE DATABASE test
GO
BACKUP DATABASE [test] TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Backup\test.bak' WITH INIT
GO
RESTORE DATABASE [test] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Backup\test.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
--works
RESTORE DATABASE [test2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Backup\test.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
--fails
GO
DROP DATABASE test
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply