Restore user-defined filegroups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Restore user-defined filegroups

Author

Topic

lipkee85
Starting Member

7 Posts

Posted - 2009-11-26 : 02:08:50

Hello Everyone,

Here is my sql from backup filegroup to restore filegroup.

--set database to full recovery
USE master;
ALTER DATABASE NetTrustOSK SET RECOVERY FULL;

--create new filegroup and file
USE master;
GO
ALTER DATABASE NetTrustOSK
ADD FILEGROUP Secondary;
GO

ALTER DATABASE NetTrustOSK
ADD FILE
(
NAME = Secondary2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Secondary.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Secondary;
GO

ALTER DATABASE NetTrustOSK
MODIFY FILEGROUP Secondary DEFAULT;
GO

--create table in secondary filegroup
USE NetTrustOSK;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON Secondary;
GO

USE NetTrustOSK;
insert into MyTable (cola,colb) values ('1','A')
insert into MyTable (cola,colb) values ('2','B')
insert into MyTable (cola,colb) values ('3','C')
insert into MyTable (cola,colb) values ('4','D')
insert into MyTable (cola,colb) values ('5','E')

--backup secondary filegroup in database
USE master
GO
BACKUP DATABASE NetTrustOSK TO
DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH INIT;
GO

--USE NetTrustOSK
--select * from usergroup

USE NetTrustOSK
delete from MyTable where colb='E'

BACKUP LOG NetTrustOSK TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH NOINIT;

--backup tail of the log
BACKUP LOG NetTrustOSK TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH NOINIT, NORECOVERY;

--restore secondary filegroup in database
RESTORE DATABASE NetTrustOSK FILEGROUP='Secondary'
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH PARTIAL,NORECOVERY;
GO
RESTORE LOG NetTrustOSK
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH FILE=1,NORECOVERY;
GO
RESTORE LOG NetTrustOSK
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH FILE=2,NORECOVERY;
GO
RESTORE LOG NetTrustOSK
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Secondary.bak'
WITH FILE=3,RECOVERY;

Now the problem is when I do a restore, the data inside the MyTable table is still the same. Initially I have 5 records in MyTable and I deleted one record. After I restore, I still get 4 records and not 5 records. What exactly have I do wrong there? Any reply would be appreciated.

jennyya
Starting Member

1 Post

Posted - 2009-12-13 : 12:46:46

Hi lipkee85, I prefer the combination of backups and recovery services, have you ever heard about the service of repair Microsoft SQL Server 8.0, provided by Recovery Toolbox for SQL Server? it is here: http://www.recoverytoolbox.com/microsoft_sql_server_2005_repair_database.html Hope it helps

Jenny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources