12

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mujah Maskey
  • 8,654
  • 8
  • 40
  • 61

3 Answers3

20

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.

Matthew Lock
  • 13,144
  • 12
  • 92
  • 130
jlnorsworthy
  • 3,914
  • 28
  • 34
  • 2
    I had to manually set different file names in the options page. – George Aug 25 '15 at 08:29
  • If I not using bak file, I can use db name in source db......... but i am unable to see all the db's in source db.can you please help me why I cant do it – koushik veldanda Sep 21 '15 at 07:34
  • If a DB already exists with the same name or has files with the same filenames, rename the DB and the files. Yes - even though you give it a new name and new filenames it will still fail (stuck in "Restoring..." state). Very annoying. – Nick Westgate May 31 '17 at 23:36
6

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

Anil Soman
  • 2,443
  • 7
  • 40
  • 64
6

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.
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • If I not using bak file, I can use db name in source db......... but i am unable to see all the db's in source db.can you please help me why I cant do it – koushik veldanda Sep 21 '15 at 07:34
  • Simply put, you don't have permissions in the other databases. So either talk with someone who has the sysadmin role to help you, get sysadmin yourself, or exercise one of the other solutions in this thread (namely renaming the current db and restoring to the old name). I wouldn't be surprised if your lack of current permissions harms the latter effort, though. – Ben Thul Sep 21 '15 at 16:02