Strategy For Nightly Backups Using Ftp
Oct 22, 2004
I 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
View 2 Replies
ADVERTISEMENT
Apr 22, 2008
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?
View 7 Replies
View Related
Aug 29, 2006
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 Replies
View Related
Sep 19, 2007
Please 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
Jan 24, 2008
Do you guys do the DBCC CHECKDB on system tables TEMPDB, MASTER, MODEL, MSDB.
When do you run the DBCC UPDATEUSAGE
First i do the following at night
Check database integrity
Rebuild Indexes
Backup Full
Remove old bak
clean up history of all bak
clean up history logs
I was wondering when to put in dbcc updateusage
I look forward to your comments
View 11 Replies
View Related
Jul 3, 2007
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
View 1 Replies
View Related
May 20, 2008
I have a production database that I would like to have copied over to a backup database on a separate server every evening. I don't want to mirror, I just want the databases synced up every evening.
The servers are physically attached through a gigabit switch and the database is relatively small, so I don't think that speed will be an issue.
Could someone point me to an article about the best way to accomplish this?
Thanks.
http://www.dynamicajax.com
View 2 Replies
View Related
Nov 6, 2006
I know this is probably an easy thing to do but I've been struggling with this on and off for the past month or so. All I want to do is copy two databases (our app db's) to two other databases (for app testing and training) on the same server every night. The databases aren't used during this time so they can be taken down if necessary.
I've tried using the Copy Database wizard and scheduling the resulting job but that isn't working...
Thanks in advance for any direction/help!
View 8 Replies
View Related
Nov 15, 2015
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?
View 3 Replies
View Related
Feb 9, 1999
Does anyone see any negative consequences to stopping and starting SQL Server nightly?
View 2 Replies
View Related
Sep 13, 2007
How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
Kev
View 1 Replies
View Related
Aug 23, 2002
I need some help. I'm running Win2000 w/SQL 2000 Active/Active cluster. I've started having problems where one node will fail over automatically every night. The times are different each night and the only thing that shows up in the log file is the SQL Server terminated due to stop request from the service control manager. I can't find any error to point me in any direction. Does anyone have any idea what could be causing this?
Thanks,
Jeff
View 1 Replies
View Related
Dec 10, 2005
I need some assistence here.I have two servers. One with MS SQL 2000 Standard edition and MSDE(MSdesktop engine).I need to sync my databases nightly, as a backup.Is there a cheap software that will do this? I don't want to justbackup the entire databases files b/c they are large and I'mtransfering them over DSL.I was thinking transaction logging, but I'm not sure. Is there a easyway to do this?I don't know how to accomplish this task.PLEASE HELP.
View 9 Replies
View Related
Feb 4, 2008
I run a nightly full backup of the databases and then back up the transaction log. After completing the full database backup and the transaction log, I then update all existing statistics.
Is this a good practice to do on a nightly basis? I drop and re-create indexes on all the databases once a week. Does updating the statistics really help?
View 3 Replies
View Related
Feb 2, 2007
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
View 1 Replies
View Related
Apr 11, 2007
2:30:03 AM SQL Server Scheduled Job 'Our Job Name' (0x888B36086CCCC34FB353B408DEBE0432) - Status: Failed - Invoked on: 2007-04-11 02:30:00 - Message: The job failed. The Job was invoked by Schedule 30 (Our Job Name). The last step to run was step 1 (Our Job Name).
Where do you begin to discover the cause of the crash?
View 6 Replies
View Related
Jul 18, 2000
Hi all,
Pardon me for asking a question that I know has been asked before. I need to develop a backup strategy for our SQL Server and I am looking for any help that anyone can offer including recommending good books for reading.
Thanks in advance,
Faustina
View 1 Replies
View Related
Oct 18, 2000
In SQL Server 6.5, Is it generally better to dump the
transaction log first, then the database or to dump
the database and then run a dump 'tranlog with truncate
only' option?
Or, is this more a matter of personal choice?
Toni
View 1 Replies
View Related
May 30, 2006
Hi guys.
I am currently developing a system thats stores exchange stats in a db. Since our customers are companies with 20 employees up to 5 000 there a a big difference in the volume of data needed to be stored.
We currently thinking of supplying a SQL Server Express DB to the small customers and suggest a SQL Server to the bigger.
But since I would like to use the same structure for both types of customers I wonder how should i design the storeage.
Since the could be from 500 records a day up to 20 000. There are quite simple recordes with only simple datatypes. about 15 fields with no more than 10 chars each, mostly 2.
Should i separate the data in diffrent tables for a week or a day etc.
Since I am only going to filter data on 1 or 2 fields the data will be easly indexed.
The reports generated will almost always only use 1-3 months of data, but historical reports have to be possible.
My question are ofcourse:
Whats the best solution for me?
Thanks in advance:)
/Johan Wendelstam
Sweden
View 10 Replies
View Related
Jul 23, 2005
I've recently inherited a position where I am responsible for the well-beingof some DBs.2 (much) more important than others.The current recovery model, from what I can tell, is to do a full db/logbackup overnight.This .bak file is then written to tape as well as saved on the disk for 2days.Both these dbs are used fairly extensively 8-5pm and losing data would notbe good.The db sizes are approx 5gb and 3gb.This doesn't seem like the ideal situation to me. Everything I read tellsme... full backup periodically, differential nightly and transaction hourly.Agreed?If so then I have 2 questions:1. Is the best way to do this via a maintenance plan or by scripting andscheduling?2. What, if any, overhead can be expected with regular transaction backupsduring work hours?A bit of a pointer to #1 would be appreciated also.Thanks.
View 4 Replies
View Related
Jun 12, 2006
I have a bit of a problem with regards an indexing strategy. Well,basically there is no indexing strategy on a set of data I have atwork. Now, I didn't create the design as I would have allowed for this.OK, so there is primary key (clustered) indexes (mainly compositekeys), but no other indexes on the tables. As you would expect, theperformance leaves a lot to be desired. A hell of a lot. We haveseveral million rows in a lot of the tables. None of the queries seemto be overly complex so we can work through the applications at a laterstage.We have multiple copies (one per client per country) of the samestructure (I may have considered combining these to allow betterperformance). One specific SP that I need to run takes 40+ hourswithout indexes and 5 hours with some (130) basic indexes to get usstarted on a better design. These 130 indexes are the minimum I suspectwe need and from there, we can start to work out which ones we need.Now the test database (our worst performer) doubles in size to 20Gb,but the performance is much much better (as expected). The originalthinking behind the design was for storage concerns (server spacerecently upgraded) and for performance with bulk inserts.We have a lot of bulk inserts, but I suspect that these are not toobad, and the time taken for indexing these is negligable due to theperformance gains. I strongly suspect that we have a considerableamount of table scans going on, but the problem is that people heredon't understand indexing (yet) or have the time (probably because it'sall taken up using the poorly designed system). That's a whole seperateissue for me to address.So, finally getting round to my questions...Is there any good reference explaining in Layman's terms why you needbasic (or advanced) indexing ? Any links would be appreciated. I needthis to help explain to colleagues why a disk space increase andindexing will be far better than spending thousands on a new box anddoing the same (a common problem I suspect).How can I accurately estimate the amount of time taken to update anindex once data is bulk inserted. I don't want to re-index from scratchas this may take too long. Indexing my database first time round takesabout 1 hour 30 minutes.It's all part of an ongoing bit of digging into the system and re-doingit to make it work properly. I'm sure most of you will have been thereat some point or another.ThanksRyan
View 7 Replies
View Related
May 23, 2007
We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea ? or better option would be weekly full & daily incrementatl ?
Do we need to do any special backup on system databases or transactional logs ?
Please advice
View 30 Replies
View Related
Nov 1, 2007
Hi
I have concern about an sql server. The server has the operating system and sql server installed locally. The databases and transaction log files is stored on SAN. We used to have the database backup and transaction log backups stored locally on the server. We tape the database backup and transaction logs every 24h. If we lose the san and the server then we are stucked with no backup easily accessible. And on tape we loose of 24h data. We decided to put up stand alone server with no connection to the san and dump the backups file on this server. We also put a secondary server sql I case of emergency, to test backups and We are looking at the getting a mirroring or log shipping solution but we are not there yet, next year€™s budget. We still using some old server left from migrating to virtualization.
Then I read the €œPractical Troubleshooting The Database Engine book€? best practice not to avoid net work drive backups. Stuck aging. Back with the backup to local drive and robocopy them? Keep them on the network drive, start using? MIRROR TO in the BACKUP DATABASE? Today we are using the Backup Database Task in the SSIS.
Advice?
Regards
Johan
View 4 Replies
View Related
May 21, 2007
I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.
Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.
*** TASK 1 ***
Backup transaction log
/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/
BACKUP LOG [DSS]
TO DISK = N'g:mssqlackuplogdss_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
*** TASK 2 ***
/* This script shrinks the DSS database transaction log file
*/
BACKUP LOG [DSS] with truncate_only
dbcc shrinkfile(DSS_log)
**** TASK 3 ****
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH FILE = 1
GO
*** TASK 4 ***
Update statistics on the DSS database
View 3 Replies
View Related
Apr 23, 2008
Currently I have 1 server running MSSQL 2005 Standard. There is no redundancy in my current solution. I'm working on a project that involves a separate installation. I have 3 goals:
1) Provide redundancy for the new installation
2) Provide high availability for the new installation
3) Provide reduncancy for the current installation
Here's what I'm thinking about doing:
1) Purchase 2 servers and a Dell MD3000i ISCSI storage box. Cluster the servers and install SQL Server on the cluster.
2) Install SQL Server on an existing box (single CPU license) for backup purposes. Enable log shipping from the new cluster to the backup server.
3) Enable log shipping from the existing installation to the backup box.
I've also though about offloading some of the static pricing operations to the backup installation. This would free up my existing installation to deal with the changing data.
A couple of questions:
-How does this strategy sound? Are there any configuration problems with it?
-Can I legally use a development edition for the backup installation (if all I do is log shipping to it)?
Any other comments are welcome.
Thanks!
Brian
View 14 Replies
View Related
Jun 15, 2007
This may seem like a silly question, but has anyone ever heard of a DBA or an Engineer deciding to not back up databases inside EM, and only relying on the RAID or third party software for redundancy?
SBS 2003 R2
SQL 2000
Veritas 8.6 open file agent, SQL agent, Exchange agent
Thanks for any input,
Rich
View 3 Replies
View Related
Mar 2, 2007
Hi,
In my current organisation they are using SQL Server.
They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.
However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.
Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?
My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.
Any suggestions please ?
Constantijn Enders
View 5 Replies
View Related
Apr 22, 2007
I have been developing a genealogy application using a SQL Server 2000 database and ASP .NET 2.0. In this application a process, Ged.Parse, converts data from the GEDCOM standard format (a heirachical file format that looks as if it was designed for 80-column cards) into my SQL Server database.
As we started to load reasonable quantities of data into the system we found that the on-line response became abysmal. This problem was fixed by defining a number of secondary indexes (response times dropped to under a second, from previously exceeding 2 minutes and often timing out). Unfortunately however the processing time of Ged.Parse then tripled, and it may now take up to an hour to process a GEDCOM. I believe that this is a byproduct of defining several indexes that are not needed by Ged.Parse itself, but which are of course maintained as Ged.Parse inserts new records into the database.
I am wondering what my best strategy is, apart from putting Ged.Parse into a background task and just letting it trickle away. (I will probably do this anyway). What I'd like to be able to do is to have Ged.Parse load records without creating the secondary indexes, and then create the indexes for the newly-added records as a penultimate step just before it makes them available for general use. Of course there is no way that you can do this: records in a table are either indexed or they are not.
Proposed change: recode Ged.Parse to load data into temporary tables, say NewPeople, NewFacts, etc., with these tables having only the indexes required by Ged.Parse. Then, as the last process in Ged.Parse run a SQL procedure with code like: - Insert into People Select * From NewPeople Delete from NewPeople etc
This is a reasonable amount of programming, so before I make this change could somebody tell me: will this be significantly faster overall, or is this likely to make little or no improvement compared to the present process in which Ged.Parse loads data directly into People, Facts, etc? Two facts that may influence the answer. First, all record relationships are through GUIDs, so records in NewPeople, NewFacts, etc would already have their final key values. Second: although Ged.Parse needs to form relationships between records, these relationships are only within the new records (created from the same GEDCOM), and Ged.Parse does not need to relate any of these new records to earlier records.
Thank you,
Robert Barnes.
View 2 Replies
View Related
Nov 27, 2000
Hi!
It would be very nice if some people out there, using
merge replication can tell me their strategy to get
started with a new subscriber. Is it a good way to copy
a publisher backup to the new subscriber and restore it
there and say that schema and data is already here when
creating the subscription?
I experienced some troubles when I tried to add a new
subscriber and used the initial snapshot transfer to
get the db to the new sub. (no defaults are transferred...???)
Or is it even better to use DTS?
I'd also be very grateful forinformations about RESCUE
STRATEGIES in case of a major database problem of replicated
databases!
Hoping for some answers. TIA & Best Regards
Gert
View 1 Replies
View Related
May 4, 1999
Hi All,
Can any one help me with this..
I've a critical application that can't be stopped for a second.
I'd like to have an implementation that uses 2 sql 6.5 servers one as standby and
which is ready and up to date to take place and run instead of the master
server when it's down and when the master is back to work it's updated with the
data entered to the standby.
This process must be automatically to maximum extent.
Thanks
Mohamed
View 1 Replies
View Related
Oct 20, 2006
Hi Folks,
The Need : Refresh a part of local database daily from remote server.
Assumption : All updates in remote are updated in local db as well.
Need inputs on the type of strategy
1) Take full backup of remote, refresh on local
( Downside for us is Network and disk space )
2) DTS ( refresh only the objects required )
Looks good to us but does it take care of my assumption ?? Your suggestions welcome .. I may be wrong
3) replication ( Dont want it implement on the already complicated sceanrio ... so I'll pass)
4) Standy databases ( ??? Any help on this)
5) Any other
Thanks so much,
Warm Regards,
Ranjit.
--------------------------------------------------------------
The best moments of my life are often things I get paid for
View 3 Replies
View Related
Jun 14, 2007
My maintenance plans are starting to acting weird. I'm building a custom script to manage the database backups on my server, but curious if anybody has some sample work that will allow me to avoid re-inventing the wheel.
A couple of primary constraints:
I want to do a full backup daily (and only retain 1 day of full backups)
Transaction Log backups every 20 minutes
I'd like to loop through the databases on the server automatically to make this a little more flexible.
You have anything you'd like to share? Or, bits of knowledge worth sharing?
Please advise,
alex8675
View 3 Replies
View Related
Apr 18, 2008
Hi,
I have just created a logging table that I anticipate to have 10's of millions of rows (maybe 100's of millions eventually).
Basically its a very basic, narrow table, we are using it to log hits on images for a webserver.
My question is that we want to run queries that show how many rows are shown per day etc, however we want to make sure these queries which we are anticipating to be very heavy, do not slow down the system.
I have been recommended to have a seperate database (mirror/replica) for reporting so that the performance of regular activity will not be affected.
I assume this means I would need another server for this other database?
I am thinking there are probably some alternative solutions to this as well. Getting a dedicated server just for these queries really isnt an option.
In order to improvement it is not a problem to make some sacrifices. For example, having the data update every 15 minutes is more than acceptable.
I see certain websites I use employ this strategy of making data update every 15 minutes, but I am unsure what is likely going on behind the scenes. Also the queries are lightening fast when run. I am thinking that they have some sort of table that is populated with some computed data, so its quick to query.
Any thoughts or suggestions to give me some direction, are very much appreciated !
thanks once again,
Mike123
View 12 Replies
View Related
