Need of files and filegroup

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  February 11, 2010 at 4:50 am

  #156666

  HI

  What is the Need of Files and filegroup backups

  I created a database with

  USE master;

  GO

  IF DB_ID (N'Sales') IS NOT NULL

  DROP DATABASE Sales;

  GO

  -- Get the SQL Server data path

  DECLARE @data_path nvarchar(256);

  SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

  FROM master.sys.master_files

  WHERE database_id = 1 AND file_id = 1);

  -- execute the CREATE DATABASE statement

  EXECUTE ('CREATE DATABASE Sales

  ON PRIMARY

  ( NAME = SPri1_dat,

  FILENAME = '''+ @data_path + 'SPri1dat.mdf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 15% ),

  ( NAME = SPri2_dat,

  FILENAME = '''+ @data_path + 'SPri2dt.ndf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 15% ),

  FILEGROUP SalesGroup1

  ( NAME = SGrp1Fi1_dat,

  FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

  ( NAME = SGrp1Fi2_dat,

  FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

  FILEGROUP SalesGroup2

  ( NAME = SGrp2Fi1_dat,

  FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 ),

  ( NAME = SGrp2Fi2_dat,

  FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 )

  LOG ON

  ( NAME = Sales_log,

  FILENAME = '''+ @data_path + 'salelog.ldf'',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB )'

  );

  GO

  Created 3 tables

  CREATE TABLE T11 (Col1 INT )

  CREATE TABLE T22 (Col1 INT ) ON SalesGroup1

  CREATE TABLE T33 (Col1 INT ) ON SalesGroup2

  populated datas

  DECLARE @I INT

  SET @I=1

  WHILE @I<=10000 BEGIN

  INSERT INTO T11

  Select @I

  SET @I=@I+1

  END

  INSERT INTO T22

  Select * from T11

  INSERT INTO T33

  Select * from T22

  checked --(Partial Database Restore Operations )

  http://support.microsoft.com/kb/281122

  i did some

  BACKUP DATABASE Sales

  FILE = 'SGrp1Fi1_dat',

  FILE = 'SGrp2Fi2_dat'

  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesGroup1.bck'

  GO

  --Back up the files in SalesGroup1.

  BACKUP DATABASE Sales

  FILEGROUP = 'SalesGroup1',

  FILEGROUP = 'SalesGroup2'

  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesFiles.bck'

  GO

  i deleted T33 table

  I want to get the data of T33 how to get

  RESTORE DATABASE Sales

  FILE = 'SGrp1Fi1_dat',

  FILE = 'SGrp2Fi2_dat'

  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesGroup1.bck'

  WITH NORECOVERY

  Go

  RESTORE DATABASE Sales

  FILEGROUP = 'SalesGroup1',

  FILEGROUP = 'SalesGroup2'

  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesFiles.bck'

  WITH NORECOVERY

  GO

  It gives message that filegroup has been restored successfully but when i run any query to fetch data from that filegroup, it gives error that filegroup is not not online.

  I am taking only filegroup backup not taking any log backup.

  If anyone have any document related to filegroup backup/restore then share it

  If i have log backup then what is the Need of files and filegroup

  Thanks

  Parthi

  Thanks
  Parthi

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  February 12, 2010 at 3:04 am

  #1117932

  parthi-1705 (2/11/2010)

  ---

  HI

  What is the Need of Files and filegroup backups

  I created a database with

  .......

  checked --(Partial Database Restore Operations )

  http://support.microsoft.com/kb/281122

  ......

  RESTORE DATABASE Sales

  FILEGROUP = 'SalesGroup1',

  FILEGROUP = 'SalesGroup2'

  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesFiles.bck'

  WITH NORECOVERY

  GO

  It gives message that filegroup has been restored successfully but when i run any query to fetch data from that filegroup, it gives error that filegroup is not not online.

  I am taking only filegroup backup not taking any log backup.

  If anyone have any document related to filegroup backup/restore then share it

  If i have log backup then what is the Need of files and filegroup

  Thanks

  Parthi

  File group is not online because you have used WITH NORECOVERY option in the last restore.

  Remember that, online restore is supported only in enterprise edition.

  Read this document http://msdn.microsoft.com/en-us/library/ms190394(SQL.90).aspx for more information.