| Create one or more filegroups for all
the data part of your database. Make sure you put all tables, indexes and
text data on this new filegroup. You can use ALTER DATABASE to make this
new filegroup the default. That will make things easier.
Here is an example script for doing this:
USE master
GO
/* Make Filegroup DATA the default filegroup in database Example:
*/
ALTER DATABASE example
MODIFY FILEGROUP data DEFAULT
GO
USE example
GO
-- Check whether status has been update
-- d in sysfilegroups:
SELECT groupname FROM sysfilegroups WHERE status = 16
|
When you create new objects in Enterprise Manager,
make sure you specify the correct filegroup. Enterprise Manager tends to
put all objects on the PRIMARY filegroup even if you specified a different
default filegroup.
Check the database for objects that have by
accident been placed on the default filegroup. The following script will
help you with this task:
PRINT 'The following objects are not set up for "POINT OF
FAILURE" recovery:'
SELECT 'Warning' =
CASE
WHEN
i.indid < 2 THEN 'Table ' + o.name +
' is located on the primary filegroup'
WHEN
i.indid = 255 THEN 'Text data of table '
+ o.name +
' is located on the primary filegroup'
ELSE 'Index
' + i.name + ' (Table ' + o.name
+ ') is located on the primary filegroup'
END,
o.name as Tablename,
i.name as Indexname,
s.groupname,
i.indid FROM
sysfilegroups s
INNER JOIN
sysindexes i
ON s.groupid = i.groupid
INNER JOIN sysobjects o
ON i.id = o.id
WHERE o.type ='U'
AND s.groupname =
'PRIMARY'
|
If this scripts issues warnings, you should
recreate the objects on a different filegroup (Enterprise Manager can here
be helpful).
Now that you have got everything settled, plan
your backup strategy. I suggest to include the following items.
- Full backup (to start with)
- Filegroup backups of PRIMARY and your own
filegroups.
- Regular transaction log backups
In case of a failure you can then recover up to
the point of the failure:
Scenario 1: PRIMARY filegroup is damaged
Restore the filegroup backup and any transaction
log backups you have
Could look like this:
-- First restore system tables
from backup:
RESTORE DATABASE Example
FILE = 'Example_SYS',
FILEGROUP = 'PRIMARY'
FROM
DISK='G:\mssql7\backup\primary.bak'
WITH NORECOVERY,
REPLACE
-- Then restore the log backup.
RESTORE LOG Example FROM DISK='G:\mssql7\backup\log.bak'
|
This should bring your database back to a
consistent state.
Scenario 2: Your data filegroup(s) become
corrupt
Save the active part of the transaction log,
specifying the NO_TRUNCATE option.
Example:
BACKUP LOG Example
TO DISK='G:\mssql7\backup\lastlog.bak'
WITH NO_TRUNCATE |
Then restore the database, logs and the final log
that you have just saved.
|
