I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?
with smiles
santhosh
Hi ,
I am restoring a VLDB from a Standby server which has just .mdf and .ldf to my production server which has .mdf and a file group with a single .ndf
file, i want to restore the databse on to the Secondary file(is this possible) i checked the BOl but could not figure out of what command is to be given
Any hhelp will be greatly appreciated..
Thanks in advance
Jane
SQL 2000I have server1 with two filegroups.I want to backup FileGroup Primary on Server1 and Primary has aphysical file called MyData.mdf.I have server2 with the same database and two filegroups BUT thePhysical names are different. The Primary FileGroup is in physical fileOurData.mdfHow can I get server1->MyData.mdf to Server2->OurData.mdfThanks !
View 1 Replies View RelatedUsing SQL Server 2000 SP3 I'm developing a data warehouse where datawill be archived off to a filegroup, this filegroup backed up and thetables in this filegroup truncated to free up space on the server.So using Enterprise Manager I've copied data to the filegroup tables,backed up the file group, truncated the tables, but when I haverestored the file group the database is stuck in a state of'(loading...)' and I can't get it to resume. I've tried running thecommand RESTORE LOG databaseName WITH RECOVERY but get an error messagethat the database has been restored to different time (sorry I can'tremember exact syntax but I'm not at my work computer)Is this familiar to anyone? Ignoring the filegroups is there a way Ican get the database .MDF back up and running?Thanks
View 2 Replies View RelatedHi!
Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.
One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.
When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.
Unfortunately it is not simple as we wish. I tried those steps:
Perform PRIMARY filegroup backup to file.
Copy this file to Artur's computer by DVD disk.
Perform our database's Trnsaction Log on Artur's computer.
Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager)
Restore backup done in step 3. --- Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.
What is wrong?
Is it possible to move data to Artur's computer this way, without full backup and serie of log backups?
Can I use other alternative solution to move data?
Best regards,
Walter
Hi guys
I have backup my database with "file and filegroup"selected. I have dropped my database by accident. is it possible to restore my data back from "file and filegroup".bak??
Thanks
Our current application which is deployed to numerous client sites usually requires the database to be returned to us to upgrade for the next release. The current process is:
- Perform a full backup at the client site
- Send the backup to us for upgrade
- Perform a full restore process to our local db server
- Modify the database
- Perform a full backup
- Send the backup to the client
- Perform a full restore over the original database.
This has served our purposes so far, however, we are about to introduce functionality that will enable the client to upload files for storing in the database.
To manage this we have a single DBFiles table which due to the potential size of it’s contents we determined that it would be best to store it in it’s own FileGroup. The idea being that then we could backup just the Primary FileGroup for returning to our offices as the DBFiles table would remain untouched.
The proposed process would now be:
- Perform a partial backup of the primary filegroup at the client site
- Perform a partial backup of the DBFiles filegroup at the client site. This only ever stays at the client site and is never restored at our office.
- Send the primary filegroup backup to our office for upgrade
- Perform a partial restore or the primary filegroup to our local db server. This will leave the DBFiles filegroup offline which is OK as we do not need to change it.
- Modify the database
- Perform a partial backup of the primary filegroup
- Send the partial backup to the client
- Perform a partial restore of the primary filegroup over the original database. This will leave the DBFiles filegroup offline.
- Perform a partial restore of the DBFiles filegroup over the original database.
This last step results in the recovery failing due to a reason like “The roll forward start point is now at log sequence number (LSN) 66787000000001800001. Additional roll forward past LSN 66787000000008900001 is required to complete the restore sequence�.
I have tried a number of variations including backing up both the client and local log files for recovery and using copy_only but I have yet to be able to successfully complete the process. Is there a means we can synchronize these 2 backup files without worrying about the logs for this part of the process?
Our requirements are basically to be able to perform a partial backup and exclude the DBFiles table as it is likely to be very large so we can send it back to our offices for upgrade and then return it to the client for restoring back to the original database while maintaining the data in the clients DBFiles filegroup and table.
Any guidance on what we need to change or a better process would be appreciated.
System.Data.SqlClient.SqlError: The backup of the file or filegroup "CUSTBilling" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
I did a file group backup and restore of a database and then when I tried to perform a full backup I am getting the above error. Any thoughts. I indeed happen to see this error http://support.microsoft.com/default.aspx/kb/921106 but not sure if both errors are the same.
Santhosh
http://sqlspy.blogspot.com
Hi,
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:
Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G: emp estdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init
backup log TestDatabase to DISK='g: emp estdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak'
with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery
I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
View 3 Replies View RelatedI am new to DBA activities. I have a database name Sample_DB in SQL Server 2014. Â
This database has below files
LogicalName FileType
FileGroup
Sample_DB_Rows  ROWS Data
PRIMARY
Sample_DB_C1 ROWS Data
Country1
Sample_DB_C2 ROWS Data
Country2
Sample_DB_Docs FILESTREAM Data
FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group
Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup
Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem:
I need to take backup of each file group separately.
I need to restore the backed up file group separately. Is this possible, how to do it.
hello,
ihave atablelike this -
z uniqueidentifier
a varchar(40)
b varchar(40)
c varchar(40)
d varchar(40)
e varchar(40)
f text
g image
I am asked to split this table into 2 filegroups and move text and images on to one and other stuff on one.
I am new to MS-SQL 7.0.
Can anyone suggest me how to go for it.
Thanking you.
i understand that the filegroup for a table can be specified in the CREATE Table statement. just out of curiosity, is there any way to move a table to a different filegroup, that too, if the table contains data.
View 2 Replies View RelatedHi,
There is a table which is about 50GB is size.
I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server.
Is it possible to place this particular table into a searate drive to increase the performance even more?
Basically my question is: is it possible to place tables into separate .ndf files?
Thanks
Hi,
I have created a database with two filegroups called FG_GroupData, FG_GroupHistory.
FG_GroupData is set as default.
FG_GroupData contains two secondary data files i.e. GroupData1.ndf and GroupData2.ndf
I can create a table so that it is stored in FG_GroupHistory. i.e.
CREATE TABLE dbo.OrdersHistory
(
OrderID int NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
UnitPrice money NOT NULL,
OrderQty smallint NOT NULL
)
ON FG_GroupHistory
Questions:
1)
How do I add tables to each .ndf file inside a group i.e. FG_GroupData
For example, how do I add a table to GroupData1.ndf and one to GroupData2.ndf ?
2)
I guess there is no need to place the file name i.e. .ndf inside the select query
Thanks
What is the advantage of having two secondary files inside a filegroup.
For example, I can create a table and place it inside a filegroup which obviously will use the secondary files.
Not sure why some DBAs create just one but otherscreate more than one secondar files in a filegroup.
I have read the booksonline but can not figure out the advantages.
Thanks
While migrating Report services in SQL Server 2005 to 2014, I am trying to restore the Encryption Key in RS Configuration Manager in2014. But I cannot click the 'Restore' button in RS Configuration Manager. So if I should be grant more right to do so or any other action?
View 2 Replies View RelatedIn Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
Can anyone show me in SQL7 how to obtain Available Space on a particular filegroup in a database (not the database or datafile).
I am trying to include this in a script to monitor my database which uses Filegroups and I have every other info that I need (from the sysfiles table) except the available space. Thanks in advance!
hi...
I don't know well this statement...
1. from Transcender
.....
One file for each filegroup is sufficient because creating multiple files for the same filegroup on the same disk does not provide any performance gain
2. from bol
....
Therefore, creating more files per filegroup can help increase performance because a separate thread is used to scan each file in parallel
what is correct ?
thanks !!
Is it possible to back up one filegroup independently from the second when tables
in one group have foreign key relationships with tables in the second group?
I want to remove a filegroup.
ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName
It says:
Server: Msg 5042, Level 16, State 7, Line 1
The filegroup 'MyFileGroupName' cannot be removed because it is not empty.
Question: How can I tell what is on this filegroup.
<Forgive me if it is a simple BOL answer, but I'm having trouble finding it>
Hello,
A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?
Stef
I am managing 120 gb SQL7 database. The 120 GB is in a RAID5 (Hardware RAID). At begining, I think I should create a user defined filegroup besides Primary filegroup, as BOL suggested.
After I read some discussions here, It appears I cannot gain any performance by adding second filegroups. Now I want to drop the filegroup I created. The Question is How?. If I do alter database, move all files from userdefined filegroup, does this mean my userdefined filegroup has gone?
Someone can help me on this!
Thanks a lot.
I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).
Any Info Will help Thanks
Barb
Hi all,
I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go
and it ran fine, with 'DBCC execution completed' message
but when I tried :
alter database pubs
remove file pubs_data_1
go
I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.
Any ideas? Is there a way to check what else sits on that filegroup?
Thanks
We have sql 2000 server at a remote site that log ships to a standby server at the site, and back here to corporate. For performance we are adding a new RAID array to the remote servers along with a new file group for indexes. The server back here is older and we can't add this RAID. For log shipping purposes, what if anything might happen to this server when the filegroup is added. We are going to be using this filegroup for indexes
View 1 Replies View RelatedHi, I am running out of space on one of the local drive, and I would like to move a filegroup from one
drive to another. Does anyone know how to do it?
Are there any general recommendations concerning filegroups? My personal point of view is to place large tables in their own filegroups and group smaller, more static, tables in a single filegroup. Is it also good practice to group small and large index in two separate filegroups or should each large index have their own filegroup? Are there any useful links out there concerning filegroups and configuration?
View 1 Replies View RelatedHi,
Is there a way to rename a Filegroup "FG1" to "FG2"?
For those interested, I'm trying to script a sliding window procedure for my partitioned table and rather than use dynamic SQL I'd like to always refer to the same Filegroup name (ie. "FG1").
Many thanks,
Richard.
WHERE DO I FOUND THE FILEGROUP?
WHEN THIS ERROR OCCURED
Could not allocate space for object '<temporary system object: 431024047390720>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Greetings All, I was hoping that someone might be able to shed somelight on this issue. I am trying to add a FileGroup/Datafile to anexisting SQL database. The code below compile and it runs in .NETStudio, however nothing happens? The form closes, no error messagesare thrown, there is no sign of any type of error taking place. Theend result (assuming the code is correct) is tha there should be a newFileGroup with a datafile in it, here is the code:************************************************** ******************private void AddFileGroup(frmMyForm f){SQLMy.SQLServer MySQLServerName = new SQLMy.SQLServer();SQLMy.Database MyDBDbName = new SQLMy.Database();SQLMy.FileGroup MyDBDataGroup = new SQLMy.FileGroup();SQLMy.DBFile MyDBDataFile = new SQLMy.DBFile();try{MySQLServerName.ConnectmyGetConfigData.OlapServerName*,myGetConfigData.Ol apUserLogin*,myGetConfigData.OlapUserPassw*ord);MyDBDbName.Name =myConnectionData.OlapDatabaseN*ame.ToString().Trim ();MyDBDataGroup.Name = "DBDataGroup";MyDBDbName.FileGroups.Add (MyDBDataGroup);MyDBDataFile.Name = "DBData";MyDBDataFile.PhysicalName =myConnectionData.OlapDBDataPat*h.ToString().Trim() + @"DBData.ndf";MyDBDataFile.Size = 50;MyDBDataFile.MaximumSize = -1;MyDBDataFile.FileGrowth = 5;MyDBDataFile.FileGrowthType = 0;MyDBDataFile.PrimaryFile = false;MyDBDbName.FileGroups.Item("DB*DataGroup").DBFiles.Ad (MyDBDataFile);this.Close();MySQLServerName.DisConnect();}catch (Exception e){MessageBox.Show(e.Message);}finally{MySQLServerName.DisConnect();}}************************************************** ******************Note: myGetConfigData: This is a class that reads in the values for thedb connection from an XML file. This class has been tested extensivelyand the data values are being populated into the variables.I would appreciate any help that anyone might be able to provide to me.Regards, TFD.
View 14 Replies View Related