Problem: How to restore Full,
differential backup of database.
Solution: RESTORE (Transact-SQL)
Describes
the RESTORE DATABASE and RESTORE LOG Transact-SQL statements used to restore
and recover a database from backups taken using the BACKUP command. RESTORE
DATABASE is used for databases under all recovery models. RESTORE LOG is used
only under the full and bulk-logged recovery models. RESTORE DATABASE can also
be used to revert a database to a database snapshot.
Restore Database is the command we have to fire on the server
pointing to restore from a backup of the database.
At times we restore the database to test our scripts and
upgrades to verify before we fix or give a solution.
I usually restore the database after testing some new
implementations to reset it back where it was.
Example:
In this article
I am going to show you how we can create a full database backup, differential
backup to a disk, then restore the full, differential backups.
Example1:
--
Backing up a database to disk with noformat
to add to existing file
BACKUP DATABASE DB to
disk = 'C:\Temp\db.bak'
WITH NOFORMAT
--backing
up the log files to a disk(set the recover mode to full and also take a full
backup before you run the log backup)
ALTER DATABASE DB SET
RECOVERY FULL
BACKUP LOG DB to
disk = 'C:\Temp\db.bak'
WITH NOFORMAT
--
Backing up a database differentially to a disk.
BACKUP DATABASE DB to
DISK ='C:\Temp\Db_diff.bak'
WITH DIFFERENTIAL,INIT
--
Restoring the database backup to an existing Database uses replace.
RESTORE DATABASE DB
FROM DISK =
'C:\TEmp\Db.bak'
WITH REPLACE,NORECOVERY;
OUTPUT:
Processed 1760 pages for database 'DB', file 'DB'
on file 1.
Processed 2 pages for database 'DB', file 'DB_log'
on file 1.
RESTORE DATABASE successfully processed 1762 pages
in 0.308 seconds (44.693 MB/sec).
Featured
Script:
go
--***
RUN ON THE SERVER WHERE YOU WANT TO RESTORE THE DATABASE
DECLARE
@BackupLocation varchar(4000) = 'C:\Temp\DB.bak' --$(BackupLocation)
, @SubDB SYSNAME = N'DB' --$(DB)
,@msg NVARCHAR(MAX)
,@FileName nvarchar(128)
SET NOCOUNT ON
SET @FileName = 'RestoreDB'
SET @msg = N'STARTING ' +
@FileName
RAISERROR (@msg, 0, 1) WITH NOWAIT
DECLARE @Message VARCHAR(1000)
DECLARE
@DataLocation
SYSNAME,
@SqlServerMajorVer
decimal(10,2),
@ListSQL
NVARCHAR(4000),
@MoveSQL
NVARCHAR(4000),
@REPLACE
NVARCHAR(50),
@TEMP
NVARCHAR(1024),
@ExecSQL
NVARCHAR(1024)
SET @REPLACE = ''
set @DataLocation=(select SUBSTRING(physical_name,1, charindex('\master.mdf',physical_name, 1)) from sys.master_files where
name = 'master')
BEGIN TRY
--SELECT @ExecSQL =
'ALTER database ['+@SubDB+'] set single_user with ROLLBACK IMMEDIATE;'
--EXEC (@ExecSQL)
SET @msg = N'... killing active connections to the "' + @SubDB + '" database'
RAISERROR (@msg, 0, 1) WITH NOWAIT
SET @ExecSQL = ''
SELECT
@ExecSQL = @ExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = @SubDB AND DBID <> 0 AND spid <> @@spid
EXEC (@ExecSQL)
END TRY
BEGIN CATCH
END CATCH
IF exists (select name from sys.databases where name
= @SubDB)
SET @REPLACE = ', REPLACE'
SET @ListSQL = ''
SET @ListSQL =
@ListSQL + 'IF
OBJECT_ID(''tempdb..##FILE_LIST'') IS NOT NULL '
SET @ListSQL =
@ListSQL + 'DROP TABLE
##FILE_LIST'
SET @ListSQL =
@ListSQL + ' CREATE
TABLE ##FILE_LIST ('
SET @ListSQL =
@ListSQL + ' LogicalName VARCHAR(64),'
SET @ListSQL =
@ListSQL + ' PhysicalName VARCHAR(130),'
SET @ListSQL =
@ListSQL + ' [Type] VARCHAR(1),'
SET @ListSQL =
@ListSQL + ' FileGroupName VARCHAR(64),'
SET @ListSQL =
@ListSQL + ' Size DECIMAL(20, 0),'
SET @ListSQL =
@ListSQL + ' MaxSize DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' FileID bigint,'
SET @ListSQL =
@ListSQL + ' CreateLSN DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' DropLSN DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' UniqueID UNIQUEIDENTIFIER,'
SET @ListSQL =
@ListSQL + ' ReadOnlyLSN DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' ReadWriteLSN DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' BackupSizeInBytes DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' SourceBlockSize INT,'
SET @ListSQL =
@ListSQL + ' filegroupid INT,'
SET @ListSQL =
@ListSQL + ' loggroupguid UNIQUEIDENTIFIER,'
SET @ListSQL =
@ListSQL + ' differentialbaseLSN DECIMAL(25,0),'
SET @ListSQL =
@ListSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'
SET @ListSQL =
@ListSQL + ' isreadonly BIT,'
SET @ListSQL =
@ListSQL + ' ispresent BIT'
Set @SqlServerMajorVer = (SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 1,
CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) + 1) AS DECIMAL(10,2)))
IF @SqlServerMajorVer >
10
BEGIN
SET @ListSQL =
@ListSQL + ',
TDEThumbpr VARBINARY(32)'
PRINT @SqlServerMajorVer
END
SET @ListSQL =
@ListSQL + ')'
EXEC (@ListSQL)
INSERT INTO
##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupLocation + '''')
DECLARE curFileLIst CURSOR
FOR
SELECT 'MOVE N''' + LogicalName + ''' TO N''' + reverse(substring(reverse(PhysicalName),1, 4) + reverse(logicalName) + reverse(@SubDB) + reverse(@DataLocation)) + ''''
FROM
##FILE_LIST
SET @MoveSQL = ''
OPEN curFileList
FETCH NEXT FROM curFileList into
@TEMP
WHILE @@Fetch_Status
= 0
BEGIN
SET @MoveSQL =
@MoveSQL + @TEMP +
', '
FETCH NEXT FROM curFileList into
@TEMP
END
CLOSE curFileList
DEALLOCATE curFileList
SET @ExecSQL = 'RESTORE DATABASE [' +
@SubDB + ']'
SET @ExecSQL =
@ExecSQL + ' FROM DISK
= ''' + @BackupLocation + ''''
SET @ExecSQL =
@ExecSQL + ' WITH FILE
= 1,'
SET @ExecSQL =
@ExecSQL + @MoveSQL
SET @ExecSQL =
@ExecSQL + ' NOREWIND,
'
SET @ExecSQL =
@ExecSQL + ' NOUNLOAD,
STATS = 5 '
SET @ExecSQL =
@ExecSQL + @REPLACE
RAISERROR (@ExecSQL, 0, 1) WITH NOWAIT
EXEC sp_executesql @ExecSQL
SET @msg = N'ENDING ' + @FileName
RAISERROR (@msg, 0, 1) WITH NOWAIT
0 comments:
Post a Comment