How to restore a SQL Server backup to another database?

Question

I want to restore a database to 12/29/2016. I want to restore to another name so I do not lose the state of the original database. The name of my database is APDatabase.

How do I modify the SQL below to restore to a database called TempAPDatabase? I also want to be careful not to lose any of the backup information which is stored on the disk as 87A991B1-9305-45C1-A461-9B1A3174A707.

USE [master]

BACKUP LOG [APDatabase] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MYSQLSERVER\MSSQL\Backup\APDatabase_LogBackup_2017-03-13_10-52-54.bak' 
WITH NOFORMAT, NOINIT,  
     NAME = N'APDatabase_LogBackup_2017-03-13_10-52-54', 
     NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY, STATS = 5

RESTORE DATABASE [APDatabase] 
FROM DISK = N'{87A991B1-9305-45C1-A461-9B1A3174A707}10' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [APDatabase] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MYSQLSERVER\MSSQL\Backup\APDatabase_LogBackup_2017-03-10_06-47-10.bak' 
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2016-12-29T00:00:00'
GO

Take a look at here to restore a backup of a database to a new database msdn.microsoft.com/en-us/library/ms186390(v=sql.105).aspx — Jason Clark
– Jason Clark, Commented Mar 14, 2017 at 10:03

Wes H · Accepted Answer · 2017-03-13 17:28:55Z

1

Assuming the rest of your command is correct, you just need to state the new name as the database you're restoring.

RESTORE DATABASE [TempAPDatabase] 
FROM DISK = N'{87A991B1-9305-45C1-A461-9B1A3174A707}10' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [TempAPDatabase] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MYSQLSERVER\MSSQL\Backup\APDatabase_LogBackup_2017-03-10_06-47-10.bak' 
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2016-12-29T00:00:00'
GO

answered Mar 13, 2017 at 17:28

Wes H

4,4992 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bill Greer Over a year ago

Is the BACKUP LOG (the first 5 lines) part of my SQL necessary?

Wes H Over a year ago

Simple question, long winded answer. At somepoint in the past you'll need to run the Backup command, but the BACKUP LOG portion is not actually required to run restore. As long as you already have the BACKUPs created, you can restore them. However, it is a good idea to backup the Log just before the restore so you get the most update to date data in your restore.

Bill Greer Over a year ago

Okay. Thank you for your help. One last thing. What's up with the GUID instead of an actual file name?

Wes H Over a year ago

Don't know, I just copied your statement. I haven't used a GUID in a Restore command previously.

Wes H Over a year ago

If you are copying the database to another location, make sure you verify the options. A backup for a copy should be treated differently than a backup for recovery.

Collectives™ on Stack Overflow

How to restore a SQL Server backup to another database?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related