Hot answers tagged

9

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible. The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily ...


5

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault: Copy the current .mdf and .ldf files. List the filegroups of ...


5

this isn't exactly how filestream and filetables work, you can't specify the name of an existing share as SQL server will try and create the share that you specify in SQL Server Configuration Manager. This is because the share is not mapped directly to a folder on the filesystem like a normal share, but an NTFS data container that lives in the filestream ...


5

60.000 files and 50 GB should be absolutely no problem. You might want to read "Special Consideration for Large Environments". A challenge could be to come up with a recovery plan.


4

You could isolate the table with a FILESTREAM in a separate database and create a reference to it in the PRODUCTION database using a view. This would allow you to do what you want without resorting to hacks.


4

To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once ...


3

Actually you can put as many files as you want in a file group. Basically file group feature included in sql server so that you can archive your organization data properly and can query the data efficiently. Its also depend on the hard disk space on server and configuration that which raid model you use for data storage.


3

If your files are going to be >1MB, then FILESTREAM is your best option. You can look at BOL documentation here, which clearly describes and advises you when is the best situation to use it.


2

If you have applied the latest update for SQL Server 2012 - Cumulative Update 2 for Service Pack 3 as at the time of writing - and the issue persists, you should open a support ticket, or report the issue on Connect. The underlying issue shares some common features with an existing report: String summary - slow statistics creation on VARBINARY(MAX) column ...


2

I do not believe your extracted quote is relevant to your problem. Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get ...


2

Really not sure what else aside from PBM might be set up to try so forcibly to disable xp_cmdshell, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you ...


2

The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!). ...


2

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


2

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database. Below are a few good links on the architecture: Paul Randal White Paper - FILESTREAM Storage Paul Randal Blog Post - FILESTREAM directory structure As quoted from Paul's blog post above: The FILESTREAM file names are actually the log-sequence number ...


2

It seems like insufficient info to tell something definitely. For example, are you deleting through Transact-SQL or through Win32API, etc. The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery. Have you deleted with CHECKPOINT delete from ...


2

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this define "," as the physical record separator join ...


2

I would suggest double checking all the requirements for enabling FILESTREAM as per: How to: Enable FILESTREAM


2

Unfortunately no, not in SQL Server 2008. According to FILESTREAM Overview - multiple containers can be added to a single FILESTREAM Filegroup but only as of SQL 2012.


1

FindNextFile is a Windows API call, so what's happening is the backup process is enumerating all the files in the FILESTREAM folder and backing them up. The error message indicates that the operating system cannot read the file system; this indicates, at least to me, that the file/folder structure on disk is corrupt. There would be a different error returned ...


1

In short you can't. You would have to restore the entire database to another machine, then copy the needed file from the restored machine and put it into place on the production server. Single object restores and single row restores (which is what restoring a single file effectively is) have never been supported via the native tools.


1

I believe the mystery is cleared up. The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access. I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.


1

I found a solution. Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9' declare @path varchar(MAX); set @path = '\folder'; declare @parent hierarchyid; set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path)); select @parent ...


1

You need to have "Allow remote clients access to FILESTREAM data" selected as shown below : Also, you need to leave the windows share as a Globalroot path and manage permissions through table properties, not Windows share properties. Refer to : Prerequisites for FileTable


1

Replication is a data distribution technology, it was not meant for HA. That said, I would use a cluster. Standard edition supports 2-nodes clusters.


1

You have manually create a file in a FS container. Stop doing so. A FS container is defined here: FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM filegroup is a special filegroup that contains file system directories instead of the files themselves. These file system directories are called data containers. Data containers are ...


1

You can't move the column without copying the data. There's one rather exotic idea to pull this of: You move all other columns and leave the filestream column in place. Depending on the number of rows and schema changes required this can be a lot faster (or slower). You might have to completely gut some tables and create complicated scripts. I only would do ...


1

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption. This message also says ...


1

Indeed, that was the answer. I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.


1

To completely remove FILESTREAM features from a database, you need to perform the following steps. Delete all FILESTREAM columns from all tables Disassociate tables from FILESTREAM filegroups Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them) Remove all FILESTREAM filegroup (there may be more than one of them) ...


1

Assuming you are doing native backups to disk, and then Veritas backs up to tape: Do your onsite backups to disk. Setup blob storage on Azure (or S3 with Amazon) and then script copying that file to that storage. You would then have Veritas backup that file as well from the local disk to tape. If you happen to be on SQL Server 2012 or higher you can backup ...



Only top voted, non community-wiki answers of a minimum length are eligible