October 27, 2011 at 8:44 am
We use third party software written in VB that uses an SQL2008 database. There are custom fields that can be created for the services. These custom fields were updated recently. One of the existing fields lost all of its data.
Question: Is it possible to restore that services table from an backup of the SQL database from September to a new-named database or table name so as to not overwrite the existing services table. The existing services table has been updated during October (new services). I just need to look at the changed/updated/(messed up) field to see if I can get the missing data.
Thanx.
October 27, 2011 at 8:49 am
Yes you can restore to a new name (and hd location obviously).
If the db is in full mode you can even take a log back and restore to a point in time just minutes or hours before the issue.
October 27, 2011 at 8:49 am
You can restore the backup as a different database instead of overwrite the real database. Then you can use the old version with update/insert statement. I would also try and figgure out how I 'Lost" the value of a column and try to prevent it from happening again.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 27, 2011 at 8:54 am
The manager of that data decided that the field wasn't needed. Then after we deleted the field, it was decided that "well...actually-hold onto it" two weeks after the fact. I am doing a favor by trying to get it back.
I assume I use the "restore" transact statement. Can you shed light on where I make sure it is a separate location, etc.
The original database name is bcdata.
The table name is "services"
Thanx
October 27, 2011 at 9:07 am
Use the GUI.
Right click restore on the prod db.
Then change the name of the db, then file location.
Then put the point in time to stop at a couple hours before the drop (as close as you can). If you over shoot you'll have to start that over.
The default trace might hold that date, but 2 weeks ago is a long time with that trace!
If you still feel uneasy, post the script here so we can check it out for you before hitting go.
October 27, 2011 at 9:07 am
can you send the results of the command sp_helpfile issued under the original db and we can build a script to do this for you
October 27, 2011 at 9:50 am
Now that I have fully wasted your time...I appologize. I went back into the database (after thinking because I usually don't just delete items)..I had renamed the field with a leading "z" to drop it down to the bottom of the active list, and deactivated it. All the data was there, just not seen. I renamed it, then reactivated it under the services table (thru the VB interface).
I looked at the restore command...it will let you restore a database to a different name, etc, but I have an uncomfortable feeling that it restores to the backed-up database name, and then renames it. (I am learning so don't yell at me too much)
My immediate problem is removed, but I am still learning SQL so I hope I can keep asking.
Thank you,
Jeff
October 27, 2011 at 9:54 am
Then take a test db and test server and test.
No it doesn't overwrite and then rename. It just builds an entirely new version of the db at the time of the end of the backup.
October 27, 2011 at 9:55 am
if you still post the results of sp_helpfile, i will build you a script which restores to a different database and location, that way you can play about with it to your hearts content to simulate restores etc
October 27, 2011 at 11:16 am
sp_helpfile
bc_Data1c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bc_Data.mdfPRIMARY1681856 KBUnlimited10%data only
bc_Log2c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bc_Data.ldfNULL3840 KBUnlimited10%log only
October 27, 2011 at 11:32 am
GilaMonster (10/27/2011)
mike.jean17 (10/27/2011)
yap u r right and i am 100% agree with u ......Ignore the spam, it's reported.
At least SOME people try to enter the conversation.
(Am I really complaining about the quality of spam now :w00t:)?
October 28, 2011 at 2:16 am
You would want to do something along the lines of this
--CREATE A NEW BLANK DATABASE
CREATE DATABASE [bcdata_restored] ON PRIMARY
( NAME = N'bcdata_restored', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bcdata_restored.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'bcdata_restored_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bcdata_restored_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--RESTORE A COPY OF THE ORIGINAL DATABASE INTO THE NEW BLANK DATABASE
RESTORE DATABASE bcdata_restored FROM DISK = 'PATH OF YOU BAK FILE GOES HERE' WITH REPLACE,
MOVE 'bc_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bcdata_restored.mdf',
MOVE 'bc_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\bcdata_restored_log.ldf'
--IF YOU HAVE DIFF BAK OR TRN FILES TO RESTORE UNCOMMENT THE BELOW LINE
--WITH NORECOVERY
GO
--IF YOU HAVE DIFF BAK FILES RUN THIS THIS
RESTORE DATABASE bcdata_restored FROM DISK = 'PATH OF YOUR DIFF BAK FILE GOES HERE'
--IF YOU HAVE TRN FILES TO RESTORE UNCOMMENT THE BELOW LINE
--WITH NORECOVERY
GO
--IF YOU HAVE TRN FILES TO RESTORE REPEAT THIS FOR EACH TRN FILE BUT NOT THE VERY LAST TRN FILE
RESTORE LOG bcdata_restored FROM DISK = 'PATH TO TRN FILE GOES HERE' WITH NORECOVERY
GO
--FOR THE LAST TRN FILE RUN THIS
RESTORE LOG bcdata_restored FROM DISK = 'PATH OF THE LAST TRN FILE GOES HERE' WITH RECOVERY
GO
Depending on how you do your backups depends which of the above lines of code you need.
If you only ever do FULL backups then you just need the CREATE DATABASE and the first RESTORE DATABASE
If you do FULL and DIFF backups then you need the second RESTORE DATABASE as well
FULL DIFF and TX backups you need the full script
This link should help you get the grips with the basics
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply