Solved

When is an MS sql database restore really done?

Posted on 2009-04-08
3
207 Views
Last Modified: 2012-05-06
I'm doing a restore to a different database using a backup of a live database so I can treat sensitive fields to hide SSN's before backing up the cloned database for sending to a vendor to sort out a problem. My code will do all the steps but I can't find a way to get a report from Ms SQL as to when the database has finished restoring and I can start masking sensitive fields without checking SQL 2005 Management Studio. Right now, I just use a messagebox to stop the code and watch the treeview in Management Studio. As soon as it reports there that the restore is complete I can then complete the rest of the program. Below is how I'm construction my restore statement.
strSQL$ = "RESTORE DATABASE [IBCT-MASKED] FROM DISK = N'C:\TNETBACK\ISB' WITH FILE = 1,"
              strSQL$ = strSQL$ + " MOVE N'TPNEW_DAT' TO N'C:\TNETBACK\IBCT-MASKED.MDF',"
              strSQL$ = strSQL$ + " MOVE N'TPNEW_LOG' TO N'C:\TNETBACK\IBCT-MASKED.LDF'," 
              strSQL$ = strSQL$ + " NOUNLOAD, STATS = 10"

Open in new window

0
Comment
Question by:bob_mechler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24102607
That statement is restoring everything in one step so when it returns/completes the db is done.  If you were loading multiple transaction logs in multiple statements with the WITH NORECOVERY option then it would be after the last file which you would leave off the NORECOVERY option.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 24103185
Your statement is looking good. You can refere this link also.
http://www.wisesoft.co.uk/articles/tsql_backup_restore_progress.aspx
 
0
 

Author Closing Comment

by:bob_mechler
ID: 31568362
The script is beyond my skill level but did tell me that the operation should be done by a DBA. I was thinking I could use low level ODBC calls to return the mode column in dbo.sysdatabases of the master db. I had noticed it was non-zero while the restore operation was running and went to zero when the restore was done. I can see that the restore operation just needs to be done by the user. From there my program should mask the cloned db fields and then do the backup, zip and ftp. Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question