Backup
Estore Strategy For Critical Issue
Jun 9, 2008
Hi
I have one query regarding backup
ecovery startegy
hi
I have Db with simple recovery model.. recently i updated my column without where clause now i need to recover all my original state of that column..i just have idea @ set DB recovery model to Full then take full back up and then take differantial back up and then take transactional log back up and restore it as full , diff and then most recent transactional log back up...but still i didnt get original state of that table column...
please guide me
T.I.A
View 13 Replies
ADVERTISEMENT
Jun 9, 2008
Hi,
I would like to have guide for backup
estore strategy for production DB..if i have to apply for high availability of production DB..
consider scenario ..Full backup every week, differential backups every 6 hours, and logs every fifteen minutes.
suppose my DB is crashed at 8:14 AM and my last logs were backed up at 8 AM then by looking above startegy then i lost my DB from 8 am to 8:14 am???? if not then where it shud find???
also looking above scenario by taking backup for such high frequency is it full my disk?? guide me pls...
T.I.A
View 4 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
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
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
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
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
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
Sep 27, 2006
Hello.
I have only ever been required to take a full back up of my main prod database every night.
Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.
I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:
Full backup @ 7pm
Backup tran log every hour after that starting @ 7am (as there are no changes overnight)
How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it? Basically I need to know what to do so it doesn't get too big!
Thanks
View 1 Replies
View Related
Aug 16, 2006
I tried searching, but it appears the search is still broken.Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).Backup our DB weekly w/ transaction logs.I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.Along with that I have some optimizations and shrinks I run. Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.So. Here in lay the questions.The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).So, I sorta get that..I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?I guess I'm a bit befuddled here.
View 14 Replies
View Related
Jul 20, 2005
Hi,Could anyone tell me the backup strategy for a 1000GB database?Thank you!Peter Wang*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 24, 2007
Real World: Backup Strategy and implementation, how?
A quote:
€œReal World:
Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:
//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups.
//servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups.
//servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups.
//servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.
Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?
I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.
Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.
I would like full details on this please, as I need to get my head around it.
Thanks
Neil
View 6 Replies
View Related
Dec 19, 2007
We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives.
We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.
One of them was taking the Symantec Log backups with Copy-Only option.
The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups
The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.
Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner.
But, I guess it is not easy to have a manual Restore Policy in place.
I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.
Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.
View 3 Replies
View Related
May 11, 2006
We currently use a split-mirror backup strategy for our Sybase database, which has a "quiesce database" command to suspend all transactions. By quiescing the database before splitting the mirror, we suspend all transactions to ensure we get a stable backup of the environment. It works very well for us and I'm trying to understand how we could implement this with our SQL Server 2005 DB.
(I'm aware of SQL Server mirroring and that there are other ways of possibly backing up the DB. In this post however, I'm only interested in how I would make the split-mirror strategy work if I wanted to pursue it. I'm trying to avoid paying for software that uses the VDI as it's quite costly.)
Can someone help me with how I would accomplish a split-mirror backup strategy in SQL Server 2005 (without using a vendor's software that uses the VDI)? I have to imagine there's something similar to the "quiesce database" command in SQL Server...
I appreciate the help!
View 3 Replies
View Related
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 Replies
View Related
Jun 1, 2007
I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
View 3 Replies
View Related
May 15, 2007
I have 32-BIT MS SQL 2005 running on Windows 2003 R2 platform. The code is 100% bug-free and works fine on staging and production. Production has an issue AT RANDOM TIMES. Most of the time, it works fine...connections are pooled and reused. Out of nowhere (very randomly), it will start opening new connections for each request and keep doing that until the DB server crashes (could not open connection exception). If I restart IIS, it works fine again...all connections are being reused (no more than 6 connections). Just for fun, I restarted IIS again....it starts opening new connections for each request!! I restarted again, it now reuses existing connections. What's going on??? This has occurred 2 times on our production box. .NET ALSO has a SERIOUS bug where if you nest master pages or user controls, sometimes it will throw a compilation error on a LIVE site (microsoft admits that it's a bug in the engine and currently...there is NO FIX for it..there are patches...none of them work).
View 3 Replies
View Related
Jan 13, 1999
I get this error when I try to access one of my tables.
Msg 605, Level 21, State 1
Attempt to fetch logical page 4377 in database 'maillist' belongs to object '1340531809', not to object 'client'.
I know that when my database is restarted it will be marked suspect because of this error.
Does anybody know:
What causes this error?
Why I keep on getting it?
How do you fix it?
Is anybody even reading this?
View 1 Replies
View Related
Nov 9, 2007
Greetings,
I am having a big issue now. I made what I thought would be a simple change to our reporting services application, which has been running smoothly for about 2 years. The change has caused my "forms authentication" to start throwing an error that I rember from a while back while testing. Our rporting server is down, sad to say. Has anyone had this problem.
The error : Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:...
Changes made
1. Stoped RS web app in IIS
2. Changed the ASP.NET Config Settings(Report Server App)
a. Authentication Tab - cookie time out (changed to 60)
b. State Manegement Tab - Session Time Out (changed to 60)
3. Changed the ASP.NET Config Settings(Our aps.net app that displays the reports)
a. Authentication Tab - cookie time out (changed to 60)
b. State Manegement Tab - Session Time Out (changed to 60)
4. Changed the SessionState Timeout property in the web config file in our web app that displays the reports.
5. Restarted the Report Server app.
6. Recycled the app pools
Could not log in!
I am catching the above error in my web app's ReportExecution.LogonUser().
I rebooted the server and continue to get sqldump logs and this exception. This error occured when I was testing the redirect a while back but that issue was resolved. The only changes I have made are above.....
View 3 Replies
View Related
Apr 16, 2008
hi,
i had a query like this ,
source flat file containg row having length 803, frm that i parse the fields of diff lengths ,record type field is one of them,
after flat file i took derived column to parse the record types(i.e type1,type2, .......)
later i took conditional split and split those record types in to diff derived columns,
problem starts now ?
i got 500 records before conditional split and after condi..split.. they r 499,
i dont know wat happened to that 1 record.
plz help me regarding this issue........
View 5 Replies
View Related
Jun 14, 2007
Hi all of you,
Does anyone ever face this sort of error? We were launching a SSIS from our .Net console:
0x80070002 while loading package file "C:Documents and Settingsadminsql2k5Local SettingsApplication DataMicrosoftSQL ServerSmoInnerPackage.dtsx". The system cannot find the file specified.
InnerPackage.dtsx is a sort of template for SSIS??
Thanks indeed,
View 6 Replies
View Related
Nov 6, 2007
Dear all
I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?
Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?
Any advice is apreciated.
Sincerely
View 1 Replies
View Related
May 12, 2008
Publc role has been grannted 'SELECT' privelege to syslogins and sysusers tables in the Master and GTSS database
The syslogins table contains all the logins that were created on the server. The sysusers table contains the users that are mapped to the database. Unauthorised access to these tables would reveal critical authentication info of other users
Restrictive permissions should be configured on critical database tables such as sysusers and syslogin.
How to set this.
View 1 Replies
View Related
Feb 12, 2008
Our SQL server keeps crashing with the following error. When it crashed it completely shut down the server. Could you please give me advice on how to stop this from happening again? I would like to thank in advance for your help.
A MS DTC component has encountered an internal error. The process is being terminated. Error Specifics: A non-MS DTC XA Library threw an exception in function olog
ntdll!KiFastSystemCallRet + 0x0
+ 0xd58c3c0
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Here is the information of our server
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SQL2387
System Manufacturer Dell Computer Corporation
System Model PowerEdge 2850
System Type X86-based PC
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
BIOS Version/Date Dell Computer Corporation A04, 9/22/2005
SMBIOS Version 2.3
Windows Directory C:WINDOWS
System Directory C:WINDOWSsystem32
Boot Device DeviceHarddiskDmVolumesSQL2387Dg0Volume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone Eastern Standard Time
Total Physical Memory 4,095.08 MB
Available Physical Memory 1.75 GB
Total Virtual Memory 1.83 GB
Available Virtual Memory 3.81 GB
Page File Space 2.00 GB
Page File C:pagefile.sys
View 1 Replies
View Related
Aug 11, 2006
Hi all of you,
This post is related with this one, previously opened:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620243&SiteID=1
Moreover, this post is related too (IMHO) with
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=430406&SiteID=17
Following with my research I've seen that DTS.DLL is installed on:
1)My root c:
2)%systemroot%system32
2)c:archivos de programa...90
3)c:archivos de programa...80
It seems that BIDS is trying to call, at first, to this COM CCW component.
So that, all has been deleted phsyically from my workstation and unregistered.
Sql Server 2005 full installation again and... the problem lives yet.
I'm desperate
View 2 Replies
View Related
Oct 29, 2004
Hi Friends,
We developed an application in the ASP.Net with C#.
We are going to do a task in the transaction.
It is working perfectly. But if two users comes simultaneously, then
It gives an exception that Transaction is already commited.
First transaction Commited Successfully.
When Second transaction goes to Commit()
Then it generate this error.
We are unable to find this problem Whether this is problom from SQL Server or DOTNET.
Pls give ur suggestions.
Thanks and waiting a helpful response.
Sachin
View 3 Replies
View Related
Jun 7, 2002
We need to apply the transactional log for business hours (9-5) everyday to a oracle instance which will be seeded with SQL Data for once. And then every day the transactional log would be applied.
We need to do this as eventually this server is moving to oracle.
At this point if somebody could help me to read the transactional log that would be great.
Thanks,
View 1 Replies
View Related
Feb 4, 2004
Please any one can help me ?I have multiuser application ( VB 6.0 ) on SQL Server 2000 running.
Last night my Sql Server was writting info on the Log file of one database when the power accidentally shut down. In fact, the server was executing one Microsoft Data Transformation Service ( DTS ) which transfers data from one table to another.
This morning when the users tried to work with this database everything went wrong... I've not been able to attach MDF AND LDF files to SQL Server 2000 Desktop Engine.
Looking for what happened I found those Info/Error LOG events:
A)Starting up database 'pcserveis'.
B)ex_raise2: Exception raised, major=34, minor=48, severity=21, attempting to create symptom dump
C) "The transaction ( Process Id 5 ) was left in "interblockade" in Lock
resources with another process and was chosen as subject of the "interblockade". Execute the transaction again." ( Sorry about wrong words , i'm using a translation tool from spanish )
D) Could not undo log record (5019077:221:133), for transaction ID (0:15254626), on page (1:1917), database 'pcserveis' (database ID 11). Page information: LSN = (5019072:426:5), type = 1. Log information: OpCode = 2, context 1.. ( Error: 3448, Severity: 21, State: 1)
E)Error while undoing logged operation in database 'pcserveis'. Error at log record ID (5019077:221:133).. (Error: 9004, Severity: 23, State: 7)
F)An error occurred while processing the log for database 'pcserveis'..
What can i do to restore my database? Thanks
View 4 Replies
View Related
Apr 10, 2007
Hi,
I'm upgrading a new dedicated sql server 2005 (64 bit) box that I just got to SP2. After I install SP2 there is 1 critical update.
Critical Update for SQL Server 2005 Service Pack 2 (KB 934458)
When I install this, it says the installation has failed. I have uninstalled it and tryed reinstalling but I get the same result.
It's listed in the control panel as a program I can remove, however in windows update history it says it has failed. Also in windows update I no longer have the option to install it again, as though it has installed properly.
Anybody have these issues before? I'm not sure if its installed OK or not.
Thanks!
mike123
View 1 Replies
View Related
