How to restore to a different database in SQL Server?

by

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).

More Related Contents:

  1. Cannot open backup device. Operating System error 5
  2. Restoring MySQL database from physical files
  3. Can I restore a single table from a full mysql mysqldump file?
  4. varchar(max) everywhere?
  5. T-SQL: Export to new Excel file
  6. Enable ‘xp_cmdshell’ SQL Server
  7. Restore a postgres backup file using the command line?
  8. How to take backup of a single table in a MySQL database?
  9. How do I backup and restore the system clipboard in C#?
  10. Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation
  11. SQL MERGE statement to update data
  12. How to get the last record per group in SQL
  13. A transport-level error has occurred when receiving results from the server [closed]
  14. storing passwords in SQL Server
  15. How does sql server sort your data?
  16. SQL Server: Database stuck in “Restoring” state
  17. Why is SQL Server 2008 Management Studio Intellisense not working?
  18. Reporting Services permissions on SQL Server R2 SSRS
  19. T-SQL substring – separating first and last name
  20. How to implement polymorphic associations in an existing database
  21. How to deploy SQL Server Compact Edition 4.0?
  22. Optional parameters in SQL Server stored procedure
  23. Entity Framework Database.SetInitializer simply not working
  24. What is the default transaction isolation level for SQL Server with ADO.NET?
  25. T-SQL strip all non-alpha and non-numeric characters
  26. Avoiding IF ELSE due to variable been NULL
  27. Where are SQL Server connection attempts logged?
  28. SQL Server 2008 Connection Error “No process is on the other end of the pipe”
  29. SSIS LookUp is not dealing with NULLs like the docs say it should
  30. Speeding up mysql dumps and imports [closed]

Leave a Comment