Hi, I have a database with about 200Gigs allocated to filegroups and 200Megs data in it presently, and am trying to create a copy of the database on the same server - except I don't have enough diskspace free for another 200Gigs. Is there a way using copy database wizard, or restore that the filegroup allocations can be resized?
Thanks,
Chris
hi
To restore files and filegroups in a different server. First, Do i have to create the database with same name as the old one? or it doesn't matter... Can we use Enterprise Manager to do this restore?
Thanks in advance
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
Hi,
I want to take the backup of a particular filegroup in SQL server 2000 and then restore the same filegroup backup.
Currently I am using the following steps :
1. Creating the database with 2 filegroups. The syntax that I used for it is as follows :
create database test
on primary
(name = test1,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est1.mdf'),
(name = test2,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est2.ndf'),
filegroup group2
(name = test3,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est3.ndf'),
(name = test4,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est4.ndf')
log on
(name = test_log,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est_log.ldf')
2. Now I am creating two tables table1 and table2 using Enterprise Manager. Both the Tables, table1 and table2 are on the "Group2" Filegroup of test database.
3. After creating the tables, I inserted three records in each table.
4. Then I performed the backup using the following commands:
backup log test
to disk = 'e: est.lg1' with init
backup database test
filegroup = 'group2'
to disk = 'e: est_group2.bak'
5. After the backup has been taken, I deleted the two records from both the tables. Now both my tables have one record each.
6. Now I tried restoring the database using following command :
backup log test
to disk = 'e: est.lg3' with init
restore database test
filegroup = 'group2'
from disk = 'e: est_group2.bak'
with norecovery
restore log test
from disk = 'e: est.lg3' with norecovery
restore database test
with recovery
7. After all these commands are executed, when i checked my database, it contains again the one record in each table. Ideally, according to me the tables should contain 3 records as i had taken the backup when each table had 3 records.
How can I bring the database back to the state in which I had taken the backup.
Pls. help me as soon as possible.
And also let me know, if I need to do any SQL level or Database level settings for the above task.
Waiting for your reply. and thanking you in advance.
Regards,
a_k93
I think jthis is a bug. I have a table created and populated on its own filegroup. I backup the db(all filegroups) and the trans log then I drop the one table. When I try to restore from my backups, it insists that I back up the trans log again. I do, then do the restore of both the filegroup and the trans log. The restore finishes, but my table is still not there and I can never get it back.
Any one with comments please help
Brad kreuzburg
I hope this is the right forum, if not sorry about that. On Friday I will be doing support with another orgnaization on a SQL 2000 cluster system. The database will be backed up , another team will do the major application upgrade, and I will be restoring the database back. From the SCN, there will be no database changes. I have been searchng for information on databse backups / filegroups.
My questions are:
1. The database has 3 filegroups, besides the primary. If I do a complete backup, will it also do the filegroups?
2. If not, how do I backup the filegroups?
3. Whne I resote the databse, how do I bring in the filegroups from the backup?
Any info or pointers to links is greatly appreciated. Have a good day.
Carl
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
I have a SQL 7.0 database Drill.mdf (19 MB) and Drill_log.ldf (594 MB).
After operation "shrink database" space allocated became as:
Data space Used Free Total
Drill 18,06MB 0,06MB 18,13MB
Transaction log space 20,79MB 545,70MB 566,49MB
Could I somehow decrease the size of Drill_log.ldf file?
I have a sql 2005 system and have a very large 25gb ldf file and a 200mb database.
Someone has set the initial size at 25gb so shrinking the file will not help.
I need to remove/add the file to gain space on the drive.
SQL 2000 you could just detach the DB and rename the ldf file and attach the db back and you are done. With 2005 you cannot do that.
Yes my DB file is 10MB but my LOG file (LDF) is 300MBis there a way to reduce or compres that one?thanx
View 2 Replies View RelatedDear all,
How can I resize SQL -2005 log file ?
Please help me
Hasan
Hasan
Hi, I wish resize the allocated space of my DB.
It's 245GB, but the DB is 50GB.
I tryed this query:
use master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = 'mydb_data',SIZE= 50000)
GO
but I receive an error:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
Can you help me?
Thanks
I have added a pie chart to my report but the legend is not wide enough. Is there a way of altering the width of the legend?
View 1 Replies View RelatedI am doing a report in SQL Server Reporting Services 2k5 , the report has a chart that displays data from a query. The question is:
Is possible to dynamically resize the area occupied by the chart depending on the amount of information (variations of a category) to be displayed on it ?
Stopping and starting the SQL should return tempdb to its initial size. It is not working for me.
Tempdb is up to 25 GB.
I tried deleting the mdf / ldf during stop, did not work.
Hi,
I have a very big report with 60 columns and some columns are invisible.
The problem is now if I have 30 columns that are invisible (hidden=TRUE), the report width still stays at 60 columns width, and the Horizontal scroll bar become very long and it just scroll to blanks.
I would like to know how to resize the report after it is generated?
Thanks.
Jon
I am trying to resize a COLUMN. Can I do this with the ALTER TABLE MODIFY command? I am getting the following error message when I try the "alter table [admin] modify [runFileTypes] NVARCHAR(250)" command.
" [ 1,21,modify ]"
command = ssceconn.CreateCommand()
command.CommandText = "alter table [admin] modify [runFileTypes] NVARCHAR(250)"
command.ExecuteNonQuery()
The only other option I can think of is to rename the old field, add a new field, copy the value of the old field to the new field, and delete the old field. I have some of the code below, but I am not sure how to copy the contents of the old field to the new field.
command = ssceconn.CreateCommand()
command.CommandText = "ALTER TABLE myTable RENAME COLUMN [oldField] TO [newField]"
command.ExecuteNonQuery()
command.CommandText = "ALTER TABLE myTable ADD COLUMN [oldField] varchar(500)"
command.ExecuteNonQuery()
command.CommandText = "ALTER TABLE myTable DROP [newField]"
command.ExecuteNonQuery()
I have a report that has a certain number of columns that are hidden and when hidden they leave a lot of white space. Is there a way to detect when they are hidden and set the width property to 0 and then reverse the value when they are visible?
View 1 Replies View RelatedHow can I resize a bitmap to fit the screen? I have inserted a bitmap using an external source.
View 4 Replies View Related
I am linking to another SQL Server database to extract data into my own system. Following an increase in size on one of the columns in a table I am extracting, I am getting the following error:
'OLE DB provider 'SQLNCLI' for linked server 'gsc-rpt.svr.bankone.net' returned data that does not match expected data length for column '[gsc-rpt.svr.bankone.net].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 148'
The column short_description has been increased from 100 to 1024. Writing a simple query, SELECT (max(len(short_description)) FROM dbo.Peregrine_Problem will generate the error, but if I embed the same SQL into an Excel VBA macro and connect to the server, it happily returns the result 1024. Our Access databases also link to the table on the remote server and handle the increased column size properly.
I have done everything I can think of, including deleting the linked server within SQL Server MS, and recreating it, but I can't override the setting of 100.
Any ideas?
Hello everybody!
I am currently developing a CustomQueryDesigner which is based on a Windows.Form that I have already developed.
Currently the QueryDesigner Class contains two UserControls: the ToolBar and the class that wrapps my own QueryDesigner.
So what I want to do now, is to resize my Windows.Form which is encapsulated in the second UserControl whenever the size of the window, that contains these two UserControls, is modified.
I already tried to use the "Parent" property and the "FindForm()" method but both just return "null".
--> I do not unterstand how I can access these informations!
Please help me out!
Hi,
We have a very severe issue in our production, and this is very very ugent to fix this issue, there was a job started on server on 10th October, 2007, for backup of database of 3.2 TB, which caused this issue on 18th October, 2007.
I cancelled that job and even restarted the server, but still there is same issue on the server.
I am using this command to expand the physical file,
ALTER DATABASE [ODS] MODIFY FILE ( NAME = N'ODSFG33File1', SIZE = 307200000KB )
Error:
MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file.
Server:
64bit Itanium Server with 4 Processors
4TB of SAN Storage
24GB of RAM
MS Windows 2003 Enterprise Edition with SP1
MS SQL Server 2005 (9.0.3152) Enterprise Edition with SP2
Database:
3.2 TB Size of Partitioned Database with 60 File Groups
Every File Group contains One month data in its only one physical File.
Physical Files are of different size between 10GB and 300GB.
Kind Regards,
Muhammad Imran.
I recently upgraded to on 2012 SP1 CU5 and have found the SSDT gui for SSIS to be almost unusable. I can't drag or resize items. Any time i try they either automagically shrink to the tiniest possible size, shoot off to some extreme or just shake uncontrollably I didn't have these problems on previous versions (dont remember what It was).
Is there a fix for this?
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.
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
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
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
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
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 Relatedhi all,
What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?
Hi,
Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?
Thank you very much in advance!
Denise
Hi,
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
Thanks
kira
I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.
Any ideas what is causing this anyone ?
Thanks