October 11, 2007 at 8:45 am
I have a .bak file and I want to restore it on another server with a different DB name. In 2000 this was possible but they have changed it on 05. Now you get an error saying "the backup set holds a backup of a databse other then the exsisting "test" databse.
If I remeber correctly Microsoft did this to prevent someone from doing a restore using the wrong databse.
Is there a workaround somewheres?
THANKS
October 11, 2007 at 8:48 am
You could try to back-up the database to a file in stead of a device. Create a new database (with required name) on the other server and restore the file in this database. Make sure that you check the path on the target server and use the option 'Overwrite existing database'.
October 11, 2007 at 8:51 am
I think Mark has it right. Do it to a disk file, not a device.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 11, 2007 at 9:04 am
hi guys,
I thought the same thing and tried it... I did from device and then selected a .bak file. and then hit restore. It give me the errror
"must select source" meaning it is forcing to over ride the device selection
does not make since
October 11, 2007 at 9:13 am
let me correct my self
I seleced device.
Then the button that took me to file.
Browse and selected the .bak file.
seems like it should work..
October 11, 2007 at 9:14 am
You need to make a new back-up to a file. Remove the back-up device in the dialog and select file. Create a new file and back-up to it. Then restore this file on the other server. This does the job for me every time. As far as I know you cannot restore from a device as if it was a file.
Cheers,
Mark
October 11, 2007 at 9:45 am
Mark did you try that in 2000 or 2005. I am still not able to make it work using 2005
thanks
October 11, 2007 at 9:58 am
I used this technique in both 2000 and 2005.
On DB.A run:
BACKUP DATABASE [name database] TO DISK = N'[path and filename.bak]' WITH NOINIT, SKIP
Startup Management Studio on DB.B. Create a new database. Select the new database. Open the contextmenu, select ALL TASKS, RESTORE A DATABASE, Select the file that was created on DB.A, make sure the path for both .mdf and .ldf are correct, select 'Overwrite existing database' and click OK.
If this doesn't work please let us know what error message appears.
Cheers,
Mark
October 11, 2007 at 10:36 am
ok I was backinp using the GUI, so now I use your statment and here is my error.
ran this...
BACKUP DATABASE test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test9.bak' WITH NOINIT, SKIP
then did a restore using file and force overwrite...I am not adding the LOG. all I have done is created a database 'test'added a table and 2 rows of data. and trying to do a restore to test2
The backup set holds a backup of a database other than the existing 'test2' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
thanks
October 11, 2007 at 12:07 pm
here is where I am now I have change everything to T-sql
BACKUP DATABASE test TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test9.bak' WITH NOINIT, SKIP
RESTORE FILELISTONLY FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test9.BAK'
RESTORE DATABASE test2
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test9.BAK'
WITH MOVE 'test' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2.mdf',
MOVE 'test_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2_log.mdf'
----
here are the results
--
Processed 184 pages for database 'test', file 'test' on file 2.
Processed 2 pages for database 'test', file 'test_log' on file 2.
BACKUP DATABASE successfully processed 186 pages in 0.318 seconds (4.769 MB/sec).
LogicalName
--
test
test_log
--
Msg 3154, Level 16, State 4, Line 9
The backup set holds a backup of a database other than the existing 'test2' database.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.
October 11, 2007 at 4:38 pm
I think I ran across this myself. Create the new db (db B) making sure you use the tablespace name of db A. Then restore to db B with the new name. That worked for me. Good luck.
October 17, 2007 at 9:23 am
Hi,
You can create a new db, and restore old db in it. but you must confirm, tah in restore procces yo change the path and the filename of physical achives mdf and ldf, with the new names, because error will be raise with this problem.
if you use t-sql,
Restore database newDB from disk='c\:MSSQL\backup\oldDB.bak
with move newDATarchivename to 'c.\MSSQL\DATAewdbname.mdf',
with move newLOGarchivename to 'c.\MSSQL\DATAewdbname.mdf'
Pls try it.
Regards
Francisco Racionero
twitter: @fracionero
October 17, 2007 at 9:37 am
The SQL 2005 instance already has a database named test2. Delete the database and then restore or rename the restored database something other than test2.
The link below will give more detail.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=594642&SiteID=1
October 26, 2007 at 12:32 am
You have missed out with file option
try using the below command
RESTORE DATABASE test2
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test9.BAK'
WITH MOVE 'test' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2.mdf',
MOVE 'test_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test2_log.mdf' ,filen=1
Regards..Vidhya Sagar
SQL-Articles
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply