none
Where is the standby file method/property in SMO.Database RRS feed

  • Question

  • Hello,

    I'm trying to locate the method / property in SMO.Database for StandByFile (undo backup file). I'm able to set it during a restore using SMO.Restore.StandByFile, but I'm currently building a empty DB from the properties of an exisitng DB and this is a property I need to at least read from if not modify directly.

    Hoping for some help.

    EDIT: Adding small part of script for context

    # Restore the database
    function Restore-Database ($srv, $dname, $src, $dst, $stby){
    		try { 			
    			$SMORestore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
    			if ($stby) {
    				Write-Host 'Restoring' ([System.IO.Path]::GetFileName($src)) 'to' $dname 'with standby...'
    				$SMORestore.StandbyFile = $tmpdir + $tmpdbname + '_standby.bak'
    			} else {
    				Write-Host 'Restoring' ([System.IO.Path]::GetFileName($src)) 'to' $dname 'with recovery...'
    			}
    			$SMORestore.Action = "Database"
    			$SMORestore.NoRecovery = $false
    			$SMORestore.ReplaceDatabase = $true
    			$SMORestore.Database = $dname
    			$SMORestore.Devices.AddDevice($src, "File")
    
    			$filearray = $SMORestore.ReadFileList($srv)
    			foreach ($file in $filearray){
    				$newfile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile
    				$newfile.LogicalFileName = $file.LogicalName
    				$newfile.PhysicalFileName = $dst + ([System.IO.Path]::GetFileName($file.PhysicalName))
    				$SMORestore.RelocateFiles.Add($newfile) | Out-Null
    			}
    			
    			$SMORestore.SqlRestore($srv)
    			Write-Host ([System.IO.Path]::GetFileName($src)) 'successfully restored to' $dname
    		} catch {
    			$_.Exception
    			if ($Error.Count -gt 0) { 
    	        	$error[0] | fl -force 
    	    	} 
    		}
    	} #Restore-Database $DSTServer $tmpdbname $bkup $tmpdir $false # - $bkup = .bak not defined | $stby = bool 

    and another piece

    function New-Database ($refdb, $dstsrv, $tname, $tdir) {
    		Write-Host 'Creating database' $tname 'based on settings from' $refdb.name '...'
    		$dstdb = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $dstsrv, $tname 
    		# Loop through the original db's filegroup to grab all the settings for the new filegroup
    		try {
    			foreach ($group in $refdb.filegroups) {
    				$fg = New-Object -TypeName Microsoft.SqlServer.Management.Smo.FileGroup -ArgumentList $dstdb, $group.name
    				$dstdb.FileGroups.Add($fg)
    				if ($group.IsDefault -eq $true) {
    					$fgdefault = $group.Name
    				}
    				# Loop through the data files in the filegroup and grab the settings for the new files
    				foreach ($file in $group.Files) {
    					$datafile = new-object -TypeName Microsoft.SqlServer.Management.Smo.DataFile -ArgumentList $fg, $file.name
    			    	$fg.Files.Add($datafile)
    					$file
    					$datafile.FileName = $tdir + ([System.IO.Path]::GetFileName($file.FileName))
    			    	$datafile.Size = $file.Size
    			    	$datafile.IsPrimaryFile = $file.IsPrimaryFile
    			      	$datafile.GrowthType = $file.GrowthType
    			    	$datafile.Growth = $file.Growth
    			    	$datafile.MaxSize = $file.MaxSize
    				}
    		    }
    			# Loop through the log files in the filegroup and grab the settings for the new files
    			foreach ($file in $refdb.LogFiles) {
    				$logfile = New-Object Microsoft.SqlServer.Management.Smo.LogFile -ArgumentList $dstdb, $file.name
    				$dstdb.LogFiles.Add($logfile)
    				$file
    				$logfile.Name = $file.name
    		    	$logfile.FileName = $tdir + ([System.IO.Path]::GetFileName($file.FileName))
    		    	$logfile.Size = $file.Size
    		      	$logfile.GrowthType = $file.GrowthType
    		    	$logfile.Growth = $file.Growth
    		    	$logfile.MaxSize = $file.MaxSize
    			}
    		} catch {
    			$_.Exception
    		}
    		# Create the database
    		try {
    			$dstdb.Create()
    			Write-Host 'Database' $tname 'created successfully'
    		} catch {
    			if ($Error.Count -gt 0) { 
    		     	$error[0] | fl -force
    		    }
    			break
    		}	
    		# Make sure the right filegroup is the default
    		If ($dstdb.FileGroups[$fgdefault].IsDefault -ne $true){
    			Write-Host 'Setting' $fgdefault 'filegroup as default'
    			$fgdef = $dstdb.FileGroups[$fgdefault]
    			$fgdef.IsDefault = $true
    			$fgdef.Alter()
    			$dstdb.Alter()
    		}
    } #New-Database $srcdb $DSTServer $tmpdbname $tmpdir



    • Edited by GeoffGin Saturday, May 2, 2015 1:58 PM
    Saturday, May 2, 2015 12:18 PM

Answers

  • There is no advantage of having multiple LDF files. You need to modify the script and get latest LDF file. I must put caution again that the method available in the blog is not supported by Microsoft. If it logic changes to store TUF file changes in future, your script might break.

    Invoke-SQLCMD can be used from PowerShell to connect to SQL and run the commands.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Sunday, May 3, 2015 12:43 PM
  • Again you are correct, it does not ask you for the undo.bak file because it is stored as and read from a property. If you've ever noticed, that file disappears  after a restore. Also if you try and restore a DB and that file is not there you will get an error.

    Msg 3013, Level 16, State 1, Line 4
    RESTORE DATABASE is terminating abnormally.
    Msg 3441, Level 17, State 1, Line 4
    During startup of warm standby database 'test' (database ID 5), its standby file ('C:\test\ROLLBACK_UNDO_test.BAK') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

    EDIT:

    And there must be a way to access it, I access virtually every other property using SMO.

    https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx

    • Edited by GeoffGin Saturday, May 2, 2015 1:13 PM
    • Marked as answer by GeoffGin Monday, May 4, 2015 2:53 PM
    Saturday, May 2, 2015 1:11 PM

All replies

  • When you restore a backup, you have three options.

    • With Recovery: Database would be online (read+write) and ready to use. NO further backups need to be applied.
    • With NoRecovery: Database would not be readable for users and further backups need to be applied.
    • With Standby: Database would be readable (not writable) for users and further backups need to be applied

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 12:23 PM
  • Thanks for the quick reply Balmukund.

    I understand my options while restoring, what I am trying to better understand is the programmatic way to retrieve where the database believes the undo file is. I would like to do this using SMO, I know thisproperty exist within the data/log files I'm just not sure where and how to access this value...programatically.

    Saturday, May 2, 2015 12:40 PM
  • That's a new file name which would be created by restore process.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 12:49 PM
  • Yes you are correct, but during/after it is created while restoring a reference (path/file.bak) to it is stored as a property of the database. What I'm trying to access is that property, so I can find the undo file programmatically.
    Saturday, May 2, 2015 12:58 PM
  • I don't think that's possible. but why that us needed? Next restore command (with standby) never asks for old standby filename.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 1:00 PM
  • Again you are correct, it does not ask you for the undo.bak file because it is stored as and read from a property. If you've ever noticed, that file disappears  after a restore. Also if you try and restore a DB and that file is not there you will get an error.

    Msg 3013, Level 16, State 1, Line 4
    RESTORE DATABASE is terminating abnormally.
    Msg 3441, Level 17, State 1, Line 4
    During startup of warm standby database 'test' (database ID 5), its standby file ('C:\test\ROLLBACK_UNDO_test.BAK') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

    EDIT:

    And there must be a way to access it, I access virtually every other property using SMO.

    https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx

    • Edited by GeoffGin Saturday, May 2, 2015 1:13 PM
    • Marked as answer by GeoffGin Monday, May 4, 2015 2:53 PM
    Saturday, May 2, 2015 1:11 PM
  • Internally its stored in LDF file. During next restore, it reads LDF file and figures out the path from there. There is no method exposed to get that.

    I have done experiment and found that I can read the LDF file and can see that file.

    Again, no supported way and its really not needed.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 1:19 PM
  • Yeah, correct. Once file is used (restore is done), it would be deleted and new would be created (if standby is used again)

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 1:20 PM
  • How were you able to see the tuf in your ldf? Using a simple text editor i'm unable to see mine.

    Also how certain are you that it is not possible, do you have experience programming with SMO? Not that I don't believe you but I have spent some time developing a ps script that somewhat hinges on this, so not happy if it was all for not.

    Saturday, May 2, 2015 1:44 PM
  • How were you able to see the tuf in your ldf? Using a simple text editor i'm unable to see mine.

    if you open that file with text editor, you can't use "find" functionality because its not an ASCII text. It took some time for me to locate the text which you are seeing in image which I posted.

    Also how certain are you that it is not possible, do you have experience programming with SMO? Not that I don't believe you but I have spent some time developing a ps script that somewhat hinges on this, so not happy if it was all for not.

    I have NEVER worked with SMO and have no experience. But Since I have spent 10 years of my life working with Microsoft with SQL Server Product support team, I can tell you that it's not stored in any table in database. It's part of LDF and there is no way to get that without parsing the LDF file.

    Here is one of the blog written by my friend, who was working in my team.

    http://blogs.msdn.com/b/batala/archive/2011/07/21/how-to-see-the-standby-file-path-when-we-restore-the-database-in-standby-mode.aspx


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, May 2, 2015 2:01 PM
  • Thank you for the link, I think... not sure but I think that the script is designed for a single mdf, ndf, ldf. In my databases there are multiple of each also is there a way to execute from powershell and not sqlcmd
    Saturday, May 2, 2015 2:56 PM
  • There is no advantage of having multiple LDF files. You need to modify the script and get latest LDF file. I must put caution again that the method available in the blog is not supported by Microsoft. If it logic changes to store TUF file changes in future, your script might break.

    Invoke-SQLCMD can be used from PowerShell to connect to SQL and run the commands.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Sunday, May 3, 2015 12:43 PM
  • Thanks Balmukund, Appreciate your time and answers and have what I need.
    Monday, May 4, 2015 2:52 PM