We help IT Professionals succeed at work.

restoring database with sql 2008

jamesmetcalf74 asked
sql 2008 standard

restoring database but logic is not working as I expected.

production database name =   premisys
want to restore a backup of premisys to about  2 days ago to a new database named premisys1 without effecting premisys in prod

in the "to database",(screenshot 1) would I put the name premisys1 (this is a database that does not exist)  screenshot1.jpg

next options....
can I bullet from database and select option and select the restore point that is bulleted?  screenshot1.jpg

will this create a database named premisys1 from a restore point at around 2 am on 3/24 without effecting the in prod premisys database? screenshot 2 referenced.
Watch Question

Distinguished Expert 2017
make sure to click on options and make sure to identify where the db and log files and their names are not the same.
you must use the full backup (file) without bringing the db online,  then use the transaction log back up to restore it to a time with the last bringing the new db online.
Mohammed KhawajaManager - Infrastructure:  Information Technology

Ensure to click the Options button and rename the files also.


thanks mohammed

in the source for restore....

from database is ok to use?
from device... for some reason it won't let me select a network location....
is this normal?

I guess I don't understand the concept of backup sets versus a backup file....

from database versus device  is obviously using some logic to grab the correct backup files which are located on a network drive.

so it seems I need to I can use the from database option instead of from device... and restore a new database from the production database without any effect?
Mohammed KhawajaManager - Infrastructure:  Information Technology

From a network location is not supported as SQL server can see local drives only.  Your option would be to copy the BAK file to a local drive and restore it from the local drive.
IT Engineer
Distinguished Expert 2017
From a network location is not supported as SQL server can see local drives only.
Not really a SQL Server issue but SSMS. I don't like to use the GUI for these kind of tasks. It will be more clear with T-SQL. The command should be something like:
FROM DISK='C:\MSSQL\BACKUPS\Premisys_backup_full.bak'
   MOVE 'premisys_Data' TO 'C:\MSSQL\Data\premisys1_Data.mdf', 
   MOVE 'premisys_Log' TO 'C:\MSSQL\Log\premisys1_Log.ldf'

Open in new window

NOTE: I don't know the location of the backup and the real data and tlog logical and physical filenames so you need to find those information and update the command above with the real information. If you also don't know you can easy find it by running the following command:
FROM DISK='C:\MSSQL\BACKUPS\Premisys_backup_full.bak'--> replace this with the correct path and filename

Open in new window