Placing on an object (table) on a specific file for file backups

Hi, Is it possible to ensure that a particular object - in this case a table - resides on a particular file in a particular file group. I am able to place a table on a particular filegroup - however I have no idea which file it resides on in that particular file group. for example. I create a database with 2 file groups Primary as the default FileGroup1 as an additional filegroup Filegroup1 has three datafiles file1 file2 file3 so if I run the create table statement below, I have no control over which file the table will reside on. CREATE TABLE TestTable ( PK INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) ) ON Filegroup1; so, if "TestTable" get massively full, will it (the table definition and the data contained in the table) simply reside in one file, or will it spread over the entire 3 files in that filegroup. This would seem to be an important question if file backups were to be done, just to capture the data contained in one table. At present it appears that I have to back up the entire filegroup in order to be sure that I have the full table definition and data of "TestTable " which leads me to the question of whether file backups are a wise idea or not. however my main question is which file will the table definition of "TestTable " reside on (or will it be spread over three files) - similarly which file will the data reside on. any help is appreciated. Thanks in advance. cheers martin.

Martin I did some testing at it shows that these files create database test on primary(name = 'datafile1', filename = 'c:\temp\datafile1'), filegroup user_fg (name = 'datafile2', filename = 'c:\temp\datafile2'), (name = 'datafile3', filename = 'c:\temp\datafile3') log on (name = 'logfile1', filename = 'c:\temp\logfile1') go use test go create table t1(col1 int) create table t3(col1 int identity (1,1)) on user_fg select object_name(i.id) as table_name, groupname as [filegroup] from sysfilegroups s join sysindexes i on i.groupid = s.groupid and i.indid < 2 where i.id in (object_id('t1'), object_id('t2'), object_id('t3')) --datafile 2,3 have size 1024 kb sp_spaceused 't3' insert into t3 default values go 300000 select 8192.0*size /1024.0 from master..sysaltfiles AS db where name like 'datafile%' --datafile 2 3072 kb --datafile 3 2048 kb It DOES spread the data over a datafiles "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in message news:B8ED2ADC-563C-426B-88F1-D6C90D5FE0BC@microsoft.com... > Hi, > > Is it possible to ensure that a particular object - in this case a table - > resides on a particular file in a particular file group. > I am able to place a table on a particular filegroup - however I have no > idea which file it resides on in that particular file group. > > for example. > > I create a database with 2 file groups > > Primary as the default > FileGroup1 as an additional filegroup > > Filegroup1 has three datafiles > file1 > file2 > file3 > > so if I run the create table statement below, I have no control over which > file the table will reside on. > > CREATE TABLE TestTable > ( > PK INT IDENTITY(1,1) PRIMARY KEY, > FirstName VARCHAR(50), > LastName VARCHAR(50) > ) > ON Filegroup1; > > so, if "TestTable" get massively full, will it (the table definition and > the data contained in the table) simply reside in one file, or will it > spread over the entire 3 files in that filegroup. > > This would seem to be an important question if file backups were to be > done, just to capture the data contained in one table. > At present it appears that I have to back up the entire filegroup in order > to be sure that I have the full table definition and data of "TestTable " > > which leads me to the question of whether file backups are a wise idea or > not. > > however my main question is > > which file will the table definition of "TestTable " reside on (or will it > be spread over three files) - similarly which file will the data reside > on. > > any help is appreciated. > > Thanks in advance. > > cheers > > martin. > > >

Hi Uri, Thanks for that. my question now would be, what purpose would simply backing up a file in the file group be used for - except may be if that file became corrupt on the OS. you've just proved that data is spread across all files in the file group, so the entire filegroup needs to be backed up for the table data to be securely backed up. I'd be interested to hear if anybody uses file backups and exactly why and in what scenario it will help them recover. there seems to be no way of placing an object (sp / functions etc) on a particular file in a filegroup. I am struggling to find a reason why they exist :) anyway, thanks for your experiments on this. cheers martin. "Uri Dimant" <urid@iscar.co.il> wrote in message news:e4sTwaOxKHA.2644@TK2MSFTNGP04.phx.gbl... > Martin > I did some testing at it shows that these files > > create database test > > on primary(name = 'datafile1', filename = 'c:\temp\datafile1'), > > filegroup user_fg > > (name = 'datafile2', filename = 'c:\temp\datafile2'), > > (name = 'datafile3', filename = 'c:\temp\datafile3') > > log on > > (name = 'logfile1', filename = 'c:\temp\logfile1') > > go > > use test > > go > > create table t1(col1 int) > > create table t3(col1 int identity (1,1)) on user_fg > > select > > object_name(i.id) as table_name, > > groupname as [filegroup] > > from sysfilegroups s join sysindexes i > > on i.groupid = s.groupid and i.indid < 2 > > where i.id in (object_id('t1'), object_id('t2'), object_id('t3')) > > --datafile 2,3 have size 1024 kb > > sp_spaceused 't3' > > insert into t3 default values > > go 300000 > > > > select 8192.0*size /1024.0 > > from > > master..sysaltfiles AS db > > where name like 'datafile%' > > --datafile 2 3072 kb > > --datafile 3 2048 kb > > It DOES spread the data over a datafiles > > > "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in message > news:B8ED2ADC-563C-426B-88F1-D6C90D5FE0BC@microsoft.com... >> Hi, >> >> Is it possible to ensure that a particular object - in this case a >> table - resides on a particular file in a particular file group. >> I am able to place a table on a particular filegroup - however I have no >> idea which file it resides on in that particular file group. >> >> for example. >> >> I create a database with 2 file groups >> >> Primary as the default >> FileGroup1 as an additional filegroup >> >> Filegroup1 has three datafiles >> file1 >> file2 >> file3 >> >> so if I run the create table statement below, I have no control over >> which file the table will reside on. >> >> CREATE TABLE TestTable >> ( >> PK INT IDENTITY(1,1) PRIMARY KEY, >> FirstName VARCHAR(50), >> LastName VARCHAR(50) >> ) >> ON Filegroup1; >> >> so, if "TestTable" get massively full, will it (the table definition and >> the data contained in the table) simply reside in one file, or will it >> spread over the entire 3 files in that filegroup. >> >> This would seem to be an important question if file backups were to be >> done, just to capture the data contained in one table. >> At present it appears that I have to back up the entire filegroup in >> order to be sure that I have the full table definition and data of >> "TestTable " >> >> which leads me to the question of whether file backups are a wise idea or >> not. >> >> however my main question is >> >> which file will the table definition of "TestTable " reside on (or will >> it be spread over three files) - similarly which file will the data >> reside on. >> >> any help is appreciated. >> >> Thanks in advance. >> >> cheers >> >> martin. >> >> >> > >

Martin > there seems to be no way of placing an object (sp / functions etc) on a > particular file in a filegroup. This is most useful if you use partitioning spead out many filegroups, then BTW you can restore filegroup for specific partition RESTORE DATABASE db FILEGROUP = 'FG1' FROM DISK = 'C:\db.bak' Actually ,nowadays I used to separate LOG file and DATA file as well as creating a user file group and make it as a deafult such as all user objecst are created on the user file group. You will ask, what is the gain? Well, if your data got corrupted /chrashed or whatever else fatll errors and etc you will be able to restore PRIMARY group where all system objects are resided..... I mean not need to reinstall server.... "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in message news:1C57574B-F0E1-44AB-B5FD-6F27B2E8F89F@microsoft.com... > Hi Uri, > > Thanks for that. > > my question now would be, what purpose would simply backing up a file in > the file group be used for - except may be if that file became corrupt on > the OS. > you've just proved that data is spread across all files in the file group, > so the entire filegroup needs to be backed up for the table data to be > securely backed up. > > I'd be interested to hear if anybody uses file backups and exactly why and > in what scenario it will help them recover. > there seems to be no way of placing an object (sp / functions etc) on a > particular file in a filegroup. > I am struggling to find a reason why they exist :) > > anyway, thanks for your experiments on this. > > cheers > > martin. > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:e4sTwaOxKHA.2644@TK2MSFTNGP04.phx.gbl... >> Martin >> I did some testing at it shows that these files >> >> create database test >> >> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'), >> >> filegroup user_fg >> >> (name = 'datafile2', filename = 'c:\temp\datafile2'), >> >> (name = 'datafile3', filename = 'c:\temp\datafile3') >> >> log on >> >> (name = 'logfile1', filename = 'c:\temp\logfile1') >> >> go >> >> use test >> >> go >> >> create table t1(col1 int) >> >> create table t3(col1 int identity (1,1)) on user_fg >> >> select >> >> object_name(i.id) as table_name, >> >> groupname as [filegroup] >> >> from sysfilegroups s join sysindexes i >> >> on i.groupid = s.groupid and i.indid < 2 >> >> where i.id in (object_id('t1'), object_id('t2'), object_id('t3')) >> >> --datafile 2,3 have size 1024 kb >> >> sp_spaceused 't3' >> >> insert into t3 default values >> >> go 300000 >> >> >> >> select 8192.0*size /1024.0 >> >> from >> >> master..sysaltfiles AS db >> >> where name like 'datafile%' >> >> --datafile 2 3072 kb >> >> --datafile 3 2048 kb >> >> It DOES spread the data over a datafiles >> >> >> "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in >> message news:B8ED2ADC-563C-426B-88F1-D6C90D5FE0BC@microsoft.com... >>> Hi, >>> >>> Is it possible to ensure that a particular object - in this case a >>> table - resides on a particular file in a particular file group. >>> I am able to place a table on a particular filegroup - however I have no >>> idea which file it resides on in that particular file group. >>> >>> for example. >>> >>> I create a database with 2 file groups >>> >>> Primary as the default >>> FileGroup1 as an additional filegroup >>> >>> Filegroup1 has three datafiles >>> file1 >>> file2 >>> file3 >>> >>> so if I run the create table statement below, I have no control over >>> which file the table will reside on. >>> >>> CREATE TABLE TestTable >>> ( >>> PK INT IDENTITY(1,1) PRIMARY KEY, >>> FirstName VARCHAR(50), >>> LastName VARCHAR(50) >>> ) >>> ON Filegroup1; >>> >>> so, if "TestTable" get massively full, will it (the table definition and >>> the data contained in the table) simply reside in one file, or will it >>> spread over the entire 3 files in that filegroup. >>> >>> This would seem to be an important question if file backups were to be >>> done, just to capture the data contained in one table. >>> At present it appears that I have to back up the entire filegroup in >>> order to be sure that I have the full table definition and data of >>> "TestTable " >>> >>> which leads me to the question of whether file backups are a wise idea >>> or not. >>> >>> however my main question is >>> >>> which file will the table definition of "TestTable " reside on (or will >>> it be spread over three files) - similarly which file will the data >>> reside on. >>> >>> any help is appreciated. >>> >>> Thanks in advance. >>> >>> cheers >>> >>> martin. >>> >>> >>> >> >>

> I'd be interested to hear if anybody uses file backups and exactly why and > in what scenario it will help them recover. File level backups is not common at all. I've never had need to do it, since a file contains only a fraction of the data in the filegroup and isn't usable for restore (to a new database). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in message news:1C57574B-F0E1-44AB-B5FD-6F27B2E8F89F@microsoft.com... > Hi Uri, > > Thanks for that. > > my question now would be, what purpose would simply backing up a file in > the file group be used for - except may be if that file became corrupt on > the OS. > you've just proved that data is spread across all files in the file group, > so the entire filegroup needs to be backed up for the table data to be > securely backed up. > > I'd be interested to hear if anybody uses file backups and exactly why and > in what scenario it will help them recover. > there seems to be no way of placing an object (sp / functions etc) on a > particular file in a filegroup. > I am struggling to find a reason why they exist :) > > anyway, thanks for your experiments on this. > > cheers > > martin. > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:e4sTwaOxKHA.2644@TK2MSFTNGP04.phx.gbl... >> Martin >> I did some testing at it shows that these files >> >> create database test >> >> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'), >> >> filegroup user_fg >> >> (name = 'datafile2', filename = 'c:\temp\datafile2'), >> >> (name = 'datafile3', filename = 'c:\temp\datafile3') >> >> log on >> >> (name = 'logfile1', filename = 'c:\temp\logfile1') >> >> go >> >> use test >> >> go >> >> create table t1(col1 int) >> >> create table t3(col1 int identity (1,1)) on user_fg >> >> select >> >> object_name(i.id) as table_name, >> >> groupname as [filegroup] >> >> from sysfilegroups s join sysindexes i >> >> on i.groupid = s.groupid and i.indid < 2 >> >> where i.id in (object_id('t1'), object_id('t2'), object_id('t3')) >> >> --datafile 2,3 have size 1024 kb >> >> sp_spaceused 't3' >> >> insert into t3 default values >> >> go 300000 >> >> >> >> select 8192.0*size /1024.0 >> >> from >> >> master..sysaltfiles AS db >> >> where name like 'datafile%' >> >> --datafile 2 3072 kb >> >> --datafile 3 2048 kb >> >> It DOES spread the data over a datafiles >> >> >> "Martin" <martin_remove_and_no_spam@martin_remove_.co.nz> wrote in >> message news:B8ED2ADC-563C-426B-88F1-D6C90D5FE0BC@microsoft.com... >>> Hi, >>> >>> Is it possible to ensure that a particular object - in this case a >>> table - resides on a particular file in a particular file group. >>> I am able to place a table on a particular filegroup - however I have no >>> idea which file it resides on in that particular file group. >>> >>> for example. >>> >>> I create a database with 2 file groups >>> >>> Primary as the default >>> FileGroup1 as an additional filegroup >>> >>> Filegroup1 has three datafiles >>> file1 >>> file2 >>> file3 >>> >>> so if I run the create table statement below, I have no control over >>> which file the table will reside on. >>> >>> CREATE TABLE TestTable >>> ( >>> PK INT IDENTITY(1,1) PRIMARY KEY, >>> FirstName VARCHAR(50), >>> LastName VARCHAR(50) >>> ) >>> ON Filegroup1; >>> >>> so, if "TestTable" get massively full, will it (the table definition and >>> the data contained in the table) simply reside in one file, or will it >>> spread over the entire 3 files in that filegroup. >>> >>> This would seem to be an important question if file backups were to be >>> done, just to capture the data contained in one table. >>> At present it appears that I have to back up the entire filegroup in >>> order to be sure that I have the full table definition and data of >>> "TestTable " >>> >>> which leads me to the question of whether file backups are a wise idea >>> or not. >>> >>> however my main question is >>> >>> which file will the table definition of "TestTable " reside on (or will >>> it be spread over three files) - similarly which file will the data >>> reside on. >>> >>> any help is appreciated. >>> >>> Thanks in advance. >>> >>> cheers >>> >>> martin. >>> >>> >>> >> >>

how to import emails

130

new

generate summary row question

Farmer

Unable to insert record in table

108

Jan

CLR to convert RTF data to image

137

MichaelH

Move a table with LOB data from one filegroup to another.

398

Iain

Can SQL Server be run locally?

139

allenc

update trigger question

Keith

Best way to format errors with raiserror?

191

Andy

SQL Server Profiler Determine Number Times User Account Used

126

Utf

ReturnRecordsBasedOnDateCalculations

110

9/6/2012 11:19:19 AM