File Table Database Backup and Restore

There is not much change in restore process, the additional process is to backup and restore file stream file and data.

Enable dbcc traceon(3004, 3605, -1) to write backup and restore background process in error log.

ft_4

ft_5

ft_6

RESTORE DATABASE [SQL2016DB] FROM DISK='H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK' WITH REPLACE,RECOVERY,STATS=5

Backup Background Process

2015-10-21 00:33:42.940	spid54	Backup(SQL2016DB): BACKUP DATABASE started
2015-10-21 00:33:42.940	spid54	Backup(SQL2016DB): Opening the database with S lock
2015-10-21 00:33:42.940	spid54	Backup(SQL2016DB): Acquiring bulk-op lock on the database
2015-10-21 00:33:42.940	spid54	Backup(SQL2016DB): Synchronizing with other operations on the database is complete
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): Opening the backup media set
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): The backup media set is open
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): Preparing the media set for writing
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): The media set is ready for backup
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): Effective options: Checksum=0, Compression=0, Encryption=0, BufferCount=7, MaxTransferSize=1024 KB
2015-10-21 00:33:42.950	spid54	Backup(SQL2016DB): Clearing differential bitmaps
2015-10-21 00:33:42.960	spid54	Backup(SQL2016DB): Differential bitmaps are cleared
2015-10-21 00:33:42.960	spid54	Backup(SQL2016DB): Writing a checkpoint
2015-10-21 00:33:42.960	spid54	Backup(SQL2016DB): Checkpoint is complete (elapsed = 4 ms)
2015-10-21 00:33:42.960	spid54	Backup(SQL2016DB): Start LSN: 5197:248:37, SERepl LSN: 0:0:0
2015-10-21 00:33:42.960	spid54	Backup(SQL2016DB): Scanning allocation bitmaps
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Scanning allocation bitmaps is complete
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Estimated total size = 73089024 bytes (data size = 73072640 bytes, log size = 16384 bytes)
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Work estimation is complete
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Last LSN: 5197:280:1
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Writing the leading metadata
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Copying data files
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Number of data file readers = 1
2015-10-21 00:33:42.970	spid54	Backup(SQL2016DB): Reading the data file C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB.mdf
2015-10-21 00:33:52.350	spid54	Backup(SQL2016DB): Completed reading the data file C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB.mdf
2015-10-21 00:33:54.830	spid54	Backup(SQL2016DB): Last LSN: 5197:280:1
2015-10-21 00:33:54.830	spid54	Backup(SQL2016DB): Copying data files is complete
2015-10-21 00:33:54.830	spid54	Backup(SQL2016DB): Copying filestream data
2015-10-21 00:35:41.440	spid54	Backup(SQL2016DB): Copying filestream data is complete
2015-10-21 00:35:41.440	spid54	Backup(SQL2016DB): Copying transaction log
2015-10-21 00:35:41.550	spid54	Backup(SQL2016DB): Copying transaction log is complete
2015-10-21 00:35:41.550	spid54	Backup(SQL2016DB): Writing the trailing metadata
2015-10-21 00:35:41.550	spid54	Backup(SQL2016DB): Writing the end of backup set
2015-10-21 00:35:41.770	Backup	Database backed up. Database: SQL2016DB, creation date(time): 2015/06/06(16:29:32), pages dumped: 8976, first LSN: 5197:248:37, last LSN: 5197:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK'}). This is an informational message only. No user action is required.
2015-10-21 00:35:41.780	spid54	Backup(SQL2016DB): Writing history records
2015-10-21 00:35:41.830	spid54	Backup(SQL2016DB): Writing history records is complete (elapsed = 50 ms)
2015-10-21 00:35:41.830	Backup	BACKUP DATABASE successfully processed 8762 pages in 118.753 seconds (0.576 MB/sec).
2015-10-21 00:35:41.830	spid54	Backup(SQL2016DB): BACKUP DATABASE finished

Restore Backup

RESTORE FILELISTONLY FROM DISK='H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK'

Restore Background Process

2015-10-21 00:38:29.840	spid54	Restore(SQL2016DB): Acquiring U lock on the database
2015-10-21 00:38:29.840	spid54	Restore(SQL2016DB): RESTORE DATABASE started
2015-10-21 00:38:29.840	spid54	Restore(SQL2016DB): Acquiring bulk-op lock on the database
2015-10-21 00:38:29.840	spid54	Restore(SQL2016DB): Opening the backup set
2015-10-21 00:38:29.840	spid54	Restore(SQL2016DB): Processing the leading metadata
2015-10-21 00:38:29.850	spid54	Restore(SQL2016DB): Planning begins
2015-10-21 00:38:29.860	spid54	Restore(SQL2016DB): Halting Fulltext crawls
2015-10-21 00:38:29.860	spid54	Restore(SQL2016DB): Acquiring X lock on the database
2015-10-21 00:38:29.860	spid54	Restore(SQL2016DB): Acquired X lock on the database
2015-10-21 00:38:29.870	spid54	Restore(SQL2016DB): Effective options: Checksum=0, Compression=0, Encryption=0, BufferCount=6, MaxTransferSize=1024 KB
2015-10-21 00:38:29.870	spid54	Restore(SQL2016DB): Planning is complete
2015-10-21 00:38:29.870	spid54	Restore(SQL2016DB): Beginning OFFLINE restore
2015-10-21 00:38:29.940	spid54	Restore(SQL2016DB): Preparing containers
2015-10-21 00:38:31.660	spid54	Restore(SQL2016DB): Containers are ready
2015-10-21 00:38:31.660	spid54	Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB_log.ldf from page 1 to 10736 (0x2000 to 0x53e0000)
2015-10-21 00:38:32.950	spid54	Restore(SQL2016DB): Restoring the backup set
2015-10-21 00:38:32.950	spid54	Restore(SQL2016DB): Estimated total size to transfer = 73089024 bytes
2015-10-21 00:38:32.950	spid54	Restore(SQL2016DB): Transferring data
2015-10-21 00:38:33.650	spid54	Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB_log.ldf (elapsed = 1987 ms)
2015-10-21 00:38:36.560	spid54	Restore(SQL2016DB): 5 percent (4194304/73089024 bytes) processed
2015-10-21 00:38:39.210	spid54	Restore(SQL2016DB): 10 percent (7340032/73089024 bytes) processed
2015-10-21 00:38:42.770	spid54	Restore(SQL2016DB): 15 percent (11534336/73089024 bytes) processed
2015-10-21 00:38:47.450	spid54	Restore(SQL2016DB): 20 percent (14680064/73089024 bytes) processed
2015-10-21 00:38:54.870	spid54	Restore(SQL2016DB): 25 percent (18874368/73089024 bytes) processed
2015-10-21 00:39:00.420	spid54	Restore(SQL2016DB): 30 percent (22020096/73089024 bytes) processed
2015-10-21 00:39:07.870	spid54	Restore(SQL2016DB): 35 percent (26214400/73089024 bytes) processed
2015-10-21 00:39:13.380	spid54	Restore(SQL2016DB): 40 percent (29360128/73089024 bytes) processed
2015-10-21 00:39:19.830	spid54	Restore(SQL2016DB): 45 percent (33554432/73089024 bytes) processed
2015-10-21 00:39:25.350	spid54	Restore(SQL2016DB): 50 percent (36700160/73089024 bytes) processed
2015-10-21 00:39:33.630	spid54	Restore(SQL2016DB): 55 percent (40894464/73089024 bytes) processed
2015-10-21 00:39:39.160	spid54	Restore(SQL2016DB): 60 percent (44040192/73089024 bytes) processed
2015-10-21 00:39:46.770	spid54	Restore(SQL2016DB): 65 percent (48234496/73089024 bytes) processed
2015-10-21 00:39:52.330	spid54	Restore(SQL2016DB): 70 percent (51380224/73089024 bytes) processed
2015-10-21 00:39:59.810	spid54	Restore(SQL2016DB): 76 percent (55574528/73089024 bytes) processed
2015-10-21 00:40:05.450	spid54	Restore(SQL2016DB): 80 percent (58720256/73089024 bytes) processed
2015-10-21 00:40:12.950	spid54	Restore(SQL2016DB): 86 percent (62914560/73089024 bytes) processed
2015-10-21 00:40:17.650	spid54	Restore(SQL2016DB): 90 percent (66060288/73089024 bytes) processed
2015-10-21 00:40:26.120	spid54	Restore(SQL2016DB): 96 percent (70254592/73089024 bytes) processed
2015-10-21 00:40:30.600	spid54	Restore(SQL2016DB): 100 percent (73089024/73089024 bytes) processed
2015-10-21 00:40:30.600	spid54	Restore(SQL2016DB): Waiting for log zeroing to complete
2015-10-21 00:40:30.620	spid54	Restore(SQL2016DB): Log zeroing is complete
2015-10-21 00:40:30.880	spid54	Restore(SQL2016DB): Data transfer is complete
2015-10-21 00:40:31.000	spid54	Restore(SQL2016DB): Backup set is restored
2015-10-21 00:40:31.030	spid54	Starting up database 'SQL2016DB'.
2015-10-21 00:40:31.080	spid54	The database 'SQL2016DB' is marked RESTORING and is in a state that does not allow recovery to be run.
2015-10-21 00:40:31.170	spid54	Restore(SQL2016DB): Offline roll-forward begins
2015-10-21 00:40:31.220	spid54	Restore(SQL2016DB): Starting filestream recovery
2015-10-21 00:40:31.220	spid54	Restore(SQL2016DB): Processing 46 VLF headers
2015-10-21 00:40:31.620	spid54	Restore(SQL2016DB): Processing VLF headers is complete
2015-10-21 00:40:31.780	spid54	Restore(SQL2016DB): First LSN: 5197:248:37, Last LSN: 5197:280:1
2015-10-21 00:40:31.790	spid54	Restore(SQL2016DB): Waiting for filestream recovery to complete
2015-10-21 00:40:31.790	spid54	Restore(SQL2016DB): Filestream recovery is complete
2015-10-21 00:40:31.790	spid54	Restore(SQL2016DB): Stop LSN: 5197:280:1
2015-10-21 00:40:31.850	spid54	Restore(SQL2016DB): Offline roll-forward is complete
2015-10-21 00:40:32.250	spid54	Restore(SQL2016DB): Database fixup is complete
2015-10-21 00:40:32.250	spid54	Restore(SQL2016DB): Transitioning database to ONLINE
2015-10-21 00:40:32.260	spid54	Restore(SQL2016DB): Restarting database for ONLINE
2015-10-21 00:40:32.490	spid54	Starting up database 'SQL2016DB'.
2015-10-21 00:40:32.850	spid54	FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB_log.ldf from 0x1b3000 to 0x1b4000.
2015-10-21 00:40:32.850	spid54	Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB_log.ldf from page 218 to 232 (0x1b4000 to 0x1d0000)
2015-10-21 00:40:32.850	spid54	Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\SQL2016DB_log.ldf (elapsed = 0 ms)
2015-10-21 00:40:32.900	spid54	Restore(SQL2016DB): PostRestoreContainerFixups begins
2015-10-21 00:40:32.990	spid54	Restore(SQL2016DB): PostRestoreContainerFixups is complete
2015-10-21 00:40:33.000	spid54	Restore(SQL2016DB): PostRestoreReplicationFixup begins
2015-10-21 00:40:33.080	spid54	Restore(SQL2016DB): PostRestoreReplicationFixup is complete
2015-10-21 00:40:33.090	spid54	Restore(SQL2016DB): Database is restarted
2015-10-21 00:40:33.090	Backup	Restore is complete on database 'SQL2016DB'.  The database is now available.
2015-10-21 00:40:33.270	spid54	Restore(SQL2016DB): Resuming any halted Fulltext crawls
2015-10-21 00:40:33.420	Backup	Database was restored: Database: SQL2016DB, creation date(time): 2015/06/06(16:29:32), first LSN: 5197:248:37, last LSN: 5197:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK'}). Informational message. No user action required.
2015-10-21 00:40:33.420	spid54	Restore(SQL2016DB): Writing history records
2015-10-21 00:40:33.440	spid54	Restore(SQL2016DB): Writing history records is complete (elapsed = 19 ms)
2015-10-21 00:40:33.440	spid54	Restore(SQL2016DB): MSDB maintenance is complete
2015-10-21 00:40:33.440	Backup	RESTORE DATABASE successfully processed 8763 pages in 117.937 seconds (0.580 MB/sec).
2015-10-21 00:40:33.440	spid54	Restore(SQL2016DB): RESTORE DATABASE finished

Leave a Reply

Your email address will not be published.