asked
Restore with Standby failed no error logs
Hello, I've a T-SQL query, invoked by powershell to restore a database with standby. Doing the query within SQL Management Studio produce the expected result. Saving the query in a .sql and running it **from within a powershell** function to a **remote server** *complete successfuly* but the database i'm restoring doesn't get set into Standby/ReadOnly mode. I've no error messages that i can refer to to debug the problem within SQL error logs file so i'm kinda hoping you guys could give me a heads up on what to do ? Here's my tested query: USE [master] ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [dbname] FROM DISK = N'somepath\dbname_Full.bak' WITH FILE = 1, STANDBY = N'somepath\dbname_RollbackUndo.bak', REPLACE ALTER DATABASE [dbname] SET MULTI_USER GO The first thing i noticed is the rollback_undo fil doesn't get created for some reason. But again, no trace whatsoever or root cause for this. Thanks for taking the time to get back to me on that. Regards, T.
There should be a message in the server log that database XYZ was successfully restored. If you aren't seeing that or can confirm via a data query of some type, then I would suspect that the database isn't actually getting restored. Can you post the PoSH query by chance so that we can see how you are doing this?
Hello JohnM, Thanks for the followup. My posh query is as follow: [
https://gist.github.com/anonymous/270e842d84bf8a64def618843356e4d9][1] Do you see something that could be improve to fix the problem i'm facing? Many thanks, again! [1]:
https://gist.github.com/anonymous/270e842d84bf8a64def618843356e4d9
using [this script][1] showed me that the restore operation did not actually occurred on database. Agreeing that, i have no trace/error message of any kind regarding a potential failure on restore operation, how can I trace the query execution so i can pinpoint what went wrong along the way? Thanks for your help! [1]:
https://www.mssqltips.com/sqlservertip/1860/identify-when-a-sql-server-database-was-restored-the-source-and-backup-date/
Hello, Just to keep you guys updated. This problem was more a powershell problem than a SQL or T-SQL ones: # First backup the primary database Invoke-Sqlcmd -InputFile "$DRbicdir\__03_DR_Database_Backup.sql" -ServerInstance "$DRMaster" -ErrorAction Stop #Copy the backup file to the secondary server/database Get-ChildItem $DRbicdir -Filter "*_Full.bak" | ForEach-Object{Copy-Item $_.FullName $DRbicdir2} ` #Then restore the database. **| ForEach-Object** {Invoke-Sqlcmd -InputFile "$DRbicdir2\__03_DR_Restore_Database.sql" -ServerInstance "$DRSlave" -ErrorAction Stop} I should not use another ForEach-Object pipeline after the first one in the command. Doing so, doesn't throw a poSH error but it doesn't run the command either. Found this out by putting verbose to the Invoke-Sqlcmd cmdlets and there i found out that the actual restore didn't take place. Plus i confirm my theory in SQL by running the below command which let me know if the restore actually took place. But it did not: SELECT [rs].[destination_database_name], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], [bs].[database_name] as [source_database_name], [bmf].[physical_device_name] as [backup_file_used_for_restore] FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC Just need to run this as is in management Studio (**credit to Thomas LaRock from
mssqltips.com**). Voila. Thanks.
18 People are following this question.
