?
Solved

When is an MS sql database restore really done?

Posted on 2009-04-08
3
Medium Priority
?
215 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
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

593 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