Using Filegroups To Mimic Table Restores
Sep 28, 2000
Recently, we had some tables inadvertantly dropped from a 20+ GB data warehouse in the middle of one of our load cycles. In order to avoid restarting the load, we had to make restore a copy of the database from a full database backup and then manually move in the dropped tables using DTS.
This ended up being a painful process, and I am looking to avoid it by restructuring the database into filegroups so I can mimic the table restore function from 6.5.
I am looking for pros, cons, gotchas, and best practices regarding splitting a MSSQL 7 database into multiple filegroups. We would be including 1 or more tables and all of thier indexes in each filegroup. Specifically, I have the following questions:
1. All the literature I read mentions the need for doing transaction log backups and restores if I utilize filegroup backups. Is this really necessary? We have turned off transaction logging for performance reasons, and since we completely control the addition of new data into the db.
2. Are there implications for disk read performance involved with filegroups? Our data resides on an EMC disk array. I feel that we'll most likely have between 20 and 30 filegroups.
3. If we decide to move a table (and its indexes) between filegroups, is the process as simple a dropping the clustered index and recreating it on the new filegroup (then dropping and recreating the n-c indexes)?
Any information y'all can post would be greatly appreciated.
View 1 Replies
ADVERTISEMENT
Jun 18, 2002
I want to change the filegroup of a table. While we can do this in enterprise mgr, I would like to accomplish this in transact sql. We have a new configuration where the filegroups are located on different disks and to take advantage of that, I want to move the heavily accessed tables onto different groups. Example: table emp needs to be moved from filegroup Primary to file group Secondary.
please help...
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Oct 6, 2006
Hi
I am using SQL Server 2005 Developer Edition.
I want a list of the following things from the database: -
Table Name , FileGroup Table resides on
Table Name, Index Name, FileGroup index resides on
To put it simply, consider the following example:-
Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.
List1
-------
XYZ F1
List2
---------
XYZ PK1 F2
Please do not tell me of sp_help <table> option
Regards
Imtiaz
View 1 Replies
View Related
Aug 13, 2007
I have approximately 400 - 600 tables that need to have their filegroups changed on a new SQL 2005 installation. In 2000, I could go the table properties in Enterprise Manager and change the filegroup, but on 2005 Management Studio, I can view (but not change) the table properties.
I realize that I can create the table on the other filegroup using a new name, move my constraints and indexes to this new table, copy the data over, drop the original table, then rename the new table to the name my application is expecting. But that could take me weeks (especially with such a time-consuming process)! I don't mind the process being so slow, but I do mind it being labor intensive.
Does anyone have an idea how I can automate this process (or at least make it as easy as it was with Enterprise Manager?)
Paul Teal
View 3 Replies
View Related
Jul 20, 2005
Hi,I want to find out in my SP the filegroup associated with a particulartable? I was not able to establish the relationship between sysobjectsand sysfilegroups table. Is there any other way to find filegroup of atable in T_SQL?Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table'xyz' is created on filegroup 'FG1' - Is there any way to find out thedatafile's names on which table xyz's data is scattered?Thanks in Advance,Subodh
View 1 Replies
View Related
Dec 20, 2005
Hello,
On an existing Merge Pull Subscription (Subscriber: SQL Express) via websync how can we start replication via vb.net? We can use the cmd line replmerge.exe and all works fine however we would like this to be cleaner as it's going to be on demand sync. I saw in BOL about the RMO and how to create pubs/subs but nothing telling me how to actually start the replication process. Is there a sample of how to start the replication process in vb.net for websync? Thanks in advance.
John
View 17 Replies
View Related
Oct 19, 2006
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
View 9 Replies
View Related
Jul 24, 2007
I'm trying to mimic the rowcount transform's custom property called VariableName in a custom pipeline transform, with the same drop-down and list of variables in the default property grid as that of "RowCount".
Should a UITypeEditor or a TypeConverter be used for such a custom property? More importantly, in either case, how do you get from the UITypeEditor or TypeConverter to the TaskHost which contains the Variables to enumerate?
I'm not that familiar with Windows Forms, but it appears in both cases (editor or type converter), an ITypeDescriptorContext parameter named "context" is passed, but I'm certain how to get from A to B, that is, from the context variable to the TaskHost.
P.S. There's no need to add a new variable, just select from those that already exist.
View 5 Replies
View Related
May 31, 2006
Hi Guys,
The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.
Thanks!
Kervy
View 8 Replies
View Related
Oct 5, 2000
I have a database 30 GB in size. I need to copy this entire database to
laptop. I want to use backup and restore database on this laptop.
I need to know how I can accomplish this wihtout copying the backup file
to the destination server. Is there a way you can restore from a file over the
network from a different server?
I tried everything,with the security setup but was not able to do this.
Any recomendations will be valuable.
View 2 Replies
View Related
Jan 18, 2006
I took some database backups from a client site to do some development.
There were 20G, 16G and 200MB
i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.
went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?
View 4 Replies
View Related
Aug 30, 2006
Hi all,
A few weeks back I had a problem with setting up Maintenance Plans on my SQL Server, the only fix in the end was re-install the SQL along with SP1. Since then backing up or restoring our developers database takes around 15-20 minutes whereas before it would take 1-2 minutes.
The server doesn't seem to be the problem as I have restored it to a 2nd server and get exactly the same problem there. Other databases on the same server of around the same size backup as expected.
The database is approximately 150MB's with the log file being 800MB's.
The server in question is a Dual Core Opteron 2.2Ghz with 4GB's of RAM, running Windows 2003 x64, fully patched and up to date. The SQL version is 2005 Standard Edition x64 also including SP1.
Plenty of disk space and no CPU activity even at the time of backups taking place.
Any help is much appreciated.
View 7 Replies
View Related
Jun 3, 2007
With Migrating from SQL 2000 to SQL 2005 I realise that there are a few options available.
A SQL 2000 backup is able to be restored into 2005 and is workable however there are some issues with internals that have changed. I have identified some DDL which would need to be modified to work correctly.
If the Backup is restored and left in a recoverable state does 2005 allow for 2000 TLog backups to be restored as well ? I am assuming that as you can restore a full db backup that this is allowed.
I am asking the question as i have a very short and fixed deadline of moving from 2000 to 2005. A new datacentre is being moved to and the old DC is being turned off. all new hardware is being placed in the new DC so i need to move from the old to the new with very little downtime to the DB or app.
My approach is as follows
1. Full DB Backup (2000) copied across to 2005 server and loaded being left in a recoverable state.
2. Tlog backups copied across on scheduled basis and loaded to a point in time.
3. Test new 2005 DB with app/web frontend
4. Bring both in sync again and then perform 1 final Tlog backup, copy and load bringing app online again with very little downtime.
Has anybody tried this approach ? does anybody see any issues with my approach ? all comments welcome on this.
Thx
View 4 Replies
View Related
Jan 16, 2008
Data encryption and database restores.
1. When using data encryption on a 2 node active/passive cluster does it make any difference which server the sqlserver is running on when encrypting or dcrypting data?
2. When restring a database to a different server with encrypted data is the data decrytable or is it lost?
View 1 Replies
View Related
Sep 21, 2007
Hello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
View 1 Replies
View Related
Aug 21, 2005
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
View 2 Replies
View Related
Jan 25, 2006
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:kupMODTRNMaster.bak'
with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf',
move 'MM' to 'f:mssqldataM1_MLSH_1.mdf',
move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf',
move 'DM' to 'f:mssqldataM1_MLSH_3.mdf',
move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf',
move 'ED' to 'f:mssqldataM1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:
View 5 Replies
View Related
Nov 25, 2014
On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.
When performing a PIT restore in SSMS the restore file list lists the most recent copy-only backup as the full backup to use, not the most recent plan full backup. I noticed that using SSMS 2008 to start a PIT restore on the 2014 box does not have this problem, and lists the correct restore file sequence (ignores the copy-only backups).
View 9 Replies
View Related
Apr 4, 2015
I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.
here are some ideas I have but have not yet tested:
Create a maintenance plan with each 3 jobs:
--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3
I would like to use seperate jobs as to get a report on the duration and status of each job
Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.
View 8 Replies
View Related
May 25, 2000
I was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would
follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is
to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?
There seems to be a system index on the table that was not created by me.
View 1 Replies
View Related
Jan 23, 2001
I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.
How can I find out which table belongs to which file group.
Thank You,
John
View 1 Replies
View Related
Oct 8, 1999
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....
On separate disks ?
Thanks in advance..
Any comments welcome ! Peter
View 1 Replies
View Related
Oct 8, 1999
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance..
Any comments welcome ! Peter
View 1 Replies
View Related
Feb 8, 2006
Hi,
I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?
Thanks,
Mario
View 4 Replies
View Related
Sep 22, 2005
i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??
View 1 Replies
View Related
Oct 8, 2007
hi all,
What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?
View 1 Replies
View Related
Jul 21, 2000
Is there a way to list all the tables in each of the filegroup
in a database. I have a database with 7 filegroups and would like
to have a list of tables in each filegroup
Sp_helpfilegroup gives you list of filegroups and the names of files
View 1 Replies
View Related
Jan 23, 2001
Is there any way in which I can move an objects/ data to another
filegroup using transact sql?
I have created a table such that the data is on a secondary filegroup.
But the table has image and text data and this data is going to
the PRIMARY filegroup.
I tried chnging the filegroup for the text data but this property but does not change(I did it through EM).
As a result, the disk which contains the primary filegroup is getting filled up.
I have configured my database such that the primary file group is on one disk and the secondary on another and the log on the same disk as the primary filegroup.
I have given lesser space to the primary file group and more space to the secondary filegroup.
help on this is greatly appreciated because my system is rapidly growing with respect to data.
thanks and best regards
Sush.
View 1 Replies
View Related
Sep 10, 1999
I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.
I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.
Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.
View 1 Replies
View Related