I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.
View 5 RepliesHi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies View Related
Hi All!
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86)
Mar 23 2007 16:15:11
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
40 subsribers.
Adding new article.
On any aubscribe need regenerate snapshot agent agent.
The tables are very big. With filters.
Regenerating process anew copy data in in ...ReplDataame.bcp, during 30-40 minutes for any subscriber!
Full work to small correct of replication flow during 2-3 days....
Is it possible to initialize subscription without copying bcp data?
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
View 1 Replies View Related
Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files?
Best regards
I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.
View 1 Replies View RelatedHello, everyone:
I want setup a scheduled job to copy txt files from FTP to local hard drive. How to open FTP and copy files from SQL Server Query Analyzer?
Thanks
ZYT
I have been looking for a way to copy only new flat files from an FTP server. I can find how to copy all of the files into another directory, but I only want to copy in files that are not already in my target directory.
So, is there a way to compare the files in the FTP directory to a list of files in a SQL table and use this to control the transfer, and how do I get those FTP file names into a SQL table? Or is there some other way?
Thanks in advance for any help.
Graham
Howdy, hope someone can help me out with this.I want to run a job each night that copies files from one server to another.I cant even get a simple copy one file from one directory to another,statement to work.When I try :@cmd 'copy c: empfile1.txt c:ackupsfile1.txt'master.dbo.xp_cmdshell @cmdI get the msg: 'c' is not recognised as an internal command, program orbatch file, Null.What I ideally want to do is pass xp_cmdshell variables for the 2 file'spath+names. I have tried numerous variations of single and double quoteswithout success, which is why I decided to work up form the simple 'copyc: empfile1.txt c:ackupsfile1.txt', but can't even get that to work.Any advice much appreciated.
View 3 Replies View RelatedHi,I'm having some trouble finding any information on this topic - can MS SQLserver 2000 copy/delete files on the host computers disk.Any info on this would be great.Thanks,Eirik
View 2 Replies View Relatedhi , I am trying to copy files dynamically from a remot folder by using File System task ,
the files are going to be generated daily with f1yyyy-mm-dd.txt
I created 3 file connections :
- Existing remote folder (RemotFolder) and set that to var v_remotefolder
- Existing destination folder (DestFolder)
- existing file (filename) and set the expression to var (string ) =v_fname
in the File System task I set the the
Destination connection = DestFolder
Operation =Copy file
Source = filename
expression >>Property (source) = @[User::v_remotefolder] + @[User::v_fname]
I know I need the date too ,but I 'm getting error on this step.
Can you please tell me if you know how to resolve this without using For each loop since I don't have control on the remote folder and I don't want to copy unneeded files (the files are 10 GB daily) and get delete it once every month.
-I know this not uncommon task but I can't find any example.
Thanks
I was trying to copy backup files from production domain to test domain nightly using xp_cmdshell. I
View 3 Replies View RelatedI connect to remote servers using PPTP connection. There are 4 web servers and 1 production server on the other side of the firewall. From my staging server, I am supposed to archive the NT event logs(Application and system) for each of the servers.
To solve this problem, I have mapped the C drives of each of the remote servers to my local staging server. I then created a batch file, webevnt.bat containing code,
G:
Cd winnt
Cd system32
Cd config
Copy sysevent.evt c:eventarcweb01sysevent.evt
Copy appevent.evt c:eventarcweb01appevent.evt
Then I used SQL Agent to create a job of type, Operating system command(cmdexec) and typed the following command :
C:atch_~1eventl~1webevnt.bat
When I run the command, C:atch_~1eventl~1webevnt.bat through the command prompt, the files are copied with the correct modified date.
The output is,
1 file(s) copied
1 file(s) copied
But, when I use SQL Agent and start the job containing the same command, the job runs successfully but, the files have a wrong modified date. Moreover, when I view the job history for this job, it says, specified drive not found. 1 file(s) copied. 1 file(s) copied.
Please let me know why this happens.
Thank you.
Praveena
hi.
how can I copy any mdf file to another folder without stopping sql server?
Hi,
I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.
I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.
Many thanks in advance
I was wondering if there is a way to copy files from a network to an ip site where you would need to log in at. What I am trying to do is make a SP that would take all .csv files from the network drive and place them in the other ip address folder however you would need to use the login information.
View 5 Replies View Related
I have a situation from where I need to loop through different folders and files in these folders. After processing these files, I need to archive these folders to different location.
e.g., C:MainFolderMar01 ==> Multiple files in Mar01 folder
C:MainFolderMar02 ==> Multiple files in Mar02 folder
Does any one know the best way to do this in SSIS?
Thanks in advance.
BC
Hi,
How do I copy files from several subdirectories into the same directory? I have about 80 subdirectories of a parent directory from which I want to copy the files and place the copies into the same, flat destination directory. I tried using the copy file and copy directory file system tasks, but this always seems to recreate the subdirectory structure under the destination directory.
How can I get around this in SSIS?
Thanks in advance,
Bruce.
In SSIS, I was to:
take files from c:directory1
copy them to cirectory2 with a different name (concatenated date on the end)
delete them from c:directory1
Should I be using the Script Task for this? I am wondering if I should be using the File System Task which copies directories or files, but I don't know if I could rename the files during the copy.
Also, I want the directory names to be in the configuration file. I am not sure how to do this. If I set up a flat file source connection, I need to specify a file name and I just want the directory names. How do I get them in the config file and how to I read them into my script from the config file?
Linda
This is a question of whether or not to use SSIS to solve a problem.
I need to copy SQL Server database backup files from a server in the DMZ to a fileserver inside the firewall. The SQL Server is not allowed to write it's backup files directly to the fileserver, so they are written to local disk. A connection can be made from inside the firewall to the SQL Server to copy the files off.
So, I'm considering SSIS for the job. Is it possible to use SSIS to perform the file copies from one remote server to another? If so, is the FTP task required, or can File System tasks be used?
An alternative would be Windows scripting, xcopy, robocopy, etc. but I like the features of SSIS and would like to take advantage of it's flow control, error handling, database scheduling, etc.
Any tips, sample code, etc. would be appreciated.
Thanks,
david
I am looking to implement a system whereby a given user can 'authorise' changes to the system itself and promote from test to live.
For instance a user might be running an application which has a SQL 2005 database DataA opena nd runnign stored procedures from that. I'd like a Stored procedure in that able to copy over a given stored procedure from a different database - DataB and copy into DataA. Is that possible?
Also is it possible from T-SQL to copy files on the servers underlying filesystem? Ie to copy some .aspx files from one location to another.
Regards
Clive
I've an emergency requirement to copy Source server database backup files to destination Server through xcopy command. Backup job on source server runs daily, so once this job get completes all databases backups needs to be moved to destination server. But here the main concern is "the backup files on destination server shouldn't be overwritten, they should be placed separately as Source server job runs daily".
We've a command which overwrites backups on destination server. But we need to keep backups on destination at-least for 4 weeks (means : retention should be 4 Weeks).
I have one job for copy files from another server to my computer using Robocopy.but when I execute my job in sql server I get this error:
Accessing Source Directory 192.168.101.191Upload
Access is denied.
this is my job:
USE [msdb]
GO
/****** Object: Job [Test] Script Date: 02/04/2015 13:08:00 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/04/2015 13:08:00 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
[Code] ....
Should I create Credential and proxy?How can I do this?Would you mind giving me required scripts for this?
I need help with better solution to copy my bak and trn files to a file server. This is the background. I use to dump the backup files directly to the file server; this was not €śbest practices€? according to some books and forums. Back to dump the file first to local disc and after that letting robocopy copy the files to the file server. And I end up with this problem.
This is error from the backup log
Failed-1073548784) Executing the query "BACKUP DATABASE [BPROJCT] TO DISK = N'F:\backup\BPROJCT\BPROJCT_backup_200711281700.bak' WITH NOFORMAT, NOINIT, NAME = N'BPROJCT_backup_20071128170005', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Write on "F:\backup\BPROJCT\BPROJCT_backup_200711281700.bak" failed: 112(There is not enough space on the disk.)
This is the error from the robocopy log
New File 0 BPROJCT_backup_200711201700.bak
2007/11/20 17:02:11 ERROR 32 (0x00000020) Copying File F:ackupBPROJCTBPROJCT_backup_200711201700.bak
The process cannot access the file because it is being used by another process.
Waiting 30 seconds... Retrying...
Robocopy tries to copy the file before its completely written to disk. I have made a batch file that looks like this
robocopy
"F:ackup" "\sefwg-sqlbackackupACON" *.* /MIR /TBD /MON:1 /MOT:5 /LOG+:"F:ackupackup.log" /NP
Monitor the file folder and copy all files every 5 min, the file is started with scheduled Task.
Have I missed some switch? Can start robocopy from SQL Serer Agent in a xp_cmd_shell?
Hello,
We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?
Thanks in advance.
We are having a problem with trying to backup the database device and log DAT files located in the MSSQLData directory.
The Seagate Backup Exec. states that the files are busy and skips them during its backup cycle. It skips all the devices in the
directory.
Any suggestions?
Hi All
One of the step in sql agent job is to execute the SSIS package to copy files from remote shared location to local server where sql server is installed. The account on which SQL agent runs as has access to remote shared location. However SSIS package always fails with following error:
An error occurred with the following error message: "Access to the path '\sharedlocationBCKTST105092008.csv' is denied.".
We have tried using proxies but same issue come with proxy also.
When using proxy, we created a proxy for a user who has access on that shared folder on Windows server(from which files are to be copied). If we login to SSIS server with the above user and execute SSIS package manually it works fine. However if we login with different user and run the job via SQL agent using proxy of the above user, then job fails throwing same above mentioned error
Any help is highly appreciated.
Thanks
I need to import multiple csv files and load into table and everytime new database has to be created .
I was able to create new databases using stored proc
How do i do a bulk insert for all the files at once to insert into tables .
i want to load all the files at once .
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedI have Developed ETL Package Which Supplying the CSV File, if I run the package Next time if Same File name  is there I need to Rename the that File with Currentdatetime need to move in to Archive Folder. if that File is not exist in that location no need to move the file into Archive file.
View 4 Replies View RelatedHi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
I want to schedule a job which pulls files from a non SQL server (Sybase) which later needs to have a step 2 kicking the ssis package. Problem is that, on the source a batch file will run every 4 hours and outputs total of 10 text files. (takes 5 minutes complete). Now, on destination i want to pull these files via SQL job but while scheduling;
1. I don't see any option saying like 4 hours 10 minutes or so
2. If its out there, then i believe this might be a problem as this time would be an increment one e.g next run would be 4 hours 20 minutes in that case.
How should i achieve pulling these files up because we have an SSIS package on destination that needs those text files to be used as soon as they arrive on SQL server(destination)
Can anyone provide an expample of bulk copying XML data to a SQL table. I am also looking at using column mapping so that I can map fields and also insert a new GUID into the key of the SQL table.
Many thanks