October 4, 2011 at 7:57 am
Looking for help in restoring a production database to a different name in order to restore a wiped out table.
Dont have this options in the gui to perform this operation. I was trying to use the move options but I am getting error that database is in use.
In the gui, I see the full backup options along with the tlog from a different time but the the options are to restore it in the same db which is a no no. Can someone please help
October 4, 2011 at 8:02 am
rjoseph (10/4/2011)
Looking for help in restoring a production database to a different name in order to restore a wiped out table.Dont have this options in the gui to perform this operation. I was trying to use the move options but I am getting error that database is in use.
In the gui, I see the full backup options along with the tlog from a different time but the the options are to restore it in the same db which is a no no. Can someone please help
You can indeed do this through either the gui or with TSQL
GUI:
Right click "Databases" then select "Restore Database"
In the "to database" block, type in a name... you dont have to select from the dropdown.
proceed as you were...
TSQL:
Restore database examples here:
http://msdn.microsoft.com/en-us/library/ms186858.aspx
this example wouild work nicely:
BACKUP DATABASE orginaldbname
TO disk='d:\backupfolder\origbackup.bak';
--run the following to get the logical file names you need in the next step.
RESTORE FILELISTONLY
FROM disk='d:\backupfolder\origbackup.bak';
RESTORE DATABASE newdb
FROM disk='d:\backupfolder\origbackup.bak'
WITH MOVE 'logicalfilenamedatafile' TO e:\MySQLServerdatafilesewfilenamedb.mdf',
MOVE 'logicallogfile' TO 'f:\MySQLServerlogfilesewlogfiledb.ldf';
GO
October 4, 2011 at 9:01 am
Thanks very much. I was able to restore that database to a new name by using the "move" feature. I have tried the gui but it did not give me the options to create a new logical file name for the db and the log.
I use the script and it is beautiful. Once again, thanks a million for your qucik response.
October 4, 2011 at 9:22 am
rjoseph (10/4/2011)
Thanks very much. I was able to restore that database to a new name by using the "move" feature. I have tried the gui but it did not give me the options to create a new logical file name for the db and the log.I use the script and it is beautiful. Once again, thanks a million for your qucik response.
No problem. With the GUI, the logical file names will remain the same. Any time you are doing a restore, the logical file names are going to remain the same. It's the physical file names you are changing.
So in the Gui, after you type in the name of the "to database", select the "from device" and click the appropriate checkbox in the "selet the backup sets to restore", on in the upper lefthand corner, you'll see the "select a page" window... in there, go to the "options"
From here, you can see the "orginal file name" and "Restore as" path and file name.
Also, aove you can see the the "Script" button with a dropdown. If you select that, you can generate the T-SQL script for the action you were going to perform. This is a good way to learn the how the GUI translates into T-SQL statments. also, Books Online should really be the first step, before even this forum.
October 4, 2011 at 9:58 am
Wonderful. I did go to book online and maybe I did no input the right information in order for me to get what you describe there for me. I am not an experienced user of SQL. I just started and I think it is better for me if I interact with an experienced guru then I can get better info than with a static knowledge base. In all, I really appreciate your help and this forum is second to now when it comes to help others.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply