Create new Datatbase from bak file

Question

Create new Datatbase from bak file

Al.Pacca

Valued Member

Points: 53
More actions
July 15, 2015 at 2:23 am

#319784

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 Studio11.0.2100.60
Microsoft Analysis Services Client Tools11.0.2218.0
Microsoft Data Access Components (MDAC)6.1.7601.17514
Microsoft MSXML3.0 4.0 6.0
Microsoft Internet Explorer9.11.9600.17843
Microsoft .NET Framework4.0.30319.34209
Operating System6.1.7601

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

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

Al.Pacca Valued Member Points: 53 More actions · Answer 3

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?

Michael L John One Orange Chip Points: 27056 More actions · Answer 4

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/

Al.Pacca Valued Member Points: 53 More actions · Answer 5

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?

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 6

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

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

Al.Pacca Valued Member Points: 53 More actions · Answer 8

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.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

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.