Backup Quits Working After Upgrade From SQL7 To SQL 2K
Apr 15, 2002
I had a number of jobs which were backing up system and user databases under SQL 7 using SQL ServerAgent. Over the weekend I upgraded SQL 7 to SQL 2K.
All backups work except for one, which gets the following error message: Executed as user: SANDIEGOSvcSQLServer. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
As best as I know SQLSTATE 42000 is an ODBC message indicating a syntax violation or an access violation. I have recreated the maintenance plan in an attempt to rule out a syntax violation. During the upgrade process I did not change the account under which SQL Server Agent runs, so it seems like it should not be an access violation.
Any ideas would be appreciated!!!
Thanks Gary Andrews Andrews_gary_w@solarturbines.com
View 1 Replies
ADVERTISEMENT
Sep 12, 2006
Hi
I have a job which is executed via SQL agent -it attempts to backup each database..
The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server
No errors are logged.. Where do I look next ?
The commands executed are as follows:
--------------------------------------------------
DECLARE @DB_Name varchar(32)
DECLARE @Backup_Path varchar(255)
DECLARE @Backup_Name varchar(255)
DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DB_Name <> 'tempdb' AND @DB_Name <> 'model'
BEGIN
print '--------------------------------<< ' + @db_name + ' >>--------------------------------'
SET @Backup_Path = N'C:sql2005backupsightly' + @DB_Name + 'Daily' + '.bak'
SET @Backup_Name = @DB_Name + N' backup'
BACKUP DATABASE @DB_Name TO DISK = @Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @DB_Name
END
CLOSE DB_cursor
DEALLOCATE DB_cursor
print 'Finished backing up'
View 4 Replies
View Related
Oct 12, 2001
I have a database on a 6.5 server but wish to upgrade this database to a sql 7 database. But I would like to test this first on another server which is a sql 7 server. Is it possible to restore a 6.5 backup to a SQL server 7.0 server?
Thanks in advance..
View 2 Replies
View Related
Jul 4, 2000
Hi all
I would like to know - we are currently running SQL6.0 on NT4 servers. We have 2 x 3rd party programs that runs on the servers, making use of the databases. We don't forsee any changes in the near future.
Question: will it be wise to upgrade to SQL7 anyways, or can we keep the status quo as is ???
Rgds
De Waal
View 1 Replies
View Related
Sep 27, 2001
Hi,
i'm brand new in the sql server world so maby this is an easy problem.
I have to get data from AS/400 to SQL7. I've created a package with a client acces odbc driver and an sql driver which transfers the data of an entire table. When i execute this it works fine.
Now i want to shedule this (shedule package). When i start this job it doesn't work.
This is the error i get:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 5 (5) Error string: [IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL]Communication link failure. COMM RC=0x5 Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
on the server i've installed client access as well and created the same odbc driver (with the same name). But it still doesn't work.
Can anybody help me please??
View 1 Replies
View Related
Jun 17, 2002
Does anyone know of a good whitepaper detailing SQL7 to SQL2000 migration?
Thanks, I really appreciate your help.
--Alex
View 1 Replies
View Related
Mar 17, 2001
Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
Thanks
View 1 Replies
View Related
Jul 14, 1999
I have a SQL6.5 sp5a, NT4 sp5, BDC, IE5 server that I have installed SQL7
on. My installation is set to mixed security.
Everytime I try to run the upgrade wizard I get the following error/problem
right after the "Checking 6.x syscomments for corruption and Verifying 6.x
data base logins etc.
Summary of warnings (if any) and choices:
=============================================
The following syscomments entries are invalid on your 6.x SQL Server. It is
recommended that you fix these problems before you continue.
=============================================
********************************
master
********************************
********************************
Inconsistency Report For Database: master.
********************************
dbo.sp_db_upgrade2
Procedure
Occurs 28 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_fallback_activate_svr_db
Procedure
Occurs three times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_password
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_recompile
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_rename
Procedure
Occurs 11 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
=============================================
The following script will be created as upgrade.ini and run into the script
interpreter when you press finish.
=============================================
[Options]
Method=1
Perform a CRC Check=0
Source=CCIDEV
Destination=CCIDEV
Export Server Startup Options=
Import Server Startup Options= -T1808
SQLServer70Path=C:MSSQL7SourcePassword=066ce2f9c0e 4ba
DestinationPassword=00
MasterPath=C:MSSQLDATAMASTER.DAT
VerswitchOldVer=65
Code Page=1252
Override Server Code Page=0
QuotedIdentifiers=0
ObjectDumpPath=c:mssql7upgradeCCIDEV_071399_163117 Ansi Nulls=0
[Tasks]
Export and Import via Named Pipe=0
Marking database upgrade status=0
Export Logins=0
Export Database Owners=0
Import Logins=0
Creating Databases=0
Setting Database Options=0
Export Database Objects=0
Import Database Objects=0
Export SQL Executive Settings=0
Preparing MSDB for Upgrade=0
Import SQL Executive Settings=0
Export Server Settings from Master=0
Modifying Scripts=0
Import Server Settings from Master=0
Preparing SQL-DMO for upgrade=0
[Databases]
006CCIDATA=0
003model=0
005msdb=0
001master=0
I don't find anything about this anywhere except the upgrading to SQL7 white
paper, it mentions that the text for objects must be intact in the
syscomments system table and that logins must exist for every database user,
but that doesn't really tell me how to fix this.
Has any one seen these errors or can point me
to where on Microsoft's site you can now EASILY get KB info for SQL Server
7?
View 2 Replies
View Related
Nov 17, 2000
I need to backup my sql7 through the network share.
I have two servers both have SQL7 and only one has a backup tape drive (which is my production database). I want to be able to backup to the tape and to the a share drive on the second server. The backup to the tape work fine but if I want to backup to share drive locate to the second server I can't see the share where I want to put the backup. Do you Know what I am doing wrong ?
Thanks.
Mtraore.
View 1 Replies
View Related
Nov 17, 2000
I need to backup my sql7 through the network share.
I have two servers both have SQL7 and only one has a backup tape drive (which is my production database). I want to be able to backup to the tape and to the a share drive on the second server. The backup to the tape work fine but if I want to backup to share drive locate to the second server I can't see the share where I want to put the backup. Do you Know what I am doing wrong ?
Thanks.
Mtraore.
View 2 Replies
View Related
May 10, 2001
Can we restore a SQL 2k Backup in SQL Server 7.0 database?
Will there be any issues on it?
View 1 Replies
View Related
Mar 29, 2000
I am trying to replicate a client's environment on my home development machine in order to develop some reports. At the client (SQL 6.5) I did a complete backup to a .dat file. How can I restore from this file into my home (SQL 7) server to create a duplicate database?? I tried restoring from a device, specified the file, but I get an "not a valid tape format ....." error. Help!
View 5 Replies
View Related
Jun 16, 2001
If I'm not mistaken, we can create a backup device OR backup files in SQL7. Can someone tell me which one is better?
View 1 Replies
View Related
Apr 4, 2007
Once a week a query is run against my SQL7 database. One month has passed and a backup of the database was restored. The database was restored as a seperate database from the active one.
The very same query was run against the backup, the results of the query were drastically different. What is worse the very same pattern that developed in the restored backup now seems to be affecting the active database.
Would restoring a database cause this sort of issue?
View 1 Replies
View Related
May 12, 2004
I have a sp which adds a record to a table. I come from a background of using MS Access which has a usefull datatype called Autonumber. There doesn't seem to be an equivalent of this in Sql Server. So my sp gets the biggest RecordNo and adds 1 to it.
@Declare @RecordNo int
Select @RecordNo = MAx([VehicleID]) +1 From Alto
This works fine so long as there is at least 1 record in the table. If the table is empty, my sp seems to just quit. I want @RecordNo to hold a value of 1 when the table is empty. How can I achieve this?
View 1 Replies
View Related
Feb 18, 2004
Question:
Is there a way to change the threshold for the number of errors encountered by bcp before it quits? I've looked in BOL and on-line and didn't find anything.
Background:
I'm using bcp to populate tables from files residing on the NTFS. This process is an upgrade from 6.5. The problem is that after 10 errors are encountered, the BCP quits but I have many more rows to load from the file. In the past, this wasn't an issue.
Any assistance would be appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare
View 2 Replies
View Related
Jul 20, 2005
I have a DTS package that is importing a fixed length file.After installing SP3a (on PERSONAL & DEVELOPER edition) it will notallow me to assign more than 125 fixed length column positions.After hitting the Finish & OK button it just wipes out the columnsettings.I've reinstalled both Personal & Developer edition. DTS will allowmore than 125 prior to the service pack.
View 2 Replies
View Related
Mar 11, 2014
I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE
[M2Data]
GO
/****** Object:
Trigger [dbo].[tblContacts_UTrig] Script Date: 3/11/2014 9:07:13 AM ******/
SET
ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?
View 8 Replies
View Related
Feb 1, 2006
Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.
After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?
View 7 Replies
View Related
Apr 29, 2015
This morning I attempted to connect to my local copy of SQL Server (2012) (Win 7) using Windows Authentication. I was unable to do so and per the error log it was due to error 18456 (invalid credentials).
Yesterday I changed my windows password. Is it possible for this to be the cause?
FYI, following is from the error logs:
2015-04-27 10:10:32.98 Backup BACKUP DATABASE successfully processed 0 pages in 0.451 seconds (0.000 MB/sec).2015-04-28 00:00:41.90 spid18s This instance of SQL Server has been using a process ID of 2096 since 4/21/2015 9:08:05 AM (local) 4/21/2015 1:08:05 PM (UTC). This is an informational message only; no user action is required.2015-04-28 09:58:51.16 Logon Error: 18456, Severity: 14, State: 38.2015-04-28 09:58:51.16 Logon Login failed for user 'NT AUTHORITYSYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: xx.xx.xxx.xxx]2015-04-28 17:09:59.87 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
The logs showed the error occurred yesterday (4/28) which is about when I changed my password. The 17:09 log (last one) is when I shut down my computer. Note that I was logged into SQL Server when I changed my Windows password and worked with it several times through out the day.
Also, per the configuration manager SQL Server (MSSQLSERVER) (and all other services) is stopped and I was unable to start it ("The request failed or the service did not respond in a timely fashion).
View 2 Replies
View Related
May 8, 2006
I upgraded to Sql Server Express 2005 w/Advanced Services, chose to install full-text indexing, rebooted, but appear to be unable to set up full-text indexing for a column. After attaching the old mdf file and opening any table in either Management Studio Express or Visual C# Express, I don't get anything but a disabled (Is Full-text Indexed) property for the fields I want to set up. I have verified that FullText Search service is running, and have tried restarting the service. I have also checked that full-text indexing is enabled on the database.
I just feel like I am missing some simple step. Any info would be welcome.
View 15 Replies
View Related
Mar 26, 2007
I used backup and restore to upgrade to sql 2005 from 2000. It went well but I didn't get the permissions change in time for the users to access the database today. The users were accessing the old server. I need to do another backup off the old server to the new server. Do I do a full backup to get the information for today's data or what? Will it restore a full backup override?
View 1 Replies
View Related
Jun 7, 2007
Hello All:
From my previous post it seems that the general consensus is for me to use backup and Restore to upgrade a dB from SQL2K to SQL2K5.
Could anyone refer me to some explicit instructions for doing this?
I have SQL2K5 and SQL2K on separate servers and I cannot back the dB up to SQL2K5. I also can't access the drives for SQL2K from SQL2K5. I'm hoping it's a simple mistake.
Any advice or guidance is greatly appreciated.
Thanks,
Cleech
View 3 Replies
View Related
Sep 20, 2007
Hi all
We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.
What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?
Thanks,
don.
View 10 Replies
View Related
Jul 21, 2015
We backup the database using the following method
1. launch the DacIESvcCli.exe at specified time, which in-turn calls the service (southeast asia).
2. the bacpac is created and uploaded to the specified storage
However, after upgrading the Server to V12, it failed to create bacpac file in the storage, there are no errors/no logs/no information.
View 4 Replies
View Related
Sep 19, 2015
I want to restore my SQL Server 2000 database backup to SQL Server 2014.
View 4 Replies
View Related
Jan 13, 2000
Hi,
How do we know that tape backups is working. And how do we know what is on them.
dindu
View 1 Replies
View Related
Mar 16, 2004
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\P3 emp est.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and regards.
View 4 Replies
View Related
May 13, 2008
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
View 3 Replies
View Related
Mar 31, 2007
Hi,
I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.
But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.
I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.
Any help will be appreciated.
Thanks
Sam
View 3 Replies
View Related
Jul 7, 2015
We successfully restore 2005 backup into SQL 2014 , so does that mean , does it mean all are ok ? or we have to do something next ?
View 6 Replies
View Related
Aug 21, 2006
I am using SQL server 7.0. After restoring all the databses (Exceptdistribution) Everything seems to be working fine except the backupmaintenance plan. I put following detail in the maintenance plan"General Tab:Plan Name --- DB Maintenance Plan1() All DatabasesOptimizations tab:Reorganize Data and Index PageChange free space per page percentage to 10%Remove unused space from database filesShrink Database when it grows beyond 50 MBAmount of free space to remain after shrink 10% of the data spaceSchedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.Integrity tab:Chack database integrityInclude indexes.Attempt to repair any minor problemSchedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.Complete Backup tab:Backup the database as part of the maintenance planVerify the integrity of the backup upon completionDisk use this directory: d:SQL BackupsBackup file extension: BAKSchedule: Occurs every 1 day(s), at 2:10:00 PM.Transaction Log Backup tab:schedule: Occurs every 1 day(s), at 2:00:00 PM.Reporting tab:Write report a text file in Directory: D:SQL BackupsLOG "Problem:My databases(mroduction as well as systems) sre not at all gettingbacked up according to above plan.Error Message in the Log Directory:"Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO(ODBC SQLState: 42000)] Error 4062: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open user default database '<ID>'. Usingmaster database instead."In the SQL server registration properties the login through whichgetting logged into the EM for that, default database is "my productiondatabase".Is this problem because of the distribution db which I am not able torestore.Please helpDeepak Sinha
View 1 Replies
View Related
May 13, 2008
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
View 7 Replies
View Related
