To Transfer Maintaenance Plan And Jobs From SQL 7.0 To 2000
Apr 25, 2002
We've upgraded our SQL7.0 user db to a SQL2000 system using sp_detach_db and sp_attach_db stored procedures.
And we'd like to transfer our sched jobs from a SQL 7.0 to SQL2000 systems,
if the jobs are created outside a maintenance plan, we can script them out and run the script to recreate them on the 2000 system.
But if the jobs are within a maintenance plan, we would have to recreate these plans manually on the 2000 system, is this the only option we have ?
Any one has a better solution ?
I think "restore msdb from abackup" should work better in this case?
In SQL2000, there is a new DTS task for transferring jobs between 2000 systems (same version), but I don't think it work to transfer them from 7.0 to 2000 ??
Anthony
View 3 Replies
ADVERTISEMENT
Feb 11, 2003
Hello,
I want to move a db from a SQL server 7 to SQL Server 2K. I'll do a backup and restore function to move the database, but how do I handle the objects mentioned in the header ?
Bothe servers (old and new) will have the same name, as well as the DB
All help is appreciated !
Thanks
View 2 Replies
View Related
Mar 9, 2006
Hi,
I'm working with SQL 2000 and am just learning about Maintenance Plans (MP). They seem convenient, but after some time, I'm wondering if they're the best approach long-term. Here are my experiences.
Using the MP Wizard, I created a plan with tasks from all the dialogs:
- Optimize database
- Check integrity
- Backup database
- Backup transaction log
- Write a report
I was puzzled to find 4 jobs were created, each with just 1 step, and staggered starting times. I expected to find 1 job with 4 steps. So, brimming with confidence, I did just that. I combined all 4 into 1 job, deleted the 3 other MP created jobs, and checked for any job-specific details in the code. However now when I open the MP, I get this pop-up:
"One or more of the jobs created for this plan has had additional steps added to it. It is not recommended that jobs created by the maintenance plan be modified in any way."
Okay, fair warning. Yet it appears the job and all steps run successfully, both on demand, and on a schedule. So now I'm wondering if jobs always need a MP. If I don't mind working with xp_sqlmaint syntax, it appears the only thing I'm giving up is the MP history. But I expect job history and '-WriteHistory' will minimize that loss.
I searched BOL, this Forum, and Google, and found a couple articles. One author preferred the ease of the Wizard, another preferred the control and added features of T-SQL, but both created a MP in their examples. So I'm hoping some experienced DBAs can advise.
If I create a job with multiple steps, and no MP, are there important things I give up or problems I create?
Is this approach a bad idea in SQL 2005?
At this stage, I don't need replication or other advanced features. Just simple database maintenance.
Thank you,
- Martin
View 1 Replies
View Related
Aug 10, 2007
I am migrating the SQL server 7 to SQl server 2005 (side by side upgrade).... I copied all the jobs including the maintenance jobs .is that the right way ?
do we need to copy the maintenance jobs as well ???
or do we have to create the maintenance plan which will create the new jobs itself ????? please Guide ....... its urgent ....
thanks in advance !!!!!
View 3 Replies
View Related
Sep 11, 2003
Hi,
Is there any way that I can script out all the jobs at a time (instead of doing one by one). I have mor than 100 jobs to transfer, I was just wondering if there were a way to do all at once.
Thanks.
View 6 Replies
View Related
Feb 12, 2014
I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
In order to solve this issue i have tried these solutions;
1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times
View 0 Replies
View Related
Mar 14, 2005
Hi,
I have installed SQL server 2000 (Enterprise) on W2K advanced server using my local "Administrator" account. Using the same account for login, I have created database backup jobs on the server. After this I rename the Windows administrator account to "admin". Now I see that the jobs started failing. The error message indicates that "could not determine whether the 'server/administrator' account has access to SQL server."
Further, when I tried to troubleshoot, I discovered that the job owner name still displayed (through EM) is "server/administrator" account (this account is now renamed). I tried to change the owner name to "server/admin" but it is not getting changed. Hence, I just created a Windows user named "Administrator" and see that the Job is executing successfully.
Please note that the backup is taken on the server itself and SQL server & Agent services are running under security context of a domain account having administrator priveleges on the server.
What I am interested in knowing is what are the reasons for such behaviour? And how to rectify this problem since i really don't want the "administrator" account to be created.
View 6 Replies
View Related
Jan 10, 2007
Hi. i'm new to sql server jobs as i have never used the feature manually before. here is what i want to do but i don't know if it's possible.
we have a report we use that's accessed from an asp page. currently, the page uses a stored procedure to generate the data. well, as time has progressed, we've added many items to the report. it now takes a good 15-20 seconds to pull the report. what i want to do is run a job that will compile the data for me and just dump that information into a new table. does that make sense?
can this be done with sql server jobs? thank you.
View 6 Replies
View Related
Nov 1, 2007
Hi,
I have been scheduling jobs from past couple months on production but it does not execute on the scheduled time.
I checked the schedule enabled tab, it was enabled when i scheduled the job, but was disabled at a later date when i checked to see why the job did not run.......
Can some one please guide me if there is any process in SQL which disables the jobs automatically.
I want to find out how the jobs when scheduled and enabled gets disabled on its own.
View 1 Replies
View Related
Sep 22, 2006
Yao,
Could anyone help me with, or point me to information regarding, scheduling jobs for MSSQL 2000? I need to execute a SQL Command, monthly, which sets a budget field to a fixed ammount, something like: UPDATE Programs SET Budget = 500.
--Kyle Johnson
View 1 Replies
View Related
Mar 19, 2008
I am trying to migrate few of my SQL jobs to SQL2K5. Can I simply script and execute them on the new environment. I hope the script is forward compatible.
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Aug 21, 2007
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
View 3 Replies
View Related
Jul 23, 2005
I have a log file that went out of control (147GB), I feel that doing a shrink on the file/database would be time consuming so I am thinking of recreating the database from an imported version from our test server.I know that data can flow from one SQL server to another without regard to the SQL server version but can tables, stored procedures and views export from a 2000 database and put back into a 7.0 database?Thanks--Message posted via http://www.sqlmonster.com
View 4 Replies
View Related
Sep 10, 2007
I would like to create an SSIS package in 2005 and run it in 2000. Is there anyway to do this? Or does SQL Server 2000 have a precursor to SSIS? I am trying to create a job to automatically catch and kill orphaned processes.
-Kyle
View 1 Replies
View Related
Nov 22, 2002
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.
many thanks
View 11 Replies
View Related
Jul 20, 2005
Hi !Can anybody tell, how to transfer the sql server 6.5 Data to SQLserver 2000. I use the upgrade utility, but it gives an error.
View 4 Replies
View Related
Apr 21, 2008
Dear all,
I want to grant a user permissions to run Jobs in SQL Server 2000. I can' seem to find any appropriate roles for that. Do I need to assign him a server role? In that case, which? Many thanks.
Pedro Martins
View 1 Replies
View Related
Dec 8, 2006
Hey all-
I am working to establish a backup plan for SQL Server 2000. We are currently doing log shipping between servers.
A full backup once a week
transaction log backup every hour
We run a bulk deletion (delete * from table where date < ninetydays) which deletes 22,000 rows approximately. We are running in Full Recovery mode.
I wonder if there was anyway to improve my backup plan, preferably minimizing logging when the deletion takes place but still backup the data with log shipping.
Let me know if any of you have ideas. Thanks
Nate
View 1 Replies
View Related
Jun 24, 2007
I was once told that I was not to use the GUI to setup a maintenance plan. What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap.
Has anyone ever heard of this or is this an old wise tale told by only one person?
I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!
View 1 Replies
View Related
Jan 3, 2006
Hi guys,
I am new to SQL Server 2000 and like to enquire the following.
Scenario:
this current website is hosted by A but now taken over by B.
B was given SQL server username and password and was instructed to download the database directly from A based on the username n pw given.
So my qns is:
Is it possible to initiate data transfer from one server situated at locationA to another server at locationB that doesnt belong to the same domain? If can, by which ways? Using the DTS method? Or there are some settings to be configure at ODBC in order to initiate the data transfer?
Any experts out there? ;)
View 8 Replies
View Related
Mar 19, 2007
Hi everybody!
I dare say it may seems rather very easy for me, but i'm quite glitching. I'm to transfer all users from SQL2000 to SQL2005.
Has anybody done this?
Thanks in advance!
View 1 Replies
View Related
Apr 27, 2007
I am trying to copy a database from sql2000 to sql2005 but it is giving me the following error:
To connect to this server you must use SQL Server Management studio or SQL server management objects (SMO)
this can be done or servers are not compatible?
View 2 Replies
View Related
Oct 3, 2006
Hi,
how to reschedule/schedule jobs in server 2000 and 2005 Programmatically?
thanks
sandipan
View 1 Replies
View Related
Jun 19, 2001
I have a partitioned view containing 4 tables (example follows at end)
The query plan generated on a select correctly accesses just one of the tables
The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area
Note that the behaviour is the same with SP1 on SQL2000
I would be very grateful for any advice
Thanks
Stefan Bennett
Example follows
--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO
INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO
--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO
--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'
-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;
View 1 Replies
View Related
Jun 27, 2007
Hello:
I'm looking for a Project Plan template for Migration from MS SQL 2000 to MS sql 2005.
We apporeciate your help.
regards
William
View 8 Replies
View Related
Jan 4, 2008
Hi,
I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
(
Columns
)
Select
Columns
From
sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
any help or ideas will be appreciated.
Regards...
Karen
View 9 Replies
View Related
May 28, 2008
Hi All! i have a database in SQLEXPRESS 2005 but i need to this Database in SQL 2000...there is any way to move data from sqlexpress 2005 to sql 2000.... please help me.....
View 4 Replies
View Related
Jul 17, 2006
Thanks for the replies,
As per the querys:
------------------------
1) I am trying to use the Database of my host (hosting server of datapacket). They have a support for MS-SQL-2000. Therefore surely the server is there. After my CPanel configuration i got the Database Server IP (67.122.X.X), Port - 1433, SQL Server user and pasword.
2) In my local machine SQL Server 2000 is installed. Where the databse and DNS is configured.
Now, from this local machine how can i transfer the data to the host machine (remote SQL Server).
Simpliy copying Files *.MDG and *.LDf files it is not working. And i don't have access to the remote SQL-SERVER as it is in the host machine of datapacket. I can only configure it throuch there Control Panel and throuch FTP can copy the required files in there machine. I have a user in that remote MS-Sql server that all i have.
As per my understanding after the control panel configuration the database and the DNS is created in the remote Sql-Server of datapacket. Now all i have to do is transfer the data & tables. HOW TO DO THAT?????
I have tried using Export but it fails to connet the remote database.
Thanks in Advance
Dipya
View 1 Replies
View Related
May 13, 2008
Hi
How to transfer logins from 2000 to 2005?
Raj.
View 1 Replies
View Related
Feb 16, 2007
Hi gurus
i have a assignment on hands to transfer store procedures of 2005 to 2000. I have tryed by scripting SPs and then runing them in 2000 QA, but it gives me error every time ....... so please help me to resolve this problem.
thanks
shekhar
View 3 Replies
View Related
Aug 16, 2007
Hi All,1. i want to transfer the .csv file data into sql server table, itried with the DTS but while creating DSN it not prompt to attechthe .csv file. give me the proper steps to perform the datatransfer...2. i want result of my query into excel or text file by using the sqlquery( like Select * from employee where emp_salary>10000 to 'c:emp.xls).i know the other way right click into query analyzer windowand select option result to file, but want the result by using SQLquery.....
Quote:
View 2 Replies
View Related
