0

I have created a backup of database A1234 as file A1234.bak.

I want to restore this A1234.bak in 2 different databases say D1 and D2 in same sql server instance.

I want same data in both the databases.

I've tried 

RESTORE DATABASE D1 
FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\A1234.bak'

This works for D1. When I try for D2 It gives error that A1234 is already created and cannot be overwritten.

when I see in C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ location, there is database A1234.mdf and A1234_log.ldf files and no D1.
while in SSMS it shows D1 and no A1234.
So I guess the database A1234 is just pointed to D1.

How can I just restore the schema and data in any database I want using .bak file. I've seen some WITH MOVE commands but can't understand it

Edit : Also Tried like this

RESTORE DATABASE D1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\A1234.bak'
WITH MOVE 'A1234' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\D1.mdf',
MOVE 'A1234_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\D1_log.ldf',
GO
Rita Shroff
  • 155
  • 2
  • 15
  • See here: https://www.mssqltips.com/sqlservertutorial/122/retore-sql-server-database-to-different-file-name-and-location/ – Peter B May 21 '19 at 07:34
  • 1
    Possible duplicate of [How to restore to a different database in sql server?](https://stackoverflow.com/questions/6267273/how-to-restore-to-a-different-database-in-sql-server) – Matt May 21 '19 at 07:43
  • @Matt So I need to do like write its logical name and move its both logical names to my desired name. Okay – Rita Shroff May 21 '19 at 07:49
  • @PeterB got it. – Rita Shroff May 21 '19 at 07:49
  • @Matt Okay I did the restore process but now the new database D1 is not accessible, in SSMS -> Databases section, it shows D1 (Restoring...). Database is not that big. hardly 20 MB. And it restores fast manually from ssms. Seems to be stuck on some process. – Rita Shroff May 21 '19 at 07:52
  • 1
    Are you sure `D1` and `D1_log` are your logical names on that database? Try `A1234` and `A1234_log` – Matt May 21 '19 at 07:57
  • @Matt My mistake writing logical names here. Its A1234 and A1234_log respectively. But why it shows restoring in SSMS. I had given enough time – Rita Shroff May 21 '19 at 11:09
  • @Matt Okay got it. I also have to use `WITH REPLACE,RECOVERY` – Rita Shroff May 21 '19 at 11:13
  • @Matt I Still have a problem shrinking the new D1_log. Do I every time need to mention the logical name of the D1 database (which is A1234) like this `DBCC SHRINKFILE ('A1234_log' , 0, TRUNCATEONLY)` ? – Rita Shroff May 21 '19 at 11:34

0 Answers0