Create new database using .bak file using T-SQL

Question

I have a scenario where i need to create a new database with some predefined tables. These structures will be available in .bak file. I need to create new database using this .bak file using T-SQL query.

I have tried creating new database using create database query.

    CREATE DATABASE newDataBase

when i tried to restore this .bak file to newly created database, it is throwing error like this backup is of different db type and not able to restore this db.

    RESTORE DATABASE newDatabase
    FROM DISK = 'c:\SkeletonDataBase\Skeleton.bak';

The error occured is

Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'newDatabase' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I want to restore database to new database without affecting source database and it files (mdf and ldf). Please help me.

I need some solution on how i can create new database using .bak file using T-SQL. Any solutions are highly appreciable.

What is the exception thrown, exactly?

David Brabant
– David Brabant

2014-04-07 08:29:22 +00:00
Commented Apr 7, 2014 at 8:29 — David Brabant
– David Brabant, Commented Apr 7, 2014 at 8:29
I have edited question, it includes error

Chethan
– Chethan

2014-04-07 08:31:18 +00:00
Commented Apr 7, 2014 at 8:31 — Chethan
– Chethan, Commented Apr 7, 2014 at 8:31

David Brabant · Accepted Answer · 2014-04-07 08:40:08Z

3

Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. Use the "with replace" option in your restore. Also see the move option if your file locations differ. More info here.

answered Apr 7, 2014 at 8:40

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chethan Over a year ago

This seems promising solution initially, but caused some error. The file 'E:\SourceDB.mdf' cannot be overwritten. It is being used by database 'SourceDB'. Msg 3156, Level 16, State 4, Line 1 File 'xyz' cannot be restored to 'E:\SourceDB.mdf'. Use WITH MOVE to identify a valid location for the file. The file 'E:\SourceDB.ldf' cannot be overwritten. It is being used by database 'SourceDB'. File 'xyz_log' cannot be restored to 'E:\SourceDB.ldf'. Use WITH MOVE to identify a valid location for the file.

David Brabant Over a year ago

The database on which you do the restore (newDatabase) has to be taken offline. You can use a statement like this one: ALTER DATABASE [newDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE. Once the restore is done, you have to put the database back online, of course.

Collectives™ on Stack Overflow

Create new database using .bak file using T-SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related