Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

When is an MS sql database restore really done?

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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