Backup and Restore
Restore from a Database Snapshot |
|
I got asked to restore from a snapshot today. I had to look it up. Pretty simple.
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
If you want to Restore from a snapshot on thing to remember is that you will need to drop all other snapshots for that DB and then restore!!!!
For more info check out this link
http://msdn.microsoft.com/en-us/library/ms189281(v=sql.110).aspx#Restrictions
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
If you want to Restore from a snapshot on thing to remember is that you will need to drop all other snapshots for that DB and then restore!!!!
For more info check out this link
http://msdn.microsoft.com/en-us/library/ms189281(v=sql.110).aspx#Restrictions
Automating your Database Restore |
|
Here is a good article on automating Restores.
http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
This will only work if you have certain formatting in your backup statements.
The script needs some additional logic to handle backups that have multiple files.
http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
This will only work if you have certain formatting in your backup statements.
The script needs some additional logic to handle backups that have multiple files.
Powershell Restore Script |
|
This is obsolete now if you are using DBATools
Check out this link...
Restor-DbaDatabase
I had a stretch where I was doing a ton of restores to different locations. This made my life a lot easier. This script includes the MOVE statement. If you have questions reach out to me. [email protected]
The version of SQL must match the Instance you use to query the headers. So if backup files are from a 2012 instance then the instance used to query the file headers must also be 2012. Sounds pretty basic but it baffled me for a while.
Will be adding a dynamic method to find the last backup. Still testing.
You can download the script at the conclusion of the code.
CLS
#######################################################
##
## populate server name
## populate db name
## Define the path and files variables for the restore statement
## populate $files to find based upon a timeframe, if no backup files found increase the addhours value, and then sort the .bak files
##
#######################################################
$server = "<servername>"
$DBName = "<dbname>"
$path = "<backuplocation>"
#######################################################
##
## filters backup location for .bak files where lastwritetime is 0-23 hours old. it will then sort those files.
## if no current files returned then change the addhours duration. backup job could have failed
##
#######################################################
$files = gci $path -Filter *.bak | Where-Object{$_.LastWriteTime -gt (Get-Date).AddHours(-24)} | Sort-Object -Property {[int]$_.Name.Split('of')[0]}
## place 1st backup files into variable in case there are multiple to use in the sql statement
$bak = $files[0]
$trn = $files[0]
#######################################################
##
## query to find logical and physical names of the DB
##
## use backup header as source for file locations
##
#######################################################
## First version that would not really help you in a disaster
#######################################################
##$moveMDF = invoke-sqlcmd -ServerInstance $server -Database MASTER -Query "SELECT m.name AS Logical_Name, m.physical_name
## FROM sys.databases d
## JOIN
## sys.master_files m
## ON d.database_id = m.database_id
## WHERE m.type_desc = 'rows' AND d.name = '$DBNAME'"
##
##
##$moveLDF = invoke-sqlcmd -ServerInstance $server -Database MASTER -Query "SELECT m.name AS Logical_Name, m.physical_name
## FROM sys.databases d
## JOIN
## sys.master_files m
## ON d.database_id = m.database_id
## WHERE m.type_desc = 'log' AND d.name = '$DBNAME'"
#######################################################
#######################################################
##
## query to find logical and physical names of the DB
##
## use backup header as source for file locations
##
## as of this version, finding the logical file names does not work on 2012. need to spend the time to fix
#######################################################
$moveMDF = invoke-sqlcmd -ServerInstance <DB instance to query file headers> -Database MASTER -Query "DECLARE @stmt TABLE
(
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) NULL ,
UniqueID UNIQUEIDENTIFIER ,
ReadOnlyLSN NUMERIC(25, 0) NULL ,
ReadWriteLSN NUMERIC(25, 0) NULL ,
BackupSizeInBytes BIGINT ,
SourceBlockSize INT ,
FileGroupID INT ,
LogGroupGUID UNIQUEIDENTIFIER NULL ,
DifferentialBaseLSN NUMERIC(25, 0) NULL ,
DifferentialBaseGUID UNIQUEIDENTIFIER ,
IsReadOnly BIT ,
IsPresent BIT ,
TDEThumbprint VARBINARY(32)
)
INSERT INTO @stmt
EXEC
( 'RESTORE FILELISTONLY
FROM DISK =
''$($path)$($bak)'''
)
SELECT logicalname AS Logical_Name ,
PhysicalName AS Physical_Name
FROM @stmt
WHERE type IN ('d','f')"
$moveLDF = invoke-sqlcmd -ServerInstance <DB instance to query file header> -Database MASTER -Query "DECLARE @stmt TABLE
(
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) NULL ,
UniqueID UNIQUEIDENTIFIER ,
ReadOnlyLSN NUMERIC(25, 0) NULL ,
ReadWriteLSN NUMERIC(25, 0) NULL ,
BackupSizeInBytes BIGINT ,
SourceBlockSize INT ,
FileGroupID INT ,
LogGroupGUID UNIQUEIDENTIFIER NULL ,
DifferentialBaseLSN NUMERIC(25, 0) NULL ,
DifferentialBaseGUID UNIQUEIDENTIFIER ,
IsReadOnly BIT ,
IsPresent BIT ,
TDEThumbprint VARBINARY(32)
)
INSERT INTO @stmt
EXEC
( 'RESTORE FILELISTONLY
FROM DISK =
''$($path)$($trn)'''
)
SELECT logicalname AS Logical_Name ,
PhysicalName AS Physical_Name
FROM @stmt
WHERE type = 'l'"
#######################################################
##
## variables to complete syntax in restore statements
##
#######################################################
$disk = " disk = '"
$QuoteComma = "',"
$EndingQuote = "'"
#######################################################
##
## set loop variable
##
#######################################################
$i = 0
#######################################################
##
## creates beginning of restore statemtent. Filelistonly if you need it.
##
#######################################################
write-host "--Restore Filelistonly"
write-host "Restore Database [$DBName]"
write-host "From" -NoNewline
#######################################################
##
## counts number of files. if only one .bak file then will execute the following statement
##
#######################################################
if($files.count -eq 1)
{$disk + $path + $files[$i]+$EndingQuote }
else
{
#######################################################
##
## loop through .bak files and puncate correctly
##
#######################################################
while($i -lt $files.Length)
{
##last .bak file ends with no comma
if ($i -eq $files.Length -1)
{$disk + $path + $files[$i]+$EndingQuote }
else
{$disk + $path + $files[$i] +$QuoteComma}
$i++
}
}
#######################################################
##
## starts the with statement
##
#######################################################
write-host " with"
#######################################################
##
## create the move statement to relocate the backup if needed.
## this section will not return info if used on a SQL 2000 box
##
#######################################################
foreach($files in $moveMDF)
{
write-host " move '$($files[0])' to '$($files[1])', --change path to new location" }
foreach($logfiles in $moveLDF){
write-host " move '$($logfiles[0])' to '$($logfiles[1])', --change path to new location"
}
write-host " replace, stats =5, NORECOVERY"
#######################################################
##
## Transaction log restore statements
##
#######################################################
$TransLogs = gci $path -Filter *.trn | ?{$_.lastwritetime -gt (get-date).Addhours(-48)} | SORT
foreach($logfiles in $TransLogs){write-host "RESTORE log [$Dbname] "
write-host "from disk = '$($path)$($logfiles)'"
write-host "with norecovery, stats = 5"}
##write-host "--Restore Database [$DBName] with recovery"
Check out this link...
Restor-DbaDatabase
I had a stretch where I was doing a ton of restores to different locations. This made my life a lot easier. This script includes the MOVE statement. If you have questions reach out to me. [email protected]
The version of SQL must match the Instance you use to query the headers. So if backup files are from a 2012 instance then the instance used to query the file headers must also be 2012. Sounds pretty basic but it baffled me for a while.
Will be adding a dynamic method to find the last backup. Still testing.
You can download the script at the conclusion of the code.
CLS
#######################################################
##
## populate server name
## populate db name
## Define the path and files variables for the restore statement
## populate $files to find based upon a timeframe, if no backup files found increase the addhours value, and then sort the .bak files
##
#######################################################
$server = "<servername>"
$DBName = "<dbname>"
$path = "<backuplocation>"
#######################################################
##
## filters backup location for .bak files where lastwritetime is 0-23 hours old. it will then sort those files.
## if no current files returned then change the addhours duration. backup job could have failed
##
#######################################################
$files = gci $path -Filter *.bak | Where-Object{$_.LastWriteTime -gt (Get-Date).AddHours(-24)} | Sort-Object -Property {[int]$_.Name.Split('of')[0]}
## place 1st backup files into variable in case there are multiple to use in the sql statement
$bak = $files[0]
$trn = $files[0]
#######################################################
##
## query to find logical and physical names of the DB
##
## use backup header as source for file locations
##
#######################################################
## First version that would not really help you in a disaster
#######################################################
##$moveMDF = invoke-sqlcmd -ServerInstance $server -Database MASTER -Query "SELECT m.name AS Logical_Name, m.physical_name
## FROM sys.databases d
## JOIN
## sys.master_files m
## ON d.database_id = m.database_id
## WHERE m.type_desc = 'rows' AND d.name = '$DBNAME'"
##
##
##$moveLDF = invoke-sqlcmd -ServerInstance $server -Database MASTER -Query "SELECT m.name AS Logical_Name, m.physical_name
## FROM sys.databases d
## JOIN
## sys.master_files m
## ON d.database_id = m.database_id
## WHERE m.type_desc = 'log' AND d.name = '$DBNAME'"
#######################################################
#######################################################
##
## query to find logical and physical names of the DB
##
## use backup header as source for file locations
##
## as of this version, finding the logical file names does not work on 2012. need to spend the time to fix
#######################################################
$moveMDF = invoke-sqlcmd -ServerInstance <DB instance to query file headers> -Database MASTER -Query "DECLARE @stmt TABLE
(
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) NULL ,
UniqueID UNIQUEIDENTIFIER ,
ReadOnlyLSN NUMERIC(25, 0) NULL ,
ReadWriteLSN NUMERIC(25, 0) NULL ,
BackupSizeInBytes BIGINT ,
SourceBlockSize INT ,
FileGroupID INT ,
LogGroupGUID UNIQUEIDENTIFIER NULL ,
DifferentialBaseLSN NUMERIC(25, 0) NULL ,
DifferentialBaseGUID UNIQUEIDENTIFIER ,
IsReadOnly BIT ,
IsPresent BIT ,
TDEThumbprint VARBINARY(32)
)
INSERT INTO @stmt
EXEC
( 'RESTORE FILELISTONLY
FROM DISK =
''$($path)$($bak)'''
)
SELECT logicalname AS Logical_Name ,
PhysicalName AS Physical_Name
FROM @stmt
WHERE type IN ('d','f')"
$moveLDF = invoke-sqlcmd -ServerInstance <DB instance to query file header> -Database MASTER -Query "DECLARE @stmt TABLE
(
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) NULL ,
UniqueID UNIQUEIDENTIFIER ,
ReadOnlyLSN NUMERIC(25, 0) NULL ,
ReadWriteLSN NUMERIC(25, 0) NULL ,
BackupSizeInBytes BIGINT ,
SourceBlockSize INT ,
FileGroupID INT ,
LogGroupGUID UNIQUEIDENTIFIER NULL ,
DifferentialBaseLSN NUMERIC(25, 0) NULL ,
DifferentialBaseGUID UNIQUEIDENTIFIER ,
IsReadOnly BIT ,
IsPresent BIT ,
TDEThumbprint VARBINARY(32)
)
INSERT INTO @stmt
EXEC
( 'RESTORE FILELISTONLY
FROM DISK =
''$($path)$($trn)'''
)
SELECT logicalname AS Logical_Name ,
PhysicalName AS Physical_Name
FROM @stmt
WHERE type = 'l'"
#######################################################
##
## variables to complete syntax in restore statements
##
#######################################################
$disk = " disk = '"
$QuoteComma = "',"
$EndingQuote = "'"
#######################################################
##
## set loop variable
##
#######################################################
$i = 0
#######################################################
##
## creates beginning of restore statemtent. Filelistonly if you need it.
##
#######################################################
write-host "--Restore Filelistonly"
write-host "Restore Database [$DBName]"
write-host "From" -NoNewline
#######################################################
##
## counts number of files. if only one .bak file then will execute the following statement
##
#######################################################
if($files.count -eq 1)
{$disk + $path + $files[$i]+$EndingQuote }
else
{
#######################################################
##
## loop through .bak files and puncate correctly
##
#######################################################
while($i -lt $files.Length)
{
##last .bak file ends with no comma
if ($i -eq $files.Length -1)
{$disk + $path + $files[$i]+$EndingQuote }
else
{$disk + $path + $files[$i] +$QuoteComma}
$i++
}
}
#######################################################
##
## starts the with statement
##
#######################################################
write-host " with"
#######################################################
##
## create the move statement to relocate the backup if needed.
## this section will not return info if used on a SQL 2000 box
##
#######################################################
foreach($files in $moveMDF)
{
write-host " move '$($files[0])' to '$($files[1])', --change path to new location" }
foreach($logfiles in $moveLDF){
write-host " move '$($logfiles[0])' to '$($logfiles[1])', --change path to new location"
}
write-host " replace, stats =5, NORECOVERY"
#######################################################
##
## Transaction log restore statements
##
#######################################################
$TransLogs = gci $path -Filter *.trn | ?{$_.lastwritetime -gt (get-date).Addhours(-48)} | SORT
foreach($logfiles in $TransLogs){write-host "RESTORE log [$Dbname] "
write-host "from disk = '$($path)$($logfiles)'"
write-host "with norecovery, stats = 5"}
##write-host "--Restore Database [$DBName] with recovery"
Powershell Restore Script | |
File Size: | 7 kb |
File Type: | ps1 |
Generate Restore Log Statements using MSDB |
|
USE msdb;
SELECT 'RESTORE LOG '+msdb.dbo.backupset.database_name+' from disk = '+ ''''+'\\unc path is needed\'+REPLACE(physical_device_name,':','$')+''''+' with norecovery, stats = 5;',
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.backup_start_date > 'enter start teim' and database_name = 'dbname' AND TYPE = 'L'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
SELECT 'RESTORE LOG '+msdb.dbo.backupset.database_name+' from disk = '+ ''''+'\\unc path is needed\'+REPLACE(physical_device_name,':','$')+''''+' with norecovery, stats = 5;',
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.backup_start_date > 'enter start teim' and database_name = 'dbname' AND TYPE = 'L'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Determine how much has changed in your DB since the last backup |
|
The following links discuss backup strategies regarding full vs diff backups and how to find out how much data has changed since last backup.
http://sqlmag.com/database-backup-and-recovery/determining-how-much-data-has-changed
http://sqlmag.com/database-backup-and-recovery/step-step-approach-differential-backup-and-recovery
http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/
http://sqlmag.com/database-backup-and-recovery/determining-how-much-data-has-changed
http://sqlmag.com/database-backup-and-recovery/step-step-approach-differential-backup-and-recovery
http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/