0

Say I have a database Live on some instance. I have created a backup of it and now I need to restore it into a new database named Dev. In SSMS I am choosing a device Live.bak, then in destination I am changing the name to Dev and in Files tab I see that database and log files are also renamed Dev.mdf and Dev_log.ldf. When I proceed the Live database changes its status to Restoring.

Now I tried to uncheck tail log option and it restored normally. Is it safe way to restore the database? I mean I don't want any changes to Live. Why it tries to restore Live database. I am confused.

Giorgi Nakeuri
  • 172
  • 1
  • 11
  • If you could supply screen shots, this might help in determining where your issue is. If you do it correctly then the status of the Live database should not change to Restoring, because then you are restoring the Live database. – John K. N. Mar 16 '18 at 09:28
  • 1
    I suggest writing a RESTORE DATABASE Dev... script instead of pointing and clicking in the GUI. – Aaron Bertrand Mar 16 '18 at 11:30
  • Yeah, if the Live database changes status to Restoring, then you've done something wrong, you probably don't have the destination set properly. In this scenario, you should instead see a new database named Dev appear with status Restoring. – BradC Mar 16 '18 at 13:42
  • @AaronBertrand, can you please explain why? I asked local admin about this case and he said, that this happens because of a tail log. So the live database is changing its status because Dev database needs a data from Live that is not in the backup. I mean if I made a bak at 10 am and restoring it to Dev at 12am, live db is locked so this 2 hour data is also added to Dev. – Giorgi Nakeuri Mar 18 '18 at 08:39
  • My reason is because you can’t always be sure what the GUI is doing for you. – Aaron Bertrand Mar 18 '18 at 14:11

2 Answers2

2

Instead of using the SSMS GUI interface to restore databases, you should create a T-SQL script for the restore statement.

This serves two purposes:

  1. You can confirm the target database name, the location of the restored database files, etc prior to the start of the restore.
  2. You can re-run the restore statement as needed in future without re-using the GUI. This ensures you do the restore the same way each time.

You can create a script from the "Restore Database" dialog-box as show in this screenshot:

enter image description here

This will generate a restore statement like the one below, which I've wrapped for readability:

USE [master]
RESTORE DATABASE [test_master_restore] 
FROM  DISK = N'D:\SQLServer\Backups\master_db.bak' WITH  FILE = 1
    ,  MOVE N'master' TO N'D:\SQLServer\MV\MSSQL10_50.MV\MSSQL\DATA\test_master_restore.mdf'
    ,  NOUNLOAD
    ,  STATS = 5
Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
1

It may be safer to restore the database via script, just to be sure you know what is -- and is not -- happening to your databases.

RESTORE DATABASE [Dev] 
FROM DISK = 'Path\to\Full-Backup\Live-Full-Backup.bak' 
WITH Recovery, --make this NORecovery if you want to apply .trn files after
STATS=10 
, MOVE '<DataFileLogicalName>' TO 'Path\Dev_01.mdf',  
  MOVE '<LogFileLogicalName>' TO 'Path\Log_01.ldf' ;

you'll need to fix the path statements and the logical names.

CaM
  • 2,183
  • 11
  • 21