Backup Time
Aug 26, 2004I 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
ADVERTISEMENT
How To Find Point In Time Or Last Committed Time In Log Backup
Jun 25, 2015I 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
Timestamp Of Backup Is Different Than OS Time?
Jun 5, 2002SQL7, 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,
SQL Queries Time Out During Backup
Aug 2, 2006Hello, 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.
Log Backup In Point-in-time Recovery
Aug 10, 2000Hi 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
How To Reduce Time In Taking BACKUP
Dec 6, 1999Hi 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
Restore From Log Backup: Time Is Not Accurate
Jun 17, 1999I 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
Tape Backup Recovery Time
Jul 28, 2004How 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
__________________
Backup Data Retention Time?
Nov 12, 2007I 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.
CHANGED Backup Time, But Still Occuring
Jul 20, 2005Hello. 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 RelatedRecovery :: Backup And Restore At Same Time
Nov 23, 2015SQL 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.
Adding Date And Time To Backup File???
Oct 30, 2006Hello 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
Point In Time Backup (impossible For Some Points?)
Sep 21, 2006Hello,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 RelatedPoint-in-time Backup Of Database && Filesystem
Jul 20, 2005We'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 RelatedRestore To Point In Time WITHOUT Full Backup?
Oct 27, 2007Greetings, 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
Transaction Log Backup Taking A Long Time
Mar 30, 2007Hello,
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
URGENT !!!! Transaction Log Backup Running Longer Time
Dec 26, 2002Hi 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
In Restorc Can U Apply Logs Thru The Time Of Another Full Backup?
May 8, 2007We 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?
Application Reflecting Time Out Error - No Backup Running
Jun 23, 2015If 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 RelatedHow To Restore Database To Point In Time (no Full Backup)
Jul 23, 2005Database is OK. I just need to roll back all the transactions until certainpoint in time. How to do it?
View 2 Replies View RelatedSQL Server 2008 :: Restore To Point In Time DURING Differential Backup
Feb 25, 2015We 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
Creating Date Time Stamped Files Names For Backup File
Nov 13, 2007Hi 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,
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Aug 7, 2007
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.
Is Point In Time Recovery To A Point Before The Last Full Database Backup Possible?
Mar 26, 2008Hello 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
BACKUP LOG Cannot Be Performed Because There Is No Current Database Backup. BACKUP LOG Is Terminating Abnormally.
Jan 31, 2008
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
SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?
Apr 25, 2014Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?
Jun 11, 2015Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedIntegration Services :: Exclude Time In Date Time Variable In SSIS For Loop?
Oct 22, 2015I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
Transact SQL :: Update Time Portion Of DateTime Field With Time Parameter
Oct 3, 2015I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
It Takes A Long Time To Insert The First Record Each Time When The Program Start
Dec 15, 2006I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?
Apr 21, 2015SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time
Nov 12, 2007Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
DB Engine :: Time Testing Azure Point In Time Database Restores?
Sep 21, 2015I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies View Related