在SQL Server中还原数据库失败

I have written a PowerShell script to back up my databases using the lite speed console.

我编写了一个PowerShell脚本来使用lite速度控制台备份我的数据库。

In the process the various configurations are read from the xml file associated.

在此过程中,将从关联的xml文件中读取各种配置。

Now I want to restore them, SO I used lite speed again to restore it.

现在我想要恢复它们,所以我再次使用lite速度来恢复它。

But its returning me an error

但它给我一个错误

RESTORE DATABASE is terminating abnormally.
The tail of the log for the database "AK4432_JIM1" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

RESTORE DATABASE异常终止。数据库“AK4432_JIM1”的日志尾部尚未备份。如果日志包含您不想丢失的工作,请使用BACKUP LOG WITH NORECOVERY来备份日志。使用RESTORE语句的WITH REPLACE或WITH STOPAT子句只覆盖日志的内容。

It says the log has to be backed up.

它说必须备份日志。

Backing up the log throws this error (Why should I back up the log ???)

备份日志会抛出此错误(为什么要备份日志???)

"Incorrect syntax near 'C:\Users\ak4432\Desktop\PS\Backup2'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."
At C:\Users\ak4432\Desktop\PS\BackUpAndRollBackScript.ps1:49 char:29
+ $cmd.ExecuteNonQuery <<<< ()
+ CategoryInfo : NotSpecified: (:) [],
MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

“'C:\ Users \ ak4432 \ Desktop \ PS \ Backup2'附近的语法不正确。关键字'with'附近的语法不正确。如果此语句是公用表表达式,xmlnamespaces子句或更改跟踪上下文子句,则前面的语句必须以分号结束。“在C:\ Users \ ak4432 \ Desktop \ PS \ BackUpAndRollBackScript.ps1:49 char:29 + $ cmd.ExecuteNonQuery <<<<()+ CategoryInfo:NotSpecified:(:) [],MethodInvocationException + FullyQualifiedErrorId:DotNetMethodException

The recovery model for the DBs is either Full or BULK_LOGGED

DB的恢复模型为Full或BULK_LOGGED

I am attaching my .ps script for you reference

我附上我的.ps脚本供你参考

function Get-ScriptDirectory
{
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path
}

function SendEmail($to, $subject, $body, $from, $attachLogFilePath,$attachErrorFilePath) 
{
  $to= "egalitarian@xyz.com"
  send-mailmessage -from $from  -to $to -subject $subject -body $body  -smtpServer "zsserver3.zs.local" -Attachments $attachLogFilePath,$attachErrorFilePath
}

function PutDbOffline($connectionString,$databaseName,$logFilePath,$dbBackUpFolder,$serverName,$processName, $processPath, $processArguments, $onError, $backup)
{
  # connect to Db and then get the DB offline 
  $connection = new-object System.Data.SqlClient.SqlConnection($connectionString)
  $connection.open()
  $sqlQuery = "USE MASTER; EXEC sp_dboption N`'" + $databaseName + "`' , N`'offline`', N`'true`'"
  $cmd = new-object "System.Data.SqlClient.SqlCommand" ($sqlQuery, $connection)
  $cmd.ExecuteNonQuery()
  $connection.close()
}

function Restore($connectionString,$databaseName,$logFilePath,$dbBackUpFolder,$serverName,$processName, $processPath, $processArguments, $onError, $backup)
{
   $combinedProcessPath= Join-Path $processPath $processName

   #dump the output to a log file
   $logFileName =  $processName + $databaseName
   $logFileName+= "_"
   $logFileName += "{0:MMddyyyy-HH mm}" -f (Get-Date) 
   $combinedLogFilePath = Join-Path ($logFilePath) ($logFileName)
   $combinedErrorLogFilePath = $combinedLogFilePath + "_error"
   $dbBackUpFile = $databaseName + ".BAK"
   $databaseBackUpPath = Join-Path ($dbBackUpFolder) ($dbBackUpFile)

   $processArguments =  "" 

   if($backup -eq "Yes")
   {
        $connection = new-object System.Data.SqlClient.SqlConnection($connectionString)
        $connection.open()

        $sqlQuery = "BACKUP LOG " + $databaseName + " TO N `'" + $dbBackUpFolder + "`'  WITH NORECOVERY ;" 
        $cmd = new-object "System.Data.SqlClient.SqlCommand" ($sqlQuery, $connection)
        $cmd.ExecuteNonQuery()
        $connection.close()
        $processArguments = " -S " + $serverName + " -T -B Database -D " + $databaseName + " -F `""+ $databaseBackUpPath + "`"" 
   }
   else
   {
      #  PutDbOffline $connectionString $databaseName $logFilePath $dbBackUpFolder $serverName $processName, $processPath $processArguments $onError  $backup

        $processArguments = " -S " + $serverName +  " -R  DataBase -D " + $databaseName + " -F `"" + $databaseBackUpPath + "`"" 
   }

   $process = Start-Process -PassThru -Filepath $combinedProcessPath -WorkingDirectory $processPath -ArgumentList $processArguments -RedirectStandardOutput $combinedLogFilePath -RedirectStandardError $combinedErrorLogFilePath -wait -NoNewWindow

   if ($process.ExitCode -ne 0)
   {
       $mailSubject = "[02SS Back Up Status] " + $processName + " failed on "  + $serverName
       $body = "Process Failed, Exited with Code - " + $process.ExitCode + ". See attached files for details."  

       if($onError -eq "Break")
       {
          $body = $body + " Breaking from the power shell script."
          SendEmail "" $mailSubject $body "O2SSConversion@zsassociates.com" $combinedLogFilePath  $combinedErrorLogFilePath 
          return  "FAILED"
       }
       else
       {                
          SendEmail "" $mailSubject $body "O2SSConversion@zsassociates.com" $combinedLogFilePath  $combinedErrorLogFilePath 
       }
    }
    else
    {
       $mailSubject = "[02SS Back Up Status] " + $processName + " ran successfully on "  + $serverName
       $body = "Process Successful, Exited with Code - " + $process.ExitCode + ". See attached files for details."  
       SendEmail "" $mailSubject $body "O2SSConversion@zsassociates.com" $combinedLogFilePath  $combinedErrorLogFilePath 
    }
}


# Load the XML FILE 
$sourceFile = Join-Path (Get-ScriptDirectory) ("BackUpAndRollBackConfiguration.xml")
$xDoc = new-Object System.Xml.XmlDocument
$xDoc.Load($sourceFile)

# Get settings to connect to DB  
$serverName = $xDoc.selectSingleNode("/configuration/appSettings/ServerName").get_InnerXml()
$databaseName = $xDoc.selectSingleNode("/configuration/appSettings/Database").get_InnerXml()
$userName = $xDoc.selectSingleNode("/configuration/appSettings/UserName").get_InnerXml()
$password = $xDoc.selectSingleNode("/configuration/appSettings/Password").get_InnerXml()
$logFilePath = $xDoc.selectSingleNode("/configuration/appSettings/logFilePath").get_InnerXml()
$dbBackUpFolder = $xDoc.selectSingleNode("/configuration/appSettings/DatabaseBackUpFolder").get_InnerXml()
#Create connection string
$connectionString = "server=" + $serverName + ";Database=" + $databaseName +";uid=" + $userName + ";pwd=" + $password

#Get Settings to decide whether its a RollBack or BackUp
$backup = $xDoc.selectSingleNode("/configuration/appSettings/BackUp").get_InnerXml()

#Declare an array to hold DB names .. Being populated later
$dbIdentifiers =@()

# Get the Process Parameter from File

$processName=""
$processPath=""
$processArguments=""
$onError = ""

$processes = $xDoc.selectnodes("/configuration/processes/process")
foreach ($process in $processes) {

    $processName=$process.selectSingleNode("processName").get_InnerXml()
    $processPath=$process.selectSingleNode("processPath").get_InnerXml()
    $processArguments=$process.selectSingleNode("processArguments").get_InnerXml()
    $onError = $process.selectSingleNode("OnError").get_InnerXml()    

}


if($backup -eq "No")
{
$returnType = Restore $connectionString $databaseName $logFilePath $dbBackUpFolder $serverName $processName $processPath $processArguments $onError $backup
if ($returnType -eq "FAILED")
{
break
}
}

#Migrate the Master Db And Scn Dbs Now

# Connect to Db and then get the SCN Db Identifier

            $Table = new-object System.Data.DataTable
            $sqlConn = new-object System.Data.SqlClient.SqlConnection($connectionString)
            $sqlConn.open()
            $adapter = new-object System.Data.SqlClient.SqlDataAdapter("SELECT DBIDENTIFIER FROM SCENARIOS",$sqlConn)
            $adapter.Fill($Table)
            $sqlConn.close()
            # Populate the db Identifer Array to include  master Db and SCN Db.
            if($backup -eq "Yes")
            {
              $dbIdentifiers += , $databaseName
            }
            foreach ($row in $Table) 
            {
             $dbIdentifiers+= , $row.DBIDENTIFIER
            }


            foreach ($dbIdentifier in $dbIdentifiers) {
            if($processPath)
            {

            $returnType =  Restore $connectionString $dbIdentifier $logFilePath $dbBackUpFolder $serverName $processName $processPath $processArguments $onError $backup
            if ($returnType -eq "FAILED")
            {
                break
            }
            }
}

Can someone help me to resolve the issue, as my mind has really stopped working now

有人可以帮助我解决这个问题,因为我的思绪现在已经停止了

2 个解决方案

#1

It says the log has to be backed up. (Why should I back up the log ???)

它说必须备份日志。 (为什么要备份日志???)

This is a safety feature. SQL Server thinks you're overwriting a live production database with a a past backup of the same database, so it wants you to back up the tail of the log first to catch any transactions that have occurred since the last transaction log backup.

这是一个安全功能。 SQL Server认为您使用同一数据库的过去备份覆盖实时生产数据库,因此它希望您首先备份日志尾部以捕获自上次事务日志备份以来发生的任何事务。

Here's instructions from Microsoft on backing up the tail of the log.

以下是Microsoft关于备份日志尾部的说明。

You can also avoid this by dropping the database first and then restoring, or by using WITH REPLACE in your restore, which tells SQL Server you're overwriting that database with something else entirely.

您还可以通过首先删除数据库然后还原,或者在还原中使用WITH REPLACE来避免这种情况,这会告诉SQL Server您完全覆盖了该数据库。

#2

If you are backing up to a file, correct syntax would be:

如果要备份到文件,正确的语法将是:

BACKUP LOG dbname TO DISK='path\to\filename' WITH NORECOVERY

However, you don't actually need to backup the log if you don't need the current data anymore (and that's what the error message says). You just need to provide WITH REPLACE option to RESTORE which will replace the existing log and datafiles.

但是,如果您不再需要当前数据,则实际上不需要备份日志(这就是错误消息所说的内容)。您只需要为RESTORE提供WITH REPLACE选项,它将替换现有的日志和数据文件。

#1