Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

When is an MS sql database restore really done?

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

877 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