July 15, 2015 at 2:23 am
Hello,
i am new in server central and this my first post - i am hopefull this is the right place to post my question.
I need to create a new db from a backup file of an allready existing database (vehicledb) on this server.
So i created a new database with name "vehicledb2"
But when i try this, the backup says that this file is used
The backup set holds a backup of a database other than the existing 'Vehicles2' database.
Restore of database 'Vehicledb' failed.
What is the problem, how can i work around this?
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2218.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.9600.17843
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.1.7601
July 15, 2015 at 2:28 am
Welcome to the forums! You're trying to restore the new database with the same file names as the existing one, which obviously isn't allowed. Use the WITH MOVE clause in the RESTORE statement to choose new names and/or locations for the files.
John
July 15, 2015 at 4:26 am
if you go to the files tab on the restore GUI you will be able to rename the file there under the restore as heading
July 15, 2015 at 6:22 am
Hello, thanks for the quick reply,
Is it the file tab only which need to edit - okay,
Is it okay if i safe the 2 files with a new (different) name in the same folder?
July 15, 2015 at 6:32 am
Al.Pacca (7/15/2015)
Hello, thanks for the quick reply,Is it the file tab only which need to edit - okay,
Is it okay if i safe the 2 files with a new (different) name in the same folder?
Yes to both questions.
See if this script helps. You will need to modify it for your needs.
USE [master]
RESTORE DATABASE [Junk2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Junk.bak'
WITH FILE = 1,
MOVE N'Junk' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Junk2.mdf',
MOVE N'Junk_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Junk2_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
When you create a database, you have a logical file name, and the physical file name.
SELECT * FROM sys.master_files
In your case, you are trying to restore the file as the same name as an existing file. Renaming the physical file will work.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 15, 2015 at 8:11 am
This sounds good,
thank you for the good support
But i dont understand it completely and have more questions:
I have the old database vehicles_old and did a backup, with name vehicles_old.bak
Then created the new Database vehicles_new
you say:
USE MASTER - i dont understand why to use master DB, i think i need to use my new db Vehicles_new , am i wrong?
then say:
RESTORE DATABASE vehicles_old FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Junk.bak'
RESTORE DATABASE vehicles_old here? FROM DISK = N'What\patheed\here?\Junk.bak'
can you tell me where to edit what here?
Sorry but i dont want to shoot anything?
July 15, 2015 at 8:28 am
Hey,
Try this:
USE [master]
GO
RESTORE DATABASE [Vehicles_new] FROM DISK = N'C:\backup\vehicles_old.bak'
WITH FILE = 1
, MOVE N'Vehicles_old' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Vehicles_new.mdf'
, MOVE N'Vehicles_old_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Vehicles_new_log.ldf'
, NOUNLOAD, STATS = 5
GO
You use master, because you cant be connected to the database you want to restore, as exclusive access wont be given.
p.s. Change your file locations (C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA) to wherever the database needs to be.
July 15, 2015 at 8:43 am
Al.Pacca (7/15/2015)
Then created the new Database vehicles_new
Yes, although you don't actually need to create the database first - you can just run the RESTORE if the database you're restoring doesn't exist. In fact, if you do create it first, you need to use the WITH REPLACE option in order to overwrite it.
John
July 15, 2015 at 3:39 pm
It worked great 🙂
But one thing i did not understand, the MOVE command.
MOVE N'cars' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\DATA\webcarsfolder\webcars.mdf'
From where came the files ('cars') we MOVE'd to the new location with a new name - where they into the bak file which we have recovered?
Thank you for the good support guys, you helped me a lot.
July 15, 2015 at 4:05 pm
The backup file contains the paths of the original database and its files. It will restore to those paths and filenames by default. However, if you have files already in that path with the same name, the restore fails, unless you overwrite the existing database.
In your case, you probably want the new database with new file names, so you "move" the old files to new ones. You can move using the logical name of the file.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply