Is there a way to move the primary log file for a database to another drive without doing this with a restore database operation? I have added a second log log file but now I would like to delete the one that was created when the database was created.
View 1 RepliesHi,
I´m trying to move one table to another filegroup, the actual table is created in the primary group, so I´m trying to do this:
ALTER DATABASE hemsa_dev
ADD FILEGROUP Bitacora
- .- add mi file group
ALTER DATABASE hemsa_dev
ADD FILE
(
NAME = hemsa_devBitacora,
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatahemsa_devBitacora.ndf',
SIZE = 500MB,
MAXSIZE = Unlimited,
FILEGROWTH = 10%
) TO FILEGROUP Bitacora
-- And I want to add my table in my new filegroup
-- Im trying with:
ALTER TABLE BitacoraPeso ON bitacora
- - or
ALTER TABLE BitacoraPeso MOVE TO bitacora
But It doesnt work....
Any Idea??
Regards
I just installed SQL 2005 on a new box. I want to move a database from a SQL 2000 server to the new server. Can I detach the database, copy it and attach it in the new server without having problems?
I'm concerned that if the datafile is in SQL2000 format, when I connect it to SQL2005 server, will it still be in old format or will it upgrade?....or is this something you don't worry about....and why?
Thanks,
Craig
Is this even possible? I tried to move an existing Sql Server Express .mdf file to SQL Server 2000 by copying the .mdf file to the server's folder and then tried to attach it. I get:
Error 602: Could not find row in sysindexes for database ID 9, object ID 1, index ID1. Run DBCC CHECKTABLE on sysindexes.
I realize that I'm moving back a version in SQL, however the structure of the database is very simple. Does anyone know?
Even if I can just script the thing to SQL 2000 would be helpful, however the .mdf is a file within my ASP.Net site, and does not show up in my Microsoft SQL Server Management Studio Express under the appropriate SERVERNAMESQLEXPRESS either, so I can't figure out how to script it out?
I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
I need to move specific files from a server to another server on a monthly basis. Â There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server. Â I would like to easily add or delete the file list as needed. Â I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them. Â With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable. Â Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....
View 10 Replies View RelatedHello
I want to move and rename a file and embed the date/time into it, so that each time the package runs a new file is created. For example MyFile_20060712_150000.doc.
Can someone give me a hint how to do this with the File Systen Task SSIS Control Flow Item?
Thanks for an early reply
Regards
Chaepp
Hi All,
I am having a problem using the file system task, what I am trying to achieve is to move a file after it has been processed . I am using a For each loop container to process bunch of files but I want to remove the files that have been processed after every loop.To achieve this I added a File System Task after my data flow task and was using the same variable used in the for each loop container as my source variable but the package is not being validated and is gives the following error
"variable used as the source or the destination is empty"
Appreciate your help
Thanks
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
We have a problem with our SQL 2000 server which we know is a product bug.
Is there anyway of importing, Copying the sql 2000 table back down to SQL 7 until the issue is resolved,
many thanks
Hello,
I'm in the process of moving a SQL Server 2000 to a new server. I have installed the new server added the latest sp. When I try to restore the system databases (Master, msdb) I get the following error
"the database xxxx.bak Cannot be restored because it was created by a different version of the server restore database is terminating abnormally"
I have looked at everything that I know of and both servers seem to be running the same versions of everything.
Can I just export the databases the the new server?
thanks in advance for any help you can give.
Jason
How do I move a tab serperated file into a table in SQL express using sql express? I need simple fast way to to this.
I can export all my access file into tab or comma files and then build the new tables and import them. I tried upsizing the table in access but sql express will not accept them at all and I cant find a way around it.
Thakns
Hi!
I have created i webpage whos has a logged in system which is cretead in VS 2005 with the function ASP.NET configuration.
And know will i move all databas to i a MSSQL 2000 there i only can have one databas, And know i have ASP.NET logged in Databas and one site databas.
I just wondering if its possible to make i virtual connection with MSSQL 2000? If not somebody has some idés for me?
I hope you can understand me.
Hi,
I have an existing sql server 2000 installation on one server. I now need to take one 2000 DB and restore it to a new sql server 2005 installation (on a different server).
What is the best way to do this. I can not upgrade the sql server 2000 installation to 2005. Basically i hoping all i need to do is backup the 2000 db and then restore it on the new sql 2005 server.
Is this possible...thanks for all help.
I need to move our production database to a brand new machine on our LAN.Both machines are running SQL2K. I'd like to place the production databasein a directory on the destination machine with a slightly different pathname than the source SQL server.How can I shrink the transaction log to the minimum size before I try themove? What is the best way to move the database to the new machine? Wehave Veritus Backup Exec.Regards,Charles MacLean
View 1 Replies View RelatedHello,I have a database in 2005 that I'd like to copy and attach in 2000. Is thispossible? if so, how do I do it? I've tried doing a number of things fromjust trying to attach to the db file to trying to export and import viaaccess and I can't seem to get anything to work.Any help would be greatly appreciated.Thanks!Rick
View 1 Replies View RelatedI have a MSSQL2000 box with a large database containing circa 150 SP's.I want to move the DB to another SQL machine, but many of the SP's havereferences to the name of the current machine. Is there any way tobatch edit the procedures to refer to the new server, disassociatingthe old machine completely?Tia,Graham
View 1 Replies View RelatedWe needed to replace our Motherboard on a server running win 2K Adv.Server with SQL server 2000. After replacing, we could not bringthe system back with the previous OS. So, we have decided tomigrate to Windows 2003 server and install SQL svr on it.On the old server, the SQL DB is residing on a seperate disk.Is there any way one can install SQL on the new server and importthe data from the disk. Many posts talk about how to do detach/attach etc.The case there is they have a running source server. In my case, I don't.any pointers will be appreciatedRegards,Anand
View 1 Replies View RelatedWe have purchased a new Development SQL Server that is running SQL Server 2005. Our existing Development SQL Server is running SQL 2000.
How can I move the DTS packages from the SQL 2000 server to the new SQL 2005 server? I will be upgrading them to SSIS at some point, but don't have the time now.
thanks
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
What's the easiest way to move a 7.0 log file to a new physical location?
View 2 Replies View RelatedHi all,
I'm trying to move the files with flat system task. Before I move the files I like to created the new directory, and each directory can hold only 10000 files. Are there any examples or anyone can point me to the right direction?
thanks....
Hi,
I have a SQL2000 database that I want to move to SQL7. What's the best
way to do that?
Many thanks.
Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...
Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.
Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.
Thanks.
I have been asked to move a system running MS SQL 2000 Server from a workgroup to a domain. The SQL server is running in SQL Authentication and the services are running as local system.
Will SQL Server break after the move?
Any advice will help. I thought I remember this being the case, but possibly only with Windows Authentication.
How to import database in SQL server 2005 from the SQL server 2000 ??
I am trying to split the log and data files between two drives on a newSQL-Server 2000 installation. I followed the instructions from the MSarticle 224071 (Moving SQL Server databases to a new location withDetach/Attach).Unfortunalty, when I try to move the Master database, as perinstructions, access to SQL-Sever is lost;the service will never startagain. I've tried this twice after re-installing with the same results.Anyone have any ideas as to what is going wrong?Tim
View 3 Replies View RelatedI have a several indexes on a filegroup that I would like to move to a different physical drive. I am aware of the sp_detach...sp_attach routine which allows moving the .mdf and .log files to a different location. How would I go about moving a .ndf file though?
TIA
I want to move my database file to a new directory, I search in MS and I use the following steps:
USE master;
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'test');
ALTER DATABASE test SET OFFLINE;
ALTER DATABASE test
MODIFY FILE ( NAME = test_Log,
FILENAME = 'D:Databases est_log.ldf');
ALTER DATABASE test
MODIFY FILE ( NAME = test,
FILENAME = 'D:Databases est.mdf');
ALTER DATABASE test
MODIFY FILE ( NAME = test_Log,
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData est_log.ldf');
ALTER DATABASE test SET ONLINE;
But I had the following errors:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:Databases est.mdf". Operating system error 5: "5(error not found)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:Databases est_log.ldf". Operating system error 5: "5(error not found)".
File activation failure. The physical file name "D:Databases est_log.ldf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'test' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Can someone tell me how to move my database to another directory (in the same server instance)?
Thanks ina dvance
Good day ,
Does anyone know how to moe / copy database users from sql server 2000 to sql server 2005. I ave been successful will everything except this in my migration. I can copy the database users themselves but not the users under the security section.
Please help
I need to move the SQL Server 2000 Desktop Engine (MSDE) database and all jobs to the SQL Server 2005 Enterprise Edition.
- May I detach and attach the DB in my case?
- Where is the procedure to move the jobs from MSDE to SQL Server
2005?