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!
We've been experiencing some problems on some of our databases ever since we've updated our servers to SP5. Backup exec seems to hang up after reaching a small number of bytes and then goes no further. I've installed the new MDACS drivers but it hasn't solved the problem.
Any ideas would be greatly apreciated!
Thank you,
Hi everybody,
I have this problem I have been struggling with for more than an hour now, hoping for some help.
Using SQL Server 2005.
DECLARE @IPAddress nvarchar(15)
SET @IPAddress = '127.0.0.1'
DECLARE @UA nvarchar(300)
SET @UA = 'MSIE'
DECLARE @UserID int
SET @UserID = 123
EXEC sp_executesql N'User_LogonLog',N'@UserID int, @IPAddress nvarchar(15), @UA nvarchar(300)',@UserID,@IPAddress,@UA
It keeps given me the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'User_LogonLog'.
In my head it should be perfectly valid syntax. I have also read Mr. Sommarskog's nice guide http://www.sommarskog.se/dynamic_sql.html, but no luck either..
I have tried to remove datatypes in parameters declaration, the Unicdode N' and so... nu luck.
If I run the following (without sp_executesql) there is no problem, which indicates the User_LogonLog procedure is work perfectly fine.
EXEC User_LogonLog @UserID,@IPAddress,@UA
What am I missing?
Code:
exec('select RIGHT(00000 + CAST(dh.zipcode AS varchar(5)), 5) as zip, '+@fieldname +' as distance into #distance from sumplicity.dbo.t_distancetohospital dh')
This runs normally and returns the number of rows created. Yet when I do a 'select * from #distance' query, I get a message that #distance doesn't exist.
Where is it storing all of this and how do I access it?
Can anyone tell me how to RESTORE a Table, not an entire Database, to the live server.
Please Help
I've installed SQL Server 7.0 SP2 and Backup Exec 7.3.
When i try to start backup exec a dr. Watson error appears.
Backup exec doesn't start. The same effect with version 8.0 and 8.5 from Veritas Backup Exec. When i try to install MDAC 2.5 after installing Backup exec, error 90332 (?) appears. Backup exec "hangs".
When i've installed Backup exec 7.0 or 7.2 there are no problems.
(also with SQL Agent)
Does somebody know to reolve this?
Thanks! !
Does anyone want to give me a few glowing reviews for me to show my bosses? I would like to recommend this product, but the only place I am finding info (such as reviews)is tha seagate site, which may be more than a little biased.
View 3 Replies View RelatedHaving a problem with my backup database.
Tonight I got this error:
Msg 8921, Level 16, State 1, Line 3
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 3
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55555545). It occurred during a read of page (1:934) in database ID 12 at offset 0x0000000074c000 in file 'E:SomefolderSomedatabase.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Tried to restore from my local .bak file and it was giving me the same error and since I cant get my database back online I cant run the restore from tape. I did some online research and found this command:
EXEC sp_resetstatus 'BEDB';
ALTER DATABASE BEDB SET EMERGENCY
DBCC checkdb('BEDB')
ALTER DATABASE BEDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('BEDB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE BEDB SET MULTI_USER
Didnt correct the problem and produced the same error above.
This has happened once before and I rebuit the entire backup server. That seems to not "fix" the problem because it has happened again. Does anyone have a suggestion. Keep in mind that the storage for the database is on iSCSI if that means anything but I have 11 other databases (including a BES Database) that never have any issues.
OS: Windows 2003 Server Standard Edition R2 SP2
SQL: SQL 2005 Enterprise Edition SP2
Has anyone had any problems with Seagate Backup Exec and MS SQL Server? We have been having server "lockups" occur when the Seagate Backup
Exec does it's DBCC CheckDB before it backs up the databases. The server starts repeating "lazywriter" errors which take up all the server resources
to where we have to "reboot" the server to recover. Apparently this is a known problem (per Microsoft Tech. Support). Has anyone else experienced
this problem? and if so, how did you get around it or repair it?
Jim
We are having a problem with trying to backup the database device and log DAT files located in the MSSQLData directory.
The Seagate Backup Exec. states that the files are busy and skips them during its backup cycle. It skips all the devices in the
directory.
Any suggestions?
Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.
Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.
Message in Error Log: [264] An attempt was made to send an email when no email session has been established.
Send test Email does work on both Servers.
Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?
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
Hi All,
I got SQL agent backup failed problem which took me 2 days , but still couldn't figure out, so tired. Help please!!!
2 days ago,the database was using my domain account to logon both SQL server and SQL agent, the backup jobs worked good. Since I changed db to another domain account which has same permission as mine at both SQL server box and database, but almost all jobs got failed except "check integrity", and the error is "The job failed. The Job was invoked by User rose. The last step to run was step 1 (Step 1).". I checked the log files and backup report, could't get any clues, and I switch domain account back to mines, it works good . But my boss required me using another domain account , what should I do? HELP!!!
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.
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...
Can anyone describe step by step how we specify a backup plan for our database? For example to backup our db and execute every night.
Thanks
I have been unsuccessful in getting the Backup Log with
No Truncate option to work.
Any tips, tricks, suggestions, etc., would be appreciated.
Thanks!!!
This seems to happen just after our backups run. the result is that the SQL Server
Enterprise Manager can be brought up the next day but nothing can be accessed or
inquired of. The service is still running, the server is still up and rebooting the server
resolves the problem.
98/08/06 20:32:31.68 kernel dpdb_asyncread: mirror: Operating system error 6
(The handle is invalid.) encountered
Is anybody else using Arcserve Backup Agent for SQL Server for Windows NT, RAID 5?
Have you seen this? I`ve been to MS home page and on technet. No mention of this
problem. Next stop is Arcserve home page, if they have one. I have read the error logs
and this is the second time this has happened this week. Both times since we added
Arcserve to manage our MS SQL Server Database backups.
Best Regards and Thanks for your time.
please help me, i need to know if i need to purchase a sql agent for veritas backup exec. i am using the sql msde version only, not the full scale version. any help would be great.
View 4 Replies View Related
Hi,
If we were to assign permissions to a backup agent such as Backup Exec to backup the databases on the SQL server, what role would give the least amount but sufficient permissions to perform the backup? I know domain admin would make the agent a local admin and therefore allow it to back up the database but is there a role available to allow backup only?
Please note that I'm referring to a domain account used by Backup Exec to directly backup the databases rather than sql server agent.
Thanks.
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,
:)
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
Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @@error, but when you run the same stored procedure in a query window the error handling does get run?
The snippet of code looks like this:
SET XACT_ABORT OFF
SET ANSI_NULLS OFF
set ansi_warnings off
set arithabort on
declare @backup_status int
declare @cmd varchar(1024)
SET NOCOUNT ON
print 'Options value: ' + convert(varchar, @@options)
set @backup_status = 0
if (@backup_mode = 'F')
begin
BACKUP DATABASE
@DB TO DISK = @BackupFile
with
NAME = @Name,
DESCRIPTION = @Description,
MEDIANAME = @MediaName,
MEDIADESCRIPTION = @Description,
STATS = 10,
init, nounload, noskip
if @@error != 0
begin
set @backup_status = 1
print '**************** Backup of ' + @DB + ' Failed'
end end
When this is run in a query window, the print statement at the bottom does get executed, but when run as a step in a sql agent job it does not.
Thanks.
I want to schedule a database backup using SQL Server Agent Jobs how can i do this?
View 1 Replies View RelatedI 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
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.
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 RelatedHello 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
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?
I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
Hi All,
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
Any ideas would be gratefully received.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Any ideas???
Also this domain account is a member of
SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER
SQLServer2005MSAgentUser$ServerName$MSSQLSERVER