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
  • 162
  • 1
  • 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,928
  • 22
  • 177
  • 323
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,193
  • 12
  • 21