Search code examples
sql-server-2008backuprestore

How to restore to a different database in SQL Server?

I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database - Database2.

I have seen this question: Restore SQL Server database in same pc with different name and the recommended step is to rename the original db, but I am out of that option as I am in the production server and I cant really do it.

Is there any other way of restoring it to Database2, or atleast, how do I browse through the data of that .bak file?

thanks.

ps: the second answer from the above link looked promising but it keeps terminating with error:

Restore Filelist is terminating abnormally

Solution

  • You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

    View the contents of the backup file:

    RESTORE FILELISTONLY FROM DISK='c:\your.bak'

    note the logical names of the .mdf & .ldf from the results, then:

    RESTORE DATABASE MyTempCopy FROM DISK='c:\your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

    This will create the database MyTempCopy with the contents of your.bak.

    (Don't create the MyTempCopy, it's created during the restore)

    Example (restores a backup of a db called 'creditline' to 'MyTempCopy'):

    RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).