Category Archives: Restores

Inserting results of FILELISTONLY to a table

I was looking for a code example of this on the web, but couldn’t find it, so here it is. You would need to create the @cmd of course with your own variables.

SELECT  @cmd = 'restore filelistonly  from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] CHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] NUMERIC(20,0) ,
[MaxSize] numeric(20,0) ,
[FileId] BIGINT ,
[CreateLSN] NUMERIC(25, 0) ,
[DropLSN] NUMERIC(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] NUMERIC(25, 0) ,
[ReadWriteLSN] NUMERIC(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] NUMERIC(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)

Restore Database From Latest Restore

Here is a bit of code, adapted from Nigel Rivett’s original. You can use this to copy one database over another or restore the most recent database over itself.

--BEGIN TRAN

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[s_RestoreLatestBackup]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[s_RestoreLatestBackup]
GO

CREATE PROC s_RestoreLatestBackup
@dbname VARCHAR(128) ,
@destdbname VARCHAR(128) ,
@localBackupPath VARCHAR(200) ,
@localDBPath VARCHAR(200) ,
@localLogPath VARCHAR(200) ,
@recipients VARCHAR(128),
@filenamesuffix VARCHAR(50)
AS /*
exec s_RestoreLatestBackup
@dbname = 'hrmprot' ,
@destdbname = 'HRMPROT2',
@localBackupPath = 'K:\Backups\' ,
@localDBPath = 'I:\Data\' ,
@localLogPath = 'J:\Logs\' ,
@recipients = 'myemailaddress',
@filenamesuffix = '_2'
*/

DECLARE @cmd VARCHAR(2000) ,
@filename VARCHAR(128) ,
@s VARCHAR(128) ,
@i INT ,
@d DATETIME ,
@sql NVARCHAR(2000) ,
@StartDate DATETIME

SELECT @StartDate = GETDATE()

-- drop database
IF EXISTS ( SELECT *
FROM master..sysdatabases
WHERE name = @destdbname )
BEGIN
SELECT @cmd = 'drop database ' + @destdbname
EXEC (@cmd)
END

-- get latest backup filename
SELECT @cmd = 'dir /B ' + @localBackupPath + @dbname + '\*.*'
PRINT @CMD
CREATE TABLE #a ( s VARCHAR(2000) )
INSERT #a
EXEC master..xp_cmdshell @cmd

DELETE #a
WHERE s IS NULL
OR s NOT LIKE '%_backup_%'

SELECT *
FROM #a

SELECT @filename = MAX(s)
FROM #a

-- Get files in backup
SELECT @cmd = 'restore filelistonly from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] CHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] NUMERIC(20,0) ,
[MaxSize] numeric(20,0) ,
[FileId] BIGINT ,
[CreateLSN] NUMERIC(25, 0) ,
[DropLSN] NUMERIC(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] NUMERIC(25, 0) ,
[ReadWriteLSN] NUMERIC(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] NUMERIC(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)

SELECT *
FROM #files
-- buld the restore command
SELECT @cmd = NULL ,
@s = ''

WHILE @s < ( SELECT MAX(LogicalName)
FROM #files
)
BEGIN
SELECT @s = MIN(LogicalName)
FROM #files
WHERE LogicalName > @s


SELECT @cmd = COALESCE(@cmd + ', move ', '') + '''' + @s
+ ''' to '''
+ CASE WHEN #files.Type = 'D' THEN @localDBPath
ELSE @localLogPath
END
+ CASE WHEN #files.Type = 'D' THEN + LogicalName + @filenamesuffix + '.mdf'
ELSE LogicalName + @filenamesuffix + '.ldf'
END
+ ''''
FROM #files
WHERE LogicalName = @s
END
SELECT @cmd = 'restore database ' + @destdbname + ' from disk = '''
+ @localBackupPath + @dbname + '\' + @filename + ''' with move '
+ @cmd -- + ', standby = ''' + @localBackupPath + 'standby.fil'''

PRINT @cmd

--restore the database
select (@cmd)

exec (@cmd)

drop table #files
drop table #a

-- Check that the restore was successful
select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out

/*
if @d > @StartDate and @i > 20
begin
if @recipients is not null
begin
select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
end
else
begin
select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
if @recipients is not null
begin
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
raiserror(@cmd, 16, -1)
end
*/
go

--COMMIT TRAN

Creating Restore Statements With Powershell

WARNING: Use caution in running scripts you find on the internet. You are responsible for the well being of your systems!

We’re sitting around waiting for the SAN monkeys to fling some crap our way – not the SAN guys, the SAN monkeys, kinda like gremlins, but not as bad. Our primary cluster is down, which runs 100+ databases. Maybe the disks will come up – or maybe they won’t.

Assessing the situation, I found that the MSDB was not in full recovery mode. People, please don’t run around changing DBs to simple to ‘save space’ without thinking the ramifications through. In this case, we lose all backup history so we now need to figure out what backup file is the most recent and then which log files come after that .bak file – then we slam them together into a file, leaving the NORECOVERY clause off the last log restore statement. If you think I’m doing this by hand, you’re crazy.

We make an assumption here. Each file holds only one backup set. This is how we do our backup files, so I’m OK with this assumption.


$DatabaseBackupRoot = “\\MYSERVA\e$\Backups\"
$fix = ""

$DatabaseBackupDirs = gci $DatabaseBackupRoot

foreach ($DatabaseBackupDir in $DatabaseBackupDirs)
{
$DB = $DatabaseBackupDir.Name

$BackupRestore = ""
$LogRestore = ""
$LogRestore = ""
$BackupRestore = ""
# Parameters

$Path = “\\MYSERVA\e$\Backups\$DB”;
$FullPattern = “*.bak”;
$LogPattern = “*.trn”;

# Enumerate Last Full Back
$FullBackup = Get-ChildItem $Path -Filter ($DB + $FullPattern) `
| sort Name `
| Select-Object -Last 1;

# Enumerate Log Backups
$LogBackups = Get-ChildItem $Path -Filter ($DB + $LogPattern) `
| Where-Object { $_.LastWriteTime -gt $Fullbackup.LastWriteTime} `
| sort Name;

$LogBackupsCount = $LogBackups.Count

$BackupFileName = $FullBackup.Name
# if the backupfile is null, skip this
if ($BackupFileName)
{

if ($LogBackupsCount > 0)
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$BackupRestore = "RESTORE DATABASE [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$BackupFileName' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"
}

}
else{
#We get a list in the output of emtpy directories.
Write-Output $DB
}

$counter = 0
$len = $LogBackups.Count

foreach ($LogBackup in $LogBackups)
{
$counter++

$LogBackupFile = $LogBackup.Name;
#Test for no log/empty directory
if($LogBackupFile)
{
if($counter -lt $len)
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
"
}
else
{
$LogRestore = $LogRestore + "
RESTORE LOG [$db] FROM DISK = N'$DatabaseBackupRoot$DB\$LogBackupFile' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
"

}
}

}

$RestoreOneDatabase = $BackupRestore + $LogRestore

$RestoreAllDatabases = $RestoreAllDatabases + $RestoreOneDatabase

}

$RestoreAllDatabases | out-file "c:\temp\restore-MYSERVA.sql"

So now we’ve got a SQL statement with all these restores compiled together and separated with GO statements. Pretty neat. I’d probably run small blocks of them and fix any issues the script might have.