.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Upgrade SQL Server - way to backup the SQL jobs

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Our database server has been used for more than 5 years and we plan to upgrade..

Currently we have lots databases inside the database server and many jobs running daily. But we have no idea on how to backup/copy all the SQL jobs to the new database server.

Kindly advise any way to upgrade the database server without losing any db, views and sql jobs?

**Currently alot of sql jobs running to update the views in order to generate the business reports.


Thank you!

View Complete Post

More Related Resource Links

SQL Server 2005 - Maintenance Jobs take a long time after OS Upgrade


We have SQL 2005 Ent Edition (SP2) on W2k3 R2 Ent Edition. This OS was previously on Std Edtion and we had done an In-Place upgrade to Ent Edition some time back. Since then the Maintenance plans - Update Stas & Index Rebuild seem to take double the time to execute. I do believe this could be due to an already busy disk subsystem (RAID 5 array).

Can someone pls advice what could be possible reason for this sudden increase in the maintenance job execution time.

Paul - SQL DBA

SQL Server Backup and Restore (video)

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

Access to SQL Server Jobs

Hello folks, Would like to grant group of users ( windows logins) to CREATE / MODIFY / DELETE / SCHEDULE jobs on SQL Server 2005. But can not grant users SYSADMIN privileges. I know that by granting users SQLAgentOperatorRole, SQLAgenetReaderRole and SQLAgentUserRole the User will be able to create jobs but they will be the owner of the newly created job and other users can not MODIFY / VIEW / RESCHEDULE the jobs. Is there any way to customize permissions to windows logins to have permission to CREATE / MODIFY / DELETE / SCHEDULE jobs without granting them SYSADMIN privilages ? Appreciate your reply   Thank You Arunaroon

Upgrade SQL server 2008 R2 from Evaluation Edition to Enterprise Edition

Hello SQL Support group, I have SQl server 2008 R2 installed on a Win 2003 R2 and it is for our SharePoint and Project server 2007 environment. Today I noticed that we have only 75 days left! Seems like we installed it on evaluation mode. Here what we have on Helpàabout : Microsoft SQL Server Management Studio Complete (expires in 75 days)    10.50.1352.12 We do have MSDN account, and we have the KEY that comes with the SQL server 2008 R2 enterprise. We would like to assign the key to our SQl server ( we want to have enterprise edition). It is in a production environment now. I would like to know how we can do it  with minimum of down time. I found this link: http://blogs.technet.com/b/sqlman/archive/2010/03/16/sql-server-2008-upgrading-to-standard-enterprise-edition-from-evaluation-edition.aspx On the internet, but doesn’t say anything that we are losing any data after upgrade or not. Please let us know the best way to assign the key, and if it has any impact in our data. Thanks Bahram  

Why upgrade is required from SQL Server 2000 to SQl Server 2008

Hi all Can some body share with information about "Why upgrade is required from SQL Server 2000 to SQl Server 2008?"   This question was raised by my Boss why upgrade is required.   I stared saying   My: there is no more   support for SQL Server 2000 from Microsoft   Boss: what you will do  as DBA, Why Required Microsoft support   My: 2008 having very good features where 2000 not have...like Compression, resource governing. .Etc..   Boss: do you mean Database backup compression take full backup and compresse it using ZIP   Finally He (Boss) told me that give document proof why I have go for SQL Server 2008 and what down time you required?   Some body help me why really SQLServer 2008 is required and what are benefits   I have a link; please provide your greatest ideas so that I can convince my Boss.   http://www.mssqltips.com/tip.asp?tip=1462   share your greate Ideas please....   Thanks in Advance     SNIVAS

Any issues removing backup mount point cluster resources from SQL Server dependencies?

Win 2003, SQL 2005/SQL2008 64 bit enterprise failover cluster with 3 nodes and 6 instances. The mount points configured as cluster resources for the data/backup/logs. The entire cluster mount points are SAN replicated to a DR Site. But backup drives/LUNs are excluded from the replication. All the disk resources including backup LUNs are set to dependencies on the SQL Server server. The SQL Server is not coming on-line in the DR Site due to unavailability of the backup LUNs (failed resources). As per the documentation all the disk resources must be set to dependencies (http://support.microsoft.com/kb/835185). Thought it did not specifically mentions only Data/Log mount points and/or the backup LUNs as well. "The SQL Server 2005 resource depends on the SQL network name resource and the physical disk resources that hold its data. When mount points are being used together with the physical disks, each mount point must appear as a cluster resource. Additionally, each mount point must also be added as a SQL Server dependency. If only the root physical disks dependency is added and the mount points are not added, database corruption will occur on failover. Database corruption may also occur when SQL Server is restarted without failing over. "     What if I remove the backup LUNs from dependencies list. This will allow me to recover the SQL Server

Will I able to do the upgrade of SQL Server.

I have resource database and all the system objects are stored into it..If I will delete it is there any problem will create, will I able to do the upgrade of SQL Server after deleting it.... Please answer if you have rigid knowledge..."SQLSERVER DBA" "INDIA"

SQL Server backup failing on one file

Hi I have a server which is not backing up a db each day. When I look at the event log it displays the following...   SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000008e00000 2010-08-16 16:07:38.19 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:07:38.19 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15100) 2010-08-16 16:07:38.20 Backup Error: 3041, Severity: 16, State: 1. 2010-08-16 16:07:38.20 Backup BACKUP failed to complete the command BACKUP DATABASE ########. Check the backup application log for detailed messages. 2010-08-16 16:12:14.32 spid5s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000009900000 2010-08-16 16:13:07.02 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:13:07.02 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15105) 2010-

Sql Server 2005 backup misses files

I have a SQL Server 2005 database that contains 3 files.  Sometimes when I run rhe backup, it appears to only backup 2 of the three files.  The only way I can get it to return to a full backup is to restore the database from a backup tape.

Compatibility problem: an "sql server 2008 R2" db backup cannot be restored to an "sql server 2008"

To verify if the "2008 R2" version can be restored back to a "2008" dbserver I make an experiment:  construct a database on R2 version ( version : 10.50.xxx )  and backup it; then I try to restore the db backup to a "sql server 2008" db-server ( version : 10.0.xxx ) but get failed. Error message is as shown: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.   Originally I want to upgrade a "2008" server to "2008 r2" , but this problem withdraws me: if I do so , I am afraid all the existing clientuser's dbs cannot be backed up to themselves if they don't have "2008 r2" locally:   if a database is backed up under "2008 r2" environment whereas client user only has "2008" db server, does it mean he cannot restore it anymore?   So I wonder if there is an approach that helps me to create a "backward-compatible" database; when creating database on wizard there is an option to choose "compatibility level" but I don't think it meets my requirement..    Anyone can help me on this ??    

COM Server upgrade breaks interface

Ok, so apparently I posted this question in the wrong group last time, so I'll try again :) ======= I have an ActiveX exe that I’m calling from a Web Service. The ActiveX exe however gets upgraded independently of the web service. When this happens the web service breaks. Looking in the registry, under HKEY_LOCAL_MACHING \ SOFTWARE \ Classes \ {DLL Name}  - the GUID seems to remain consistent after upgrading the EXE, however I’m guessing the interface GUIDs much change for some reason. Is there perhaps a GUID per interface that’s exposed that’s changing when the ActiveX EXE is recompiled? If so can someone tell me how to view these interfaces and confirm that his is or is not the case. ======= What I'm trying to figure out of why my Interop Proxy DLL stops working every time the developer of this ActiveX EXE releases a new version. As I said that GUID in the redistry seems to remain the same. I'm just not sure how to go about debugging the issue. I don't want to have to re-compile my application every time he releases a new version and he does not seem to think he's changing the interface - so something else must be changing when you /unregserver  /regserver the COM server \ ActiveX EXE. ========

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

HTTP Error 500.19 - Internal Server Error after SharePoint 2010 In-place upgrade

Hello We have done an SharePoint 2010 In-Place Upgrade. After the upgrade, we can access the Central Administration page without problems. However, when accessing the standard http port 80 site, the following error appears: Error Summary HTTP Error 500.19 - Internal Server Error The requested page cannot be accessed because the related configuration data for the page is invalid.   <fieldset><legend>Detailed Error Information</legend> Module IIS Web Core Notification Unknown Handler Not yet determined Error Code 0x80070032 Config Error The configuration section 'system.web.extensions' cannot be read because it is missing a section declaration Config File \\?\C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config Requested URL http://coolsharepoint:80/ Physical Path   Logon Method Not yet determined Logon User Not yet determined </fieldset> <fieldset><legend>Config Source</legend> 534: </location> 535: <system.web.extensions> 536: <scripting> </fieldset>

Moving a Copy of SQL Report Server 2005 and Upgrade to 2008

Hello, We have SQL 2005 Report Server in a Windows 2003 64 bit server box. We want to create a copy of this report server database somewhere in the domain, so that we can access it for a different purpose. My question is, will there be any complications with this? I'm thinking about the encryption key that's needed to access the database. If we are successful with this, we might also want to upgrade to SQL 2008. Thanks for your help,   Elizabeth K.

Health Analyzer reports "Product / patch installation or server upgrade required." and I can't seem

The text of the error is: On server SHAREPOINTSERVER, once all required products and/or patches are installed, perform an upgrade by either running PSConfigUI.exe or by executing the command "PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures". If a former upgrade attempt has failed, you may need to resolve upgrade specific issues before attempting upgrade again. Refer to the upgrade status page (http://sharepointserver:XXXX/_admin/UpgradeStatus.aspx) for information about current and prior upgrade attempts, and to determine issues that may be preventing upgrade from succeeding. For more information about this rule, see "http://go.microsoft.com/fwlink/?LinkID=142700". So I went to the server in question and ran the command line quoted in the error message and then I get this error back: Configuration of SharePoint Products failed. Configuration must be performed before you use SharePoint Products. For further details, see the diagnostic log located at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\1 4\LOGS\PSCDiagnostics_....log and the application event log. So fine, I look into the event log and get this: Failed to initiate the upgrade sequence. An exception of type System.IO.IOException was thrown. Additional exception information: Access to the path 'C:\ProgramData\Microsoft\SharePoi

Backup SQL Server 2008 R2 Restore on SQL 2008

I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance. When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo) How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?Ian Ceicys

Backup server

I have a 2008R2 server that does AD, sharepoint and file shares.  What I need to have to is if the main server goes offline a second server pick up where it left off. What is the a good solution for me?   Thanks
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend