Restore All SQL Databases from a Backup File

by Joe Havelick 5. July 2011 09:22

As part of a  disaster recovery plan, I recently needed to have a script to restore all the databases, from all the backups (Full and Differential) from a single backup file. Here's how I did it:

 

SET NOCOUNT ON;

DECLARE @DBName nvarchar(256)
DECLARE @BackupFile nvarchar(256) = N'\\SERVER\PATH\FILE.bak' 
DECLARE @FileID int

DECLARE @BackupHeaders TABLE
      (
      BackupName nvarchar(128) NULL,
      BackupDescription  nvarchar(255) NULL,
      BackupType smallint NULL,
      ExpirationDate datetime NULL,
      Compressed tinyint NULL,
      Position smallint NULL,
      DeviceType tinyint NULL,
      UserName nvarchar(128) NULL,
      ServerName nvarchar(128) NULL,
      DatabaseName nvarchar(128) NULL,
      DatabaseVersion int NULL,
      DatabaseCreationDate  datetime NULL,
      BackupSize numeric(20,0) NULL,
      FirstLSN numeric(25,0) NULL,
      LastLSN numeric(25,0) NULL,
      CheckpointLSN  numeric(25,0) NULL,
      DatabaseBackupLSN  numeric(25,0) NULL,
      BackupStartDate  datetime NULL,
      BackupFinishDate  datetime NULL,
      SortOrder smallint NULL,
      CodePage smallint NULL,
      UnicodeLocaleId int NULL,
      UnicodeComparisonStyle int NULL,
      CompatibilityLevel  tinyint NULL,
      SoftwareVendorId int NULL,
      SoftwareVersionMajor int NULL,
      SoftwareVersionMinor int NULL,
      SoftwareVersionBuild int NULL,
      MachineName nvarchar(128) NULL,
      Flags int NULL,
      BindingID uniqueidentifier NULL,
      RecoveryForkID uniqueidentifier NULL,
      Collation nvarchar(128) NULL,
      FamilyGUID uniqueidentifier NULL,
      HasBulkLoggedData bit NULL,
      IsSnapshot bit NULL,
      IsReadOnly bit NULL,
      IsSingleUser bit NULL,
      HasBackupChecksums bit NULL,
      IsDamaged bit NULL,
      BeginsLogChain bit NULL,
      HasIncompleteMetaData bit NULL,
      IsForceOffline bit NULL,
      IsCopyOnly bit NULL,
      FirstRecoveryForkID uniqueidentifier NULL,
      ForkPointLSN decimal(25, 0) NULL,
      RecoveryModel nvarchar(60) NULL,
      DifferentialBaseLSN decimal(25, 0) NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      BackupTypeDescription  nvarchar(60) NULL,
      BackupSetGUID uniqueidentifier NULL,
      CompressedBackupSize binary(8) NULL
);

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      [Type] char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

DECLARE @DataFilePath nvarchar(256) = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';
DECLARE @LogFilePath nvarchar(256)  = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';

--Load backup headers (Backup level Information)
DECLARE @RestoreStatement nvarchar(256);
SET @RestoreStatement = N'RESTORE HEADERONLY FROM DISK=N''' + @BackupFile + '''' ;
INSERT INTO @BackupHeaders
      EXEC(@RestoreStatement);

DECLARE iDB CURSOR FOR SELECT DISTINCT DatabaseName FROM @BackupHeaders 
--For each DB
OPEN iDB
FETCH NEXT FROM iDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 
	IF @DBName IN ('master','msdb','model','tempdb') GOTO NextDB;	

	PRINT '';
	PRINT '';
	PRINT '-- Restoring :' + @DBName ;
	
	DECLARE @DBNewName nvarchar(256) = N'Restored_' + @dbName ;
	PRINT '--- Destination Database Name: ' + @DBNewName;
	
	--Get the file list for this DB
	SET @RestoreStatement = N'RESTORE FILELISTONLY
		 FROM DISK=N''' + @BackupFile + ''' WITH FILE=' + CAST((SELECT TOP 1 Position from @BackupHeaders WHERE DatabaseName = @DBName) as nvarchar(10));;
	DELETE FROM @FileList;
	INSERT INTO @FileList
		EXEC(@RestoreStatement);
	--SELECT * FROM @FileList;
	
	--Get the physical file names
	DECLARE @DataFileName nvarchar(256) = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'D' ), '.mdf', '');
	DECLARE @LogFileName nvarchar(256)  = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'L' ), '.ldf', '');
	PRINT '--- Destination DataFileName: ' + @DBNewName;
	PRINT '--- Destination LogFileName: ' + @DBNewName;

	DECLARE @LogicalDataFileName nvarchar(256) = (SELECT LogicalName FROM @FileList WHERE Type = 'D');
	DECLARE @LogicalLogFileName nvarchar(256)  = (SELECT LogicalName FROM @FileList WHERE Type = 'L');
	PRINT '--- LogicalDestination DataFileName: ' + @DBNewName;
	PRINT '--- LogicalDestination LogFileName: ' + @DBNewName;

	
	DECLARE iBackup CURSOR FOR SELECT Position FROM @BackupHeaders WHERE DatabaseName = @DBName ORDER BY Position ASC 
	--For each backup, in sequence
	OPEN iBackup
	FETCH NEXT FROM iBackup INTO @FileID
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		
		--Restore the file
		PRINT '---- Restoring FileID: ' + CONVERT(nvarchar(5),@FileID)
			
		DECLARE @RestoreSQL nvarchar(max)= N'
		RESTORE DATABASE 
			[' + @dbNewName + ']
			FROM  DISK = N''' + @BackupFile + '''
			WITH  FILE = ' + CONVERT(nvarchar(5),@FileID) + ',
			MOVE N''' + @LogicalDataFileName + ''' TO N''' + @DataFilePath + @DataFileName + ''',
			MOVE N''' + @LogicalLogFileName + ''' TO N''' + @LogFilePath + @LogFileName + ''',
			NORECOVERY,  
			NOUNLOAD,  
			REPLACE'
		--PRINT @RestoreSQL

		EXEC(@RestoreSQL)
		
		PRINT '';
	NextFile:
	FETCH NEXT FROM iBackup INTO @FileID
	END 
	CLOSE iBackup
	DEALLOCATE iBackup
	
	--Recover the database
	PRINT '--- Recovering ' + @DBNewName
	RESTORE Database @DBNewName WITH RECOVERY
	

NextDB:
FETCH NEXT FROM iDB INTO @DBName
END 
CLOSE iDB
DEALLOCATE iDB

 

Note:

  • This was not engineered to handle incrementals, but I believe it should be easily accomidated with a couple small changes (if any)
  • This excludes the system databases via the 'IF @DBName IN...' statement.

Tags:

Tech Tips

Send email notification if databases are not backed up in specific duration.

by Joe Havelick 10. May 2010 14:15

Backup utilities may notify you when a backup fails, but don't provide coverage for you forgetting to set the job, or not having permissions to a certain resource. As part of my protection plan, in addition to regular notifications, I like to know if a database HAS NOT been backed up for any reason within the past two days.  The following script accomplishes this.

Note that this utilizes Database Mail and a DBMail profile called "Default DB Mail Profile". These must be manually configured.  More information can be found here.

 --Declarations 
DECLARE @Threshold DATE = CONVERT(DATE, Getdate()-2) --The cutoff from when we consider backups to be current (2 days before the end of today)
DECLARE @SendEmail BIT = 0
DECLARE @emailbody VARCHAR(2000) = 'The following databases have not been backed since before ' + CONVERT(VARCHAR(32), @Threshold) + ':
<BR/><UL>'
DECLARE
 @DatabaseName VARCHAR(256)
DECLARE
 @LastBackupTaken VARCHAR(256)

DECLARE @UserName VARCHAR(256)
 
DECLARE emailcursor CURSOR FAST_FORWARD FOR
  SELECT t1.name                                                              AS databasename,
         Coalesce(CONVERT(DATE, MAX(t2.backup_finish_date)), 'Not Yet Taken') AS lastbackuptaken,
         Coalesce(CONVERT(VARCHAR(32), MAX(t2.user_name), 101), 'NA')         AS username
  FROM   sys.sysdatabases t1
         LEFT OUTER JOIN msdb.dbo.backupset t2
           ON t2.database_name = t1.name
  WHERE  database_name NOT IN ( 'TempDB' )
  GROUP  BY t1.name
  HAVING Coalesce(CONVERT(VARCHAR(32), MAX(t2.backup_finish_date), 101), 'Not Yet Taken') < @Threshold
  ORDER  BY t1.name

--Open Cursor
OPEN emailcursor
FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
WHILE @@FETCH_STATUS = 0
  BEGIN
      ---
      SELECT @SendEmail = 1
      SELECT @emailbody = @emailbody + '<LI>' + @DatabaseName + ' (' + @LastBackupTaken + ')</LI>'
      FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
      ---    
  END
CLOSE emailcursor
DEALLOCATE emailcursor

SELECT @emailbody = @emailbody + '</UL>'

IF @SendEmail = 1
  BEGIN
      EXEC msdb.dbo.Sp_send_dbmail
        @recipients= 'myemail@address.com',
        @copy_recipients = '',
        @subject = 'SERVER Database Backups',
        @body = @emailbody,
        @body_format = 'HTML',
        @profile_name = 'Default DB Mail Profile';
  END
GO
 

Tags:

Tech Tips

List SharePoint site owners

by Joe Havelick 6. April 2010 11:21

I recently had some issues with site owners on my SharePoint sites.  Specifically, one of the sites didn't have one altogether.  The frontend and command line tools all reported "User could not be found".  This also derived from the error "You cannot delete the owners of a Web site collection" when attempting to remove a user for a dead account. 

If you find yourself in that predicament, the following SQL query can be run on any of the content databases to determine who (if anyone) is currently the owner or secondary owner of each site:

SELECT webs.fullurl,
       webs.title,
       sites.id                 AS siteid,
       sites.ownerid,
       owner.tp_login           AS ownerlogin,
       sites.secondarycontactid AS owner2id,
       owner2.tp_login          AS owner2login
FROM   sites
       INNER JOIN webs
         ON sites.id = webs.siteid
       LEFT OUTER JOIN userinfo AS owner2
         ON sites.secondarycontactid = owner2.tp_id
            AND sites.id = owner2.tp_siteid
       LEFT OUTER JOIN userinfo AS owner
         ON sites.ownerid = owner.tp_id
            AND sites.id = owner.tp_siteid 

Tags: ,

Tech Tips

Determine how my SQL memory is being used

by Joe Havelick 26. March 2010 12:22

Tags:

Tech Tips

Creating a delimited field from a number of rows

by Joe Havelick 24. July 2009 09:55

I found a couple of tricks in solving the problem of generating a single comma separated field from a number of rows. The function looks a little like:

CREATE FUNCTION ReturnAllNAICs

(     @CompanyID int)

RETURNS nvarchar(128)

AS

BEGIN

      DECLARE @NAICS NVARCHAR(128)

 

      SELECT @NAICS = COALESCE(@NAICS + ', ', '') + NAIC

      FROM NAIC_Codes

      WHERE CompanyID=@CompanyID

 

      RETURN @NAICS

END

So how do we generate the list? Simply appending to a string with each row added. But this always gets 10 times more complex because you don’t know whether to add a comma. That’s where my favorite new keyword COALESCE comse into play. You can read the details below, but basically it’s like an IIF statement but it assesses for the value to be null or not null. So, it will add a comma before the next value only if @NAICS is not NULL.

References:
COALESCE (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms190349.aspx
Using COALESCE to Build Comma-Delimited String - http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Tags:

Tech Tips

URL Decoding in SQL

by Joe Havelick 16. July 2009 14:19

CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
AS
BEGIN 
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072
    SET @count = Len(@url
    SET @i =
    SET @urlReturn = '' 
    WHILE (@i <= @count
     BEGIN 
        SET @c = substring(@url, @i, 1
        IF @c LIKE '[!%]' ESCAPE '!' 
         BEGIN 
            SET @cenc = substring(@url, @i + 1, 2
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int
                            END * 16
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int) 
                            END
            SET @urlReturn = @urlReturn + @c 
            SET @i = @i +
         END 
        ELSE 
         BEGIN 
            SET @urlReturn = @urlReturn + @c 
         END 
        SET @i = @i +
     END 
    RETURN @urlReturn
END
GO

References:

http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx

 

Tags:

Tech Tips

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations