Solved

When is an MS sql database restore really done?

Posted on 2009-04-08
3
204 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 26

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 40

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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