where in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 RepliesI am using SQL Server 2000 and trying to create a disaster recovery strategy that would run nightly and backup the database or at least the changes and would ftp these to a secure ftp site. For smaller database it is easy, I just take a full backup, zip up the file and ftp it to the secure backup site. This strategy does not work so well when the zipped up database is still close to 3GB. I have a pretty big window for doing everything but 3GB is just too much to ftp overnight. The recovery model is simple so the only other option seemed to be do a full backup once a month and take differentials nightly. The problem is I am offsight and the client may need to take a full backup during the day and my nightly differential would get screwed up.
There is a fairly low volume of transactions so the idea of just doing nightly backups on the data that has changed is the obvious choice but differentials don't seem to fit. Any ideas?
Thanks,
TH
Quick question..
If I'm log-shipping data from a database can I still perform a full backup of it each night without it affecting the log sequence?
I'm requesting of our dba that he create a database with recovery simple for my peer and I to start using. I'm asking him to give us db owner on this db so we can create schemas, tables, views, procs, do table inserts, deletes etc etc. what sql permission (if any) would allow my peer and I to do a backup once in a while to the default sql directory for backups? And for that matter a restore from there.
View 7 Replies View RelatedI 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 RelatedHi,
I have some some files names in SQL DATABASE but my actuall files are keep in a seperate folder. so please help me that becasue i dont know that how i can select the specific files from folder and fetch into the imageArray according to the database table. In my coding its get the all files from directory but i want to get from database with where class and then select from directory.
The structure of my table is
create table event_pic( event_sub_id integer, event_pic_name varchar(50) )
here is code below:
Sub displayMe() dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ") dim cmd as new SQLCommand("select * from event_pic where event_sub_id='5' ",con) con.open() dim SDR as SQLDataReader SDR = cmd.ExecuteReader() con.close() Dim imageArray() As String Dim i As Integer ' grab full path and file of images on server in images folder imageArray = Directory.GetFiles(Server.MapPath("upload/"), "*.*")
' remove the full path from the image filenames For i = 0 To (imageArray.Length - 1) imageArray(i) = Replace(imageArray(i), Server.MapPath("upload/"), "") Next ViewImages.DataSource = imageArray ViewImages.DataBind() End Sub
http://forums.databasejournal.com/showthread.php?s=&threadid=29895
View 2 Replies View Relatedhow can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.
View 20 Replies View RelatedIs there an XP/SP out there that will return a list of files residingin a specified directory?I'm looking for something simlar toExecute master..xp_subdirs N'C:'But instead of it returning a list of subdirs I want it to return alist of files in that directory.Jeff
View 5 Replies View RelatedHello,
How to delete old files stored in a directory using dos script?.
I appreciate your feedback.
Thanks,
Ravi
Hi,
I have posted a request regarding deleting the file more than 1 month old that we have discussed in bleow URL.
http://forums.databasejournal.com/showthread.php?s=&threadid=29895
My new request is I have enabled C2 Audit mode and wanted to delete old files that is more than 200MB.
Could you Please give me a query ?.
Thanks,
Ravi
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
HI All,
I have Uzip Files to be loaded From ftp.companyname.com, the zip files get updated everyday thus i have to load the newly added files, the Zip file has got 13 text Files within it, the issue is:
1. how do i load it for the new zip file only, the zip files are shown below, i am trying using FTP Task, but need more info.
2. How do i UnZip it and and take the text files and then load them to sql server 2005, each text file has to be loaded to sql server tables.
3. How do i automate it, i mean every time i run the package (on Job based) it has to look the new file only, see the zip file below to understand what i am saying about.
02/25/2008 09:02PM 2,780,729 CompanyName_2008-02-22.zip
02/27/2008 09:04PM 1,274,557 CompanyName_2008-02-23.zip
02/25/2008 08:57PM 1,383,112 CompanyName_2008-02-24.zip
02/26/2008 04:21PM 3,327,882 CompanyName_2008-02-25.zip
02/27/2008 08:44PM 3,623,334 CompanyName_2008-02-26.zip
02/28/2008 05:27PM 3,570,243 CompanyName_2008-02-27.zip
02/29/2008 04:20PM 3,444,375 CompanyName_2008-02-28.zip
03/01/2008 04:17PM 2,655,782 CompanyName_2008-02-29.zip
03/03/2008 04:09AM 1,179,338 CompanyName_2008-03-01.zip
03/03/2008 05:21PM 1,267,777 CompanyName_2008-03-02.zip
03/04/2008 04:49PM 3,238,121 CompanyName_2008-03-03.zip
As you can see the Zip file names are the blues color, they are added at different time, thus what i need is only the current Zip file (i mean i have to Load only the newly added Zip file (recent one)). Thus the SSIS Task has to go to this FTP server and look the newly added zip file. After that i have Unzip it b/se i have 13 text files there, and then Load them to Sql Server 2005.
Please get help on this, the deadline is near by time, if possible try as soon as possible, I would like to say Thank you for every help you do and try.
Thanks,
Hello Group,
I have a SQL Server 2000 database that has never been backed up. The SQL Server database can't be logged into due to an SSL Security Error, thus I can't get to the backup utilities within Enterprise Manager.
What data files do I need to backup manually and what steps do I have to take to backup these files to a tape and rebuild the server?
Thanks
Kevin Kraus
i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.
Anyone have ny problems with maintenance plans not cleaning up when told?
a
Which is better, to store the files onto the server's folders or to a database?
I tried storing to MSSQL 2000 but the varbinary does not allow me to set "MAX" for the data type.
My problem, I have the aspx, .cs and ascx files but I don t have a project or solution that came with the files (since that was done by front page). So I need to add all those files in a solution or a project so that I can maintain the site that was created by front page using Visula Studio.
Do you know how i Can put all those files in a new project or new solution.
2/Also, when we modify the the pages, how can i put them beck in the site, will I have to compile the files again, or not. thank you.
Thank you
Hi
After install the mdf and log files are stored in the Program Files directory tree.
How do I change the place were they are stored to a path of my own choice, so that new DBs get created there too?
I've looked though the docs, but I can't find this.
Thanks
John
Here's the senario:
View 8 Replies View RelatedI have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution
View 11 Replies View RelatedMy scenario -
-Dev server lost all its raid drives (which included user data/log files and ALL backups)
-Raid drives were removed from server, so we could try and recover the data (not sure why they had to remove them)
-Recreating the user db's isn't an issue, but I REALLY need to retrieve the jobs from the MSDB (developer was working on dts package for the last 2 months, but didn't check it in to VSS)
-Since the System DB's (model, master, msdb) were installed on the C drive, I was able to recover their mdf/ldf files.
So we build an new server and recreated the user db's.
How do I go about getting the jobs data from the msdb database?
I've tried the following -
Started SQL server with the -T3608 and was able to detach the MSDB database, but when I go to reattach using the mdf/ldf files from the other server I get the following error message.
Error 5172: The header for the file 'C:mssqldatamsdbdata.mdf' is not a valid database file header. The PageAudit property is incorrect
I've been doing some research and found a lot of great articles on how to RESTORE (from backup - dont have) or Rebuild (wipes out the data I'm looking for) the MSDB, but not how to replace it with only the ldf/mdf files....
Any idea's would be greatly appreciated.
Thanks
PS - All backups are now being copied to another server (lesson learned)
My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7.
DROP TABLE images
CREATE TABLE images
(
image_name varchar(500) null
,image_data varbinary(max) null
[Code] ....
Currently looping through the set of flat files like CHK0604, CHK0611, CHK0618, and CHK0625 from the source folder C:SOURCE
OBJECTIVE within the flat file if any records/rows cause error i have to move the bad data into separate folder C:ERROR
STEPS TAKEN
1) In FOREACH LOOP component i specified the variable User:: sourceFilePath for my source file CHK0604 etc. location C:SOURCE. The loop walkthrough each file in C:SOURCE and if no error then moves the flat file into another folder C:ARCHIVED. This task is perfectly working.
2) Within the dataflow I am diverting the the bad rows from "conditional component" into "Flat File Destination" Component.
3) "Flat File Destination" Connection manager i set the expressions as @[User:: sourceFilePath] +"_Error.TXT".
ISSUE
Because of point (3) the error file is created in the SOURCE flat file location C:SOURCE.
QUESTION
1) My error file name should be CHK0604_Error, CHK0611_Error, CHK0618_Error, CHK0625_Error created in another folder C:ERROR.
2) How to move the bad data into another directory while looping through a set of FLAT FILES ?
3) If i have to create another variable like @[User:: ErrorFilePath] where to create ? How to use the source file title as the title of error file.?
Thanks for the help
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka
Michael writes "We are running SQL and Veritas to backup the databases. Supposedly the SQL agent in Veritas, after a full backup, truncates the log files but for some reason this isn't happening... any ideas?"
View 4 Replies View RelatedWe have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.
I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.
Has anyone experienced this issue before?
What causes it?
How do we resolve it?
Thank you in advance for your help,
Greg
I have a scenario where I need to move a series of files from within a directory of many files. The files follow no nameing convention and are more of less random. However the file names never change from week to week. I tried various different options in a 'file system task', no go.
Any ideas on how to move only a list of files?
or
can I load only specific files into a 'Foreach Loop container' from a certain directory. I tried delimiting file names in the file source, that did not work.
Either way can work,
Thanks
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
I need to only receive files via ftp when the file does not exist on my local machine. FIles are being added to the remote location on a weekly basis and they are being downloaded locally. I do not want to download all the files each time. Instead, just download what was not already downloaded. Is there a way to do this? I want to do this using SSIS / ftp task.
Thanks in advance.
Hi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View RelatedPlease help.I have Database A (Archived data), Database B (Transactional data).Both A and B do not have any timestamps in any tables.I need to bring A (only once because it is archived data) and B withonly differentials into Database C for reporting purposes.Currently C is cleared before loading B with differentials every nightand A is not loaded since it takes 15 hours to load.Now I want to bring A once and leave it and then bring B every nightwith differentials without clearing C.Please suggest some solutions.Thank You
View 1 Replies View Related