You can see the restore database command for SQL Server FILESTREAM enabled database. It is also similar to a standard database restore command. The only difference is that you can see a FILESTREAM file move command also in the script.Įxecute this restore database command. We can see the output of the restore command also processed FILESTREAM file ‘DemoFiles’ similar to the backup command output. Processed 432 pages for database ‘FileStreamDemoDB_Restore’, file ‘FileStreamDemoDB’ on file 9. Processed 2 pages for database ‘FileStreamDemoDB_Restore’, file ‘FileStreamDemoDB_log’ on file 9. Processed 2041 pages for database ‘FileStreamDemoDB_Restore’, file ‘DemoFiles’ on file 9. RESTORE DATABASE successfully processed 2475 pages in 1.859 seconds (10.399 MB/sec). Now, let us go to the FILESTREAM container for the newly restored database. Below we can see both the databases contain the exact similar files and file size is similar to each other. We cannot restore the SQL Server FILESTREAM container in the same location as of source database. If we try to do so, we get the below error message.įile ‘DemoFiles’ cannot be restored to ‘C:\sqlshack\FileStream’. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. RESTORE DATABASE is terminating abnormally.
In the last article, we took the database backup while the file in the FILESTREAM container opened in another program. Below was the error in the backup of the database. The backup operation has detected an unexpected file in a FILESTREAM container. Now let us restore the database backup that completed with the above warning message. If we restore the database, it works fine. Now, let us go to the container of the newly restored database, and we can see the temporary file there as well. However, there is no issue with the database availability and work due to this hidden file. Now let us look at the internal of the FILESTREAM database restore using the extended event session. This event session we configured in my last article. You can follow the steps there and monitor it during the database restore.įirst, it takes the locks on the database (if we are doing database overwrite) and opens the backup set. Once it reads the backup set, it plans for the database restore.
Dbschema load from .dbs file Offline#
In below screenshot, you can notice that it begins the OFFLINE database restore.
We are restoring the SQL Server FILESTREAM enabled database backup. Therefore it prepares the FILESTREAM containers into the path we specified in database restore command. In the backup stream, you can see that it starts processing the FILESTREAM data.
0 kommentar(er)
