CODEWITHSUNDEEP
sql-server
Mujah Maskey
Mujah Maskey

Reputation: 8804

Restore SQL Server database in same pc with different name

I have:

SQL Server 2008

Database Name: database1

I had taken backup from "database1" nearly one month ago. Now, by someway, "database1" is messed up in some tables.

So I want to recover only those tables from my last backup.

I am trying to make another database named "database2" and restore on it with old backup.

I'm afraid to restore from backup file, which may restore in "database1", I want to re-store in only 'database2' (which is new)

Upvotes: 12

Views: 42944

Answers (3)

jlnorsworthy
jlnorsworthy

Reputation: 3974

If you are using SSMS, you can:

1)right click the Databases folder

2)select Restore Database

3)Enter any name you want in the "To Database" field - this will be the database name

4)Select "From device" and find your backup file.

You may need to specify different physical file names by selecting the Options page (I don't remember if SQL server will automatically prepopulate a different file name if a database already exists with the filenames contained in the backup.

Upvotes: 20

Ben Thul
Ben Thul

Reputation: 32697

Here's where using T-SQL will help you out.

  1. Execute a "restore filelistonly from disk = 'your backup file here'" This will get you a list of the logical files for the database from the backup file along with their physical paths.
  2. Based on the information obtained from above, execute "restore database database2 from disk = 'your backup file here' with move 'logical file 1' to 'physical file name 1', move 'logical file 2' to 'physical file name 2', ...". Make sure that the physical paths that you're providing here are not in use.

Upvotes: 6

Anil Soman
Anil Soman

Reputation: 2467

You can rename your existing database to something like OriginalDB and then create an empty database called database1. Restore your backup in it. Now you can copy data from your Database1 (from bkp) to OriginalDB. After you are done, delete backup and rename original back to Database1

Upvotes: 6

Related Questions