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
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
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?
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 ???
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.
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 ?
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?
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 ?
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 ?
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!
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?
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?
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.
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.
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?
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?
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).
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.
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?
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.
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?
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.
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.
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?
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.
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
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?
