Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to write a script which will read a folder containing a full backup and X number of log files. My script fails when it starts the loop to read the log files. The error is cannot convert varchar to nvarchar. I have tried defining @Pathandlog as a nvarchar, and I still get the same error. Any ideas?

--allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1
    GO
    --Update the currently configured value for advanced options.
    RECONFIGURE
    GO
    --Enable XP_CMDSHELL
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    --Update the currently configured value for this feature.
    RECONFIGURE
    GO

    --delete temp table if exists
    if object_id('dbo.myFileList') is not null
    begin
      drop table dbo.myFileList
    end


    --Create the table to store file list
    CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))
    --Insert file list from directory to SQL Server
    DECLARE @clientname varchar(256) ='clientname'
    DECLARE @Path varchar(256) = 'C:\Import\' + @clientname     /* ' */
    DECLARE @dbname varchar(256) = 'ApplicationV1A'+@clientname
    DECLARE @Command varchar(1024) =  'dir ' + @Path + ' /A-D  /B'
    INSERT INTO myFileList
    EXEC MASTER.dbo.xp_cmdshell @Command





    --Check the list
    SELECT * FROM myFileList


    --get first file and run restore of database - leaving db in recovery

    DECLARE @fullbackname VARCHAR(MAX)
    SELECT TOP (1) @fullbackname = FileName
    FROM dbo.myFileList
    ORDER BY Filenumber

    PRINT @fullbackname

    DECLARE @fullbacknamedb VARCHAR(MAX)='c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@dbname+'.DB'
    DECLARE @dbnamelog VARCHAR(MAX)='c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@dbname+'.log'
    PRINT @dbname
    DECLARE @tmp VARCHAR(MAX)=  @Path +'\'+ @fullbackname



    RESTORE DATABASE @dbname 

    FROM  DISK = @tmp

    WITH  FILE = 1,  MOVE N'ApplicationV1A' TO @fullbacknamedb,  MOVE N'ApplicationV1A_log' TO @dbnamelog, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

    /* temporarily disabled 

    */


    --restore db commands to go here

    --select last file for later.

    DECLARE @taillog int
    SELECT  TOP (1) @taillog = Filenumber
    FROM dbo.myFileList
    WHERE FileName IS NOT null
    ORDER BY Filenumber DESC


    DECLARE @fullbackup int
    SELECT  TOP (1) @fullbackup = Filenumber
    FROM dbo.myFileList
    ORDER BY Filenumber

    DECLARE @taillogname VARCHAR(MAX)
    SELECT  TOP (1) @taillogname = FileName
    FROM dbo.myFileList
    WHERE FileName IS NOT null
    ORDER BY Filenumber DESC

    PRINT @taillogname

    --get logfiles and run restore of database leaving db in recovery.  This must loop through all log files

    DECLARE @FileName VARCHAR (MAX)
    SELECT FileName 
    FROM dbo.myFileList
    WHERE (Filenumber > @fullbackup AND Filenumber < @taillog)
    ORDER BY Filenumber


    DECLARE @logloop INT
    SET @logloop = 1
    WHILE (@logloop <=@taillog)
    BEGIN

    PRINT @FileName
    DECLARE @Pathandlog VARCHAR(MAX) = @Path + @FileName

    DECLARE @Manu NVARCHAR(MAX)=@dbname

    RESTORE LOG @Manu
    --RESTORE LOG @dbname

    FROM DISK = @Pathandlog

    WITH FILE = 1, NORECOVERY, NOUNLOAD,  STATS = 10







    SET @logloop = @logloop + 1
    IF @logloop = @taillog
    BREAK;
    END





    RESTORE LOG [@dbname]

    FROM DISK = @Path + @FileName

    WITH NORECOVERY
    /**/




    --select each one, run restore then move to next log

    --get last logfile and run restore of database and take db out of recovery

    DECLARE @tailogname VARCHAR(MAX)
    SELECT  TOP (1) FileName
    FROM dbo.myFileList
    WHERE FileName IS NOT null
    ORDER BY Filenumber DESC

    --command to restore and take db out of recovery here

    /*
    RESTORE LOG [@dbname]

    FROM DISK = @Path+@tailogname

    WITH RECOVERY
    */


    --Clean up - may need to run this on script launch as well in case this script failed
    DROP TABLE myFileList
    GO

    --disable xp_cmdshell once job complete
    EXECUTE sp_configure 'xp_cmdshell', 0
    RECONFIGURE
share

migration rejected from stackoverflow.com Oct 6 '13 at 11:24

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Mark Storey-Smith, Mat, dezso, Max Vernon, bluefeet Oct 6 '13 at 11:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, dezso, Max Vernon, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

    
FWIW, I wouldn't do this in TSQL: it's not a good language for working with files, and using xp_cmdshell often brings up security and configuration issues. A small script in your preferred scripting language may be easier, possibly using SMO. –  Pondlife May 22 '13 at 14:17
    
I disable xp_cmdshell after the script runs. Thanks though! –  Jake Langford May 22 '13 at 15:07
    
Where does the error happen? –  gbn Jun 6 '13 at 6:51

3 Answers 3

The problem is that you're declaring @filename but not populating it with anything. So when you create @PathandLog by appending @Path with @filename you're actually setting @Pathandlog to NULL (adding NULL to anything gives you NULL). This then can't be converted by the RESTORE statement into a meaningful value, so you get the error.

It's probably worth pointing out that this could all be done in 30 lines of powershell:


Import-Module "SQLPS" -DisableNameChecking

$SQLsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("Server1")
$Restore = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Restore
$DeviceType =[Microsoft.SqlServer.Management.Smo.DeviceType]::File

$ClientName='clientname'
$BackupFolder= 'c:\import\' + $ClientName
$DataBaseName = 'ApplicationV1A' + @ClientName

$Restore.Database= $DataBaseName
$Restore.ReplaceDatabase= $TRUE
$Restore.NoRecovery = $TRUE

$rf = new-object -typename Microsoft.SqlServer.Management.Smo.RelocateFile
$rf.LogicalFileName = 'ApplicationV1A'
$rf.PhysicalFileName = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+$DataBaseName+'.DBF'
$restore.RelocateFiles.Add($rf)

$rf.LogicalFileName = 'ApplicationV1A_log'
$rf.PhysicalFileName = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+$DataBaseName+'.log'
$restore.RelocateFiles.Add($rf)

$files = Get-ChildItem $backupfolder

$i=1
foreach ($file in $files){
    if($i -eq $files.count){
        $restore.NoRecovery= $FALSE
    }
    $backupdevice = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem -ArgumentList $file.FullName, $devicetype
    $restore.devices.add($backupdevice)
    $restore.SQLRestore($sqlsvr)
    $restore.devices.remove($backupdevice)
    remove-variable backupdevice
    $i++
}

These sort of errors are one of the main reasons I gave up automating restore in T-SQL and moved over to using PowerShell.

share

I will point you to Stuart Moores excellent PowerShell Backup and Restore blog series which will do what you want, if you wish to use PowerShell to do it.

http://stuart-moore.com/category/31-days-of-sql-server-backup-and-restore-with-powershell/

share

Below script will help you to restore full backup, differential (if any) and then subsequent log backups. This is a basic script only.

Enable/disable xp_cmdshell as per your requirement.

USE Master
GO 
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Homeroom'
SET @backupPath = 'D:\backups'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile) 
FROM @fileList 
WHERE backupFile LIKE '%.BAK' 
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile) 
FROM @fileList 
WHERE backupFile LIKE '%.DIF' 
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR 
   SELECT backupFile 
   FROM @fileList
   WHERE backupFile LIKE '%.TRN' 
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles 

-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile 

WHILE @@FETCH_STATUS = 0 
BEGIN 
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' 
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile 
END

CLOSE backupFiles 
DEALLOCATE backupFiles 

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd 
share