Will This Backup Work?
Apr 26, 2006
Currently we are running a full backup on Fridays and differentials monday through thursday. With the Logs being truncated. we are using Veritas 10d with a 5 tape auto loader.
As far as recovery goes will this be sufficient?
we have about 40 users doing normal work hours.
My main concern is the transaction logs and making sure that I will be able to recover my data.
also we do not have an open file option and cannot do backups during the day, only at night.
Thanks in advance :D
View 6 Replies
ADVERTISEMENT
Aug 15, 2000
Backup Log ..... with no_truncate -- How to get it to work.
Applicable to SQL Server 7.0 Date 8/14/00 andrews_gary_w@solarturbines.com
Introduction to the "Backup Log
.with no_truncate" feature of SQL Server 7.0.
On occasion a database may become corrupt or a database file may be inaccessible. Ideally, in these cases you want to be able to restore and recover the data back to the current point in time.
This requires that the Transaction Log be backed up prior to the restore process beginning.
Microsoft has provided the "Backup Log
with no_truncate" feature for use in cases such as this. However, this feature works only under very specific conditions and thus is the topic of this document.
Background.
When a database is defined, it always has a primary file group. In some cases this may be the only file group defined for the database. The primary filegroup contains, at a minimum, the system tables for the database being defined. The systems tables hold metadata describing the database. Where no other filegroups have been defined, the primary file group will also contain the user defined tables/indexes.
Essential requirement
"Backup Log
with no_truncate" must be able to access the "metadata" located in the primary file group, or it will not work. This also means that this part of the database must not be corrupt or that the file containing this part of the database must not be inaccessible.
Specific conditions required for "Backup Log
.. with no_truncate" to work.
a. The database needs to be defined so that the user tables/indexes are contained in one or more file groups other than the primary filegroup.
b. Logging of changes to the database must be in effect.
c. A full backup must exist
d. Changes are made (logged) to the user database.
e. No non-logged operations must have been performed since the last full backup.
f. The primary filegroup file(s) must be available.
g. The primary filegroup data must not be corrupted. Or at least the portion needed by the backup operation must not be corrupted. I am not 100% sure about this issue.
h. The data in the filegroups other than primary may be corrupted or these files may be completely missing.
Process to restore/recover.
a. When problem detected (data file(s) inaccessible or database is corrupted), if primary filegroup is accessible, then perform the Backup Log
. with no_truncate.
b. Then perform the restore process using the "with replace" option for the database. This will get back any missing file(s) and recover you right up to when the last log backup was taken.
c.Post restore/recover process, your database will not be corrupt or missing any data file(s)
Bottom line
To achieve maximum recoverability, the following is recommended.
a. Define databases so that user data is not put in the primary file group.
i.e. Define not only a primary filegroup, but also one or more "other" filegroups.
b. Place user data in the "other" filegroup(s), NOT in the primary filegroup.
c. Place the primary filegroup on a disk drive separate from the "other" filegroup(s).
d. Place the log on a disk drive by itself.
Under the above scenario, at least three different disk drives would be needed to accommodate a single database.
Some sources recommend you put the primary file group and the transaction log on the same disk drive (mirrored). This would of course reduce the maximum number of disks needed to two. This topic, from my perspective, is another issue to be separately addressed and thus is not covered any further in this document.
Remember, even if the database files are isolated as recommended above:
a. Should the primary filegroup file(s) be inaccessible, then the Backup Log
. with no_truncate option will not work.
b. Should the primary filegroup file(s) be corrupt, then depending on where the corruption is determines whether or not the Backup Log
.. with no_truncate option will work.
View 2 Replies
View Related
Nov 4, 1999
We have Dell Optiplex GX1 PCs with Seagate ST22000 IDE tape backup devices, running NT Workstation or NT Server and SQL 7.0 with SP1, NTSP5.
SQL 6.5 will backup successfully to these devices, but SQL 7.0 will not.
The most common error message is 18257, Device or media does not support ...
42000 Syntax error, permission violation, or other nonspecific error...
View 3 Replies
View Related
Aug 8, 2000
I have been unsuccessful in getting the Backup Log with
No Truncate option to work.
Any tips, tricks, suggestions, etc., would be appreciated.
Thanks!!!
View 2 Replies
View Related
Mar 22, 2004
i know this topic has been covered and i've searched but i can't find the answer.
-SQL server is a diffrent server than my backup server
-i've installed the sql agent option on the backup exec server
-i've installed the client network utility on the remote server
the backup server doesn't see the SQL server databases. any thoughts
could it be a security issue?
thanks!
View 11 Replies
View Related
Apr 13, 2004
Hello everybody,
I have run into some problems with logfiles and backup logfiles (MS SQL server). I have read much about them but uptil now I dont seem to grasp how it works. Specially the part of working with backup log files.
Can someone please help me with this?
What I want todo is to create backup log files, so I can restore the database to any point in time before a failure occured. But using this method create's a problem with the backup log files. They get very big.
So how can I use backup log files without getting to large backup log files?
How can I shrink on a regular basis the backup log files and still be able to restore the database?
Please help.
Best regards,
Marc Janssen,
:)
View 3 Replies
View Related
Jul 20, 2005
Hello,I have run into some problems with logfiles and backup logfiles (MSSQL server). I have read much about them but uptil now I dont seem tograsp how it works. Specially the part of working with backup logfiles.Can someone please help me with this?What I want todo is to create backup log files, so I can restore thedatabase to any point in time before a failure occured. But using thismethod create's a problem with the backup log files. They get verybig.So how can I use backup log files without getting to large backup logfiles?How can I shrink on a regular basis the backup log files and still beable to restore the database?Please help.Best regards,Marc Janssen,Join Bytes!:)
View 4 Replies
View Related
Sep 6, 2005
I am running the latest MSDE with the SQL Server 2000 client tools on
Windows 2K Server on my production server. I have a simliar setup on my
development machine except it is running Windows 2K (non-server). I
have the same issue on both machines (I am wondering if it is related
to the structure of the database). When I open Enterprise Manager,
connect to the server, navigate to the Management folder, and add a new
management plan, I experience the following error.
I create a management plan that includes nothing but a complete
database backup (i.e. no reorganization of index pages, logging, etc.).
I set the backup schedule to occur every Sun at 12:00AM and hit OK.
Sometimes I get the following error :"Error 8114: Error converting data
type int to tinyint" sometimes not. Either way, though, the same thing
happens, the schedule for the backup is not saved. If I reopen the
management plan and go to the "Complete Backup" tab, there is no
schedule in the schedule box.
I have found this link:http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there is
no resolution there. I was wondering if anyone had any ideas.
Jason
View 5 Replies
View Related
May 6, 2006
Hello
I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures.
I get the following error:
Could not find stored procedure 'xxx'.
If I use enterprise manager and go to the procedures tab the procedure appears there.
Any ideas what is up?
PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem?
Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
I am doing a complete backup on a sql 7 db and then doing a completerestore (with overwrite existing db) on a sql 2000 server. This is nowour hot standby server. I have the process automated and it worksgreat. The only problem I have now is the logins dont work.I have tried running EXEC sp_change_users_login 'Report', and thelogins appear.However, when I run EXEC sp_helplogins 'joe', the results are empty.So, I am guessing all I need is a sp that will re-associate my loginswith the correct db and grant the appropriate permissions.If anyone has any ideas that would be great.I have also considered doing a log ship instead of a full backup andrestore. Does anyone have any suggections or good examples of how tomake that happen?Thanx
View 1 Replies
View Related
Aug 1, 2007
Hello all,
I need help concerning a differential backup on a MSDB database.
I received this message when I tried to backup it
Log from Windows
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=GlobalData Protector_(DEFAULT)_msdb_14_00_21.
Log from DataProtector
Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;
[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
Thank you for your help
View 7 Replies
View Related
May 7, 2008
Hello there,
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work=======================
I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work=======================
Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData".
Why can't Express see the directory on the D: partition???
What is wrong? Here? Can anybody help me?
View 5 Replies
View Related
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
View 3 Replies
View Related
Aug 23, 2013
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
View 9 Replies
View Related
Feb 18, 2015
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
View 3 Replies
View Related
Feb 9, 2004
Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. Yap
View 2 Replies
View Related
Feb 19, 2015
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
View 2 Replies
View Related
Aug 3, 2015
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
View 8 Replies
View Related
Dec 28, 2007
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
View 4 Replies
View Related
Jul 15, 2015
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies
View Related
Mar 13, 2015
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
View 2 Replies
View Related
Jul 11, 2007
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
View 11 Replies
View Related
Dec 29, 2014
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies
View Related
Dec 5, 2006
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
View 3 Replies
View Related
Nov 16, 2015
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem.Β I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies
View Related
May 10, 2001
This is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
Jason
View 1 Replies
View Related
Dec 7, 2001
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
View 2 Replies
View Related
Nov 29, 2007
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc
View 2 Replies
View Related
Jul 19, 2007
Hi
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
View 3 Replies
View Related
Apr 15, 2008
I have only one tape drive.
My server is using the SQL server 2005 backup tool and arcserve backup tool to backup different files .
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use SQL server 2005 backup tool.
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use arcserve backup tool.
It seems that the tape device resource is held by softwares even the tape drive is not in use.
What should I do to make the tape drive shared by different softwares?
Regards,
Manuel
View 2 Replies
View Related
Sep 8, 2015
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
View 8 Replies
View Related
Aug 4, 2007
Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies
View Related
Aug 8, 2007
Hello,
I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time).
I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:
Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.
I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?
Or is it possible to schedule full backup to run if tran log backup fails?
Any advice will be much appreciated.
View 1 Replies
View Related
