SQL7, sp3
I run a backup each night via a job, using a backup set. The question/concern is: the timestamp on the physical backup set file doesn't not change but SQL says in the history of the job that the backup is successful. I checked the system time on the server and it's current, it's todays date, but again last night's backup says May22. Should this be cause for alarm? Bug??
thanks,
I have a series of time from 08:10 till 00:55 and a single date in a seperate column. What I need to have is associate the date with each time from 08:10 and when it comes after 23:55 the date needs to be incremented to the next day.
something like below
29Jun2015 08:10
29Jun2015 08:15
29Jun2015 08:20
29Jun2015 08:25
29Jun2015 08:30
29Jun2015 08:35
29Jun2015 08:40
.....
.....
....
....
29Jun2015 23:55
30Jun2015 00:00
30Jun2015 00:05
30Jun2015 00:10
30Jun2015 00:15
30Jun2015 00:20
I tried to add the based on the datepart minute however its getting added only to 00:00 and not after that.
I'm experiencing a weird problem with log shipping in SQL 2005.
I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.
The the data below extracted from the backup history:
2007-06-23 17:30:00.000 D:BackupDatabasesmydbmydb_20070623073000.trn
2007-06-23 17:15:00.000 D:BackupDatabasesmydbmydb_20070623071500.trn
2007-06-23 17:00:00.000 D:BackupDatabasesmydbmydb_20070623070000.trn
2007-06-23 16:45:00.000 D:BackupDatabasesmydbmydb_20070623064500.trn
My timezone here is GMT+10.
Although it's not affecting Log Shipping, it's very confusing as the full backups have a timestamp in the local timezone!
Has anyone seen experienced something similar to this? Please see below my SQL details:
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT AMD64
5 Comments NULL NT AMD64
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 4 f
18 ProcessorType 8664 NULL
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID NULL NULL
Thanks,
André
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View RelatedI have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
I am reading about the RESTORE command to a point in time using logs, I would like to know the minimum point in time recovery for a backup image using T-SQL command before applying a log restore and what are the log ranges needed for the restore during restore.
 My Version 2008 R2
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
date       time        s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date       time        s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                 3
6/9/2015 11:56:26 TestSite 900                  2
6/12/2015 12:56:26 TestSite 700                  1
I am going to schedule the back up sql server database, i want to know how long it will finish. how can i check the total time. Thanks.
View 1 Replies View RelatedHello, I am new to the forum and hopefully someone can help me with the
problem I have. Lately, I noticed that my sql statements are timing out
during the time SQL server is running backup job. The errors I am
seeing in the log are:
Process 59:0 (724) UMS Context 0x06403BC0 appears to be non-yielding on Scheduler 1.
Error: 17883, Severity: 1, State: 0
I am running SQL Server 2000, Enterprise (SP4)
I believe this problem should have been fixed in SP3a. I even tried
rolling back to SP3a, but still experience the same problem. Does
anyone else have experienced this issue?
Thanks.
Hi Everybody:
We plan to do point-in-time recovery for certain databases. We plan to do Complete Database Backup every night and transaction log backup every two hours from 8:00 AM to 5:00 PM. I have following questions regarding the log backup.
1. There are two type of backup 'Append to media' or 'Overwrite'. If I choose 'Append' for log backup, is that mean I only need to restore database against last log backup file because all previous log backups have been accumulated there?
2. Can I automatically truncate log after the backup is done? How I can do it?
Thank you very much.
Joan
Hi guys.
I am having trouble in time issues while backuping my database.
My database size is around 50GB. It is taking around 5hrs.
Is there any way to reduce the 5 hr backup time to 3 or less.
Thanks in advance
MAK
I do hourly transaction log backups at 9,10, 11 etc...
When I restore from a 9:00 backup I clearly see changes that I made after 9:00 am!!!
I then noticed when I go to my scheduled backups that a 10 am backup was indeed done
but in the "restore from device" tab it says the last backup was at 9 am.
Apparently it is not showing the actual latest backup that was done. This explains why
when restoring from a 9am backup I am seeing changes after 9, because in reality
I am restoring from a 10 am backup!
Is this a bug? I am running on 6.5 sp 5a.
thanks in advance....Mary
How much time does a 20 g tape backup take to recovery
I am on 6.5 sql and have compacq 15/30 gb DLT
How best to configure a standby sqk 2000 server
salim
__________________
I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc.
If I run 'BACKUP DATABASE xxx TO DISK = 'D:DB_Backupsxxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?
Any ideas would be much appreciated.
Hello. Have a strange one here. I'm backing up databases locally to a driveon the server daily. I needed to change the time of the backup, and didthat, but it's still occuring at the old time.I deleted the old job, and recreated it with the new time. I won't knowuntil tonight at 12:00 whether that worked or not.Can someone tell me where it saves the maintenance schedules and how itkicks it off? I'd like to take a look at it to see if it's still there.I'm a little bit of a newbie on SQL server, so be gentle. :-)--If responding to me directly, please take out "REMOVE" from my e-mailaddress.Thanks!Doug
View 2 Replies View RelatedSQL Server 2008 R2
I don't see a "general discussion" thread and this is the closest i think.
I just have a general question: if my backup window is from 8am to 10am, and i do a restore within that backup window, what will happen? assuming we're talking only of a single database, ACME.
I'm comparing because with Oracle RMAN, it pukes when i do a restore while the backup is going on. it would complain of unable to find some archive logs.
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Thanks,
Parallon
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
View 3 Replies View RelatedWe're considering purchasing an application,which stores some data in the filesystem, andsome data (meta data and links to files) inMS-SQL.We need to be able to create a backup whereinthe database and files are "in synch" (in other words,in a consistent state). We need to maintain the"referential integrity" between database and files.What I'm thinking of is something like this:1. stop the application server2. set database to readonly (flush updatesfrom cache to disk)3. backup database to disk files4. back up application files and backup files.(We could use "snapshot" capabilities of our diskhardware, so that the backup could run against thesnapshot; that would minimize downtime.)4. set database to read_write; restart app serverRecovery scenario would be:1. Stop application server.2. restore application files and database backup files3. restore database from disk backup4. restart application serverIs there a simpler way to get a "point in time" backupof application files and the database?
View 1 Replies View RelatedGreetings, All -
Is it possible to restore to a point in time without a preexisting full backup? The situation is this:
I have a table in the DB from which an unknown number of records were accidentally deleted. The table in questio has about 2 million records; the user ran a query to delete all records from the table by accident, and cancelled the query after about 3 seconds.
The DB recovery mode is full, so I should be able to do a point in time restore to go back to just before the deletion, but unfortunately, the DB has never been backed up, so I have no backup to work from.
The DB has not been used since the incident, and is otherwise operational, but I need to recover these records if at all possible. All the instructions I've seen for this involve restoring from a full backup first, then restoring the log backup second. Is there any way for me to accomplish the same task?
Mark Faulcon
Hello,
I'm trying to figure out why my transaction log backup is taking up to an hour to complete. I started off with a full recovery model with a Full database back up every Sunday, differential backups every Tuesday/Thursday and log backups every 5 minutes. I would have thought that the log file backups would execute much quicker because I'm backing them up more often.
Here is my backup statement, I'm hoping I've got a wrong option that you can point out to me:
BACKUP LOG [xxxx] TO [LogFilexxxxBackups] WITH NOINIT , NOUNLOAD , NAME = N'xxxx log backup', SKIP , STATS = 10, NOFORMAT
Hi Guys,
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour
from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
Please advise.
Thanks,
Anu
We run a disk backup (full) early in the evening, and then another bull backup to tape later at night. We also keep independent backups of the logs.
It seems that any full backup "resets" the ability to apply logs when restoring?
I want to be able to restore a full backup, and apply logs forward possibly more than 1 day, in case a backup is corrupted and not usable. But it seems I can only apply logs up until the time of the next full backup.
Is this true, and is there a way around this?
If an application is reflecting timeout errors but there are no backups running, the network is fine and the data and log files are within normal parameters, what else could be the cause of the errors?
View 9 Replies View RelatedDatabase is OK. I just need to roll back all the transactions until certainpoint in time. How to do it?
View 2 Replies View RelatedFor starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
We have a 1TB Database. Our backup strategy looks like this.
Weekly Full backups - Saturday 10pm. (Takes anywhere between 5 - 9 hours)
twice Nightly Diffs (8:30p and 2:30am - Mid week this takes roughly 1.5 hours)
Hourly Log backups starting at 4:00am until 11pm.
I have an issue where I need to restore to 3:00 today. If my (2:30a)Diff is still running at that recovery point.
I am getting an error when trying to restore Full/2:30a Diff/4a Log with a stopat 3:00a.
Error telling me my log backup is incorrectly formed.
Can I, in fact, restore to this point at all? Do I need to go back to my previous Diff (8:30p) and restore the logs, if so, which ones?
My nighttime Diff ran from 8:30 - 10p.
I have logs at 9p, 10p, 11p, 4a
Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
how to insert a value into table where the fieldname uses a datatype timestamp.(asp.net2.0)
View 1 Replies View Related