I'm looking for a bit of advice about the best way to make automatic remote backups of a MS SQL server.
I currently run a dedicated windows 2003 standard server that i have complete control over, but my databases are hosted on a shared machine that i have no control on.
Is there a way to automate backups from the SQL database onto the windows machine?
Any suggestions on how to do this would be appreciated!
Using SQL Server 6.5 and NT 4.0,..I'm trying to schedule a backup of a transaction log from one SQL Server to another SQL Server both running NT 4.0. I keep getting:
dbsvolopen:Backup device t:ackup emote.dat failed to open, operating system error =5 (Access denied)
I've checked every conceivable permissions entry on the target NT server and am now reading about the different security modes for communicating between SQL Servers but I'm concerned that this may not be where the problem really resides. Time is money.
Has anyone else configured a backup strategy where the database has a full backup to tape daily and the transaction log is backed up every couple of hours, without truncatre, to disk, on another SQL Server?
I have installed Sql Server 2000 evaluation copy on Windows XP, which I use to monitor other sql server 2000 installations (on Windows 2000) through Enterprise manager version 8.
What has been noticed is that if you try and set up a backup job through enterprise manager you do not get the option to remove inactive transaction log entries (yes the database's use full recovery models). Also if you select the verify backup option, it is not included within the job.
This is the same if you try the above on the local instance. This is not a problem as you can use the server installations, but what I am wondering is:
1. Has anyone come across this before? 2. Is it sql server 2000 on XP? 3. Is it the evaluation copy that is causing the problem? 4. Is it none of the above but indeed a far more complex reason which will mess with my head?
As always your help / knowledge is very much appreciated.
We have about 40 locations worldwide, with a typical data size of about 200 GB per location, with some up to 2 TB. Most sites have 1024 to 1536 Kb/s MPLS links, with a few 2 Mb/s and one 4.5 Mb/s. Central data center has two 45 Mb/s links.
We are looking to remote, online backups to our data center from each location. I'm wondering which products and processes have worked.
Has anybody successfully implemented remote, online backups of Windows servers across their WAN to a central data center?
What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?
I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.
We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
I am thinking about moving some backups from the local machine to a remote server. Right now, I am using Ola Hallengren script for backups. The script performs checksum on the backup while it's being written on the disk. I am going to move the files using Robocopy utility. How do I check the data integrity once the backups are transferred to the remote server?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
Hi everyone,i'm new to ado.net and need some help. My problem:got one DB with real bad tablestructureand one new DB (structured!)i need do do an automated import from the old to the new DB, with check if the data row is new or updated.I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchangedother way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changedSo how can i get the merge working or how can i implemt an effective funktion to solve this problem. Thanks in advance!
Hi, looking for some paths to take on this one. I have to backup the database to a LS120 floppy drive. It has to be automated so the user can just click on a desktop icon and away it goes. Probably an easy script buy i sure would appreciete some help on this one.
Here is the situation... there are random (.csv) files being dropped into a specific directory on a daily basis. Is it possible to have SQL server automatically pick up this file and run it through the Import/Export data option so that it creates a SQL table from the original data? I need this whole sequence to be automated. Is this possible? Somebody please help... I will appreciate it greatly. Thanks a lot!
I am trying to automate the installation process of our Help Desk software `Expert Advisor`. It uses a MS SQL Server 6.5 database. I can figure out the automation of Expert Advisor, but is it possible to automate the installation of the SQL drivers for each workstation? I don`t want or need any of the utilities such as ISQL/w installed along with the driver--just the SQL driver itself. Is there a runtime version of the database I should use? I`m fairly new to SQL so please forgive me if I seem naive.
Any suggestions? Any help would be appreciated. Toni
I don't know what happened on that first thread, but here it is again.
Thanks for your help.
Hello, I have set up a .bat file to automatically export all of my server logs to a directory on my SQL server. From there I am trying to import those files into an event_log table that I have set up. (from there I am going to use Cold Fusion to parse the table each day and e-mail me with anything I should look at) The estimated ROI for my company is roughly $14.5k, it's easy so if you don't already have an error log monitoring solution, check this out!
Anyway I tried to use BCP in my .bat file but it keeps coming back with an unexpected end of file error.
Can I get some help with either: 1. Find a command line tool to import these .txt files with -or- 2. Help me with the BCP issue?
I am an intern at a university and we are looking for a way to automatically document tables, queries, etc. ColdFusion is the platform used, and SQL Server 2000. Is there a way to have the documentation performed that will capture tables, constraints, etc.? I have found some, but so far, none are working. Please help if you can!!! :confused:
I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".
Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.
I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.
Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.
I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.
I've only used restore via the enterprise manager and I was wondering if anyone here could help.
I have a customer who insists on emailing an excel spreadsheet to me to be uploaded to a sql database. I came across a script at one point that laid out how I would script this out. Basically it was in parts, 1. open email program and get attachment. 2.After saving attachment closing email program in order to be used the next day or next time an attachment was sent. 3. import the attached spreadsheet into SQL database. I would also like to be able to schedule this to run with the built in Windows 2000 scheduler.
Hi, I'm working on a program that is some kind of organizer for all the tasks that employees have to carry out. A part of this program should check the deadline from the tasks, and send a mail if the deadline is in less than 5 days. I'm using asp .net 2.0 & sql server 2000 Can anyone tell me how I can do that, or put me on track with some key words to google for? thx.
I'm pretty new in SQL Server and trying to build up a BI Solution for my firm. We have got monthly data about revenue headcounts and so, which should be integrated into a DW.
I was wondering whether there is an automated way to import this csv data in SQL like in a batch, because the users are not familiar with SQL Server at all. The structure of the data will be the same all the time, since it comes from legacy systems.
I have around 80 databases in my one of sql server instance. we hve to regularly send the database wise health report to the client. The following fields mentioned by the client in excel sheet
Name of database Logical file name of .MDF .Mdf File location drive Size of the file in MB Physical file location(full path of .mdf file) Maxsize Growth
Now a days we have to check databases one by one. Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.
Was unsure where to place this post. Windows Server 2003R2 w/ Sp2 MS SQL 2005
I currently use MS SQL 2005 to host my Citrix Datastore DB. I manually run a daily back up (Tasks>Backup). How difficult would this be to automated? I have googled this without luck.
Unfortunately don't I know, how I can adjust this or whether SQL code is needed and if, where?? I do not know myself unfortunately yet so well with the subject out, from therefore am grateful I for each assistance. Many greetings M-l-g
Hello,
I have a problem with an integration services project of SQL server 2005. I have a text file with various data, which can be read out easily with a dataflow task and a flat file source. The data shall be transferred into a target database which I developed according to a star pattern. The fact table can be generated automatically if and only if all data belonging to a dimension table is inserted by myself. Of course, this procedure is not reasonable while having a vast amount of data. That is the reason why I want to generate the dimension tables automatically, too. A dimension is build e.g. like this: ApplicationID | ApplicationName. So, only two columns are available. But I don't know how to insert an automated ID.
We have automated the process of backing up and restoring the databases. But the problem is One database is restoring from several hrs ..though it is supposed to take only a few mins. So there is some issue with it and I want to find out how I can stop the restoring process. Its not a command am running in query analyser or Enterprise manager, but its automated so i cant see the command to stop it. Any ideas?
Hi I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me. With thanks
Where can i learn about making the sql server execute task when a certain situation happens. Like lets say make the server delete all entrys older than 40 days? Or have a entry deleted automatically when a certain situation happens like lets say it take 4 votes and a entry get deleted how do i make it do that automatically
I need to create an Automated DTS package. At present I am running it manually each morning to import our phone switch data to an SQL table. The reason I had to go this route is this.
Because the Access tables are locked, I have a System DSN that connects to the System.mda file, this in turn gives me access to the locked tables to extract the data I need into Excel. I then drop the existing SQL table and run the DTS package recreating the SQL table with the data from the Excel file. I do not want to use OLEDB to connect to the Access table from within my application because it changes each month and of course the traffic load, I have as many 150 clients hitting the DB at one time.
I actually need the DTS package to update the SQL table with the latest data.
I'm no DTS guru, so how can I do this automagically?
Here's my process: 1. I have a form where someone enters certain criteria for some data… 2. The form than populates a record in a table with all the criteria… 3. I create a query based on the selected criteria for exporting…
How can I automate the process (maybe using DTS) where SQL server will automatically export the data for me using a stor proc? Create a file, populate the file etc…. or will I need to create an external App to do this?
I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...How can I automate this?
I have been playing around with the enterprise manager and selected Backup Database from the action menu and schedule this task for an odd time for testing.
How do I go about removing this task?
Whenever I go to the same menu item again, it just starts over .. like I don't have the task already setup.
I wish to select processes from sysprocess that are SLEEPING and more than a certain time old (say 10 minutes) so that I may KILL them. I can get the query to do the select, but how do I KILL the process? I have tried selecting the SPID into a local variable and then trying KILL @var_name, but I get "Incorrect syntax near '@var_name'".
I have tried all of the resources that I can find, but without success. Is this possible? If so, how do I go about doing it?
Is there a way to automate the generation of sql scripts. I addition to backing up the database I like to have a backup schema. Just wondering if this can be done.