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


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

Migrating SQL Server 2005 databases, including SQL Agent jobs and SQL users

Posted By:      Posted Date: November 20, 2010    Points: 0   Category :Sql Server
 

Hi,

I should begin to migrate all my data from a SQL Server 2005 (x86) SP3 to another server with SQL 2005 (x64) SP3 installed. About moving the Databases, I think that detach and attach them won't create unexpected problems.

The problems that scare me are:

  1. Migrate the SQL Agent processes. I've created so complex jobs that I'd rather swim across an ocean than rewrite them.
  2. Migrate Users. There are near 70 users with integrated security and each has different mappings to the 40 DBs. Unfortunately there's no domain, so I don't know whether it's possible to copy them without changing their GUIDs.

Does anybody know if my goal is within reach?

Can someone tell me which step to make (and in what order)?

Thank you very much.




View Complete Post


More Related Resource Links

How can I get a list of users that are able to start/stop/execute jobs on sql server?

  
I know that the users with sysadmin roles are able to start/stop/execute jobs but I wonder if there are another roles or permissions that enable users to do this.   regards,

Run Reporting services 2005 with databases housed on SQL Server 2008?

  
Hi, I have a RS 2005 install in which the ReportServer and ReportServerTempDB databases reside on a different SQL Server 2005 server. I am planning to upgrade both to 2008 at some point but would like to do the SQL database engine first. So I would be running something like this: Server A: SQL 2005 Reporting Services Server B: SQL 2008 database engine - housing ReportServer and ReportServerTempDB Is this configuration supported? If I do this would I just need to run the DB's in 9.0 compatibility mode until we upgrade the RS server? Would it be better to upgrade the RS box first or both of them at the same time instead of doing the database engine first?

Run Reporting services 2005 with databases housed on SQL Server 2008?

  
Hi, I posted this in the RS forum as well but was thinking it would be better here. I'm not sure if I can move a post to  a different forum or not so sorry for the double post. I have a RS 2005 install in which the ReportServer and ReportServerTempDB databases reside on a different SQL Server 2005 server. I am planning to upgrade both to 2008 at some point but would like to do the SQL database engine first. So I would be running something like this: Server A: SQL 2005 Reporting Services Server B: SQL 2008 database engine - housing ReportServer and ReportServerTempDB Is this configuration supported? If I do this would I just need to run the DB's in 9.0 compatibility mode until we upgrade the RS server? Would it be better to upgrade the RS box first or both of them at the same time instead of doing the database engine first?

SQL Server Security > Logins vs. Databases > Security > Users

  
Why isn't all security needs done just in logins?  When migrating databases, the Logins are not brought over when restoring a backup.  It does not make sense.  Could it not be done all just in the Security > Logins area or why not?  That COULD be designed to track users and rights on various databases rather than a thing owned by each database....Thanks for your time.Gib

Restoring Databases form SQL Server 2000 and SQL Server 2005 to SQL Server 2008 -Side by Side --he

  
Hi all I Installed SQL Server 2008 R2 in Server and I took backup of all the User databases backup from 2000 and 2005(two instance are running) Now I am doing restore the Databases,while I am restoring I am information icon in bottom of the restore window that is "The Full-Text Upgrade Option server property controls whether full-text indexes are improved,rebuild or reset" that means I have to upgrade full-text indexes or this just inforamtion.If I want find out whither Database using full-text how to check ? once I done this I have to move the users as well.Please dome body share the script you have.   Thanks in AdvanceSNIVAS

Sql Server Agent stops executing jobs

  

Hello everyone.  I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule.  I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again.  It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed.  Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:

 

SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed.  Unable to determine if the owner (AD\adacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

 

Any help would be greatly appreciated.

sending mail in SQL using SQL Server Agent Jobs

  

Hi,

 I am using SQL Server 2008 and I want to create one job in that.

 Job should be like that it will run any T-SQL and the output of that query

should be mail to me. But I am not able to do this.

Please help me...


Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

3) Also one more point about the s

SSIS Jobs VS SQL Server Job Agent

  

Which one of them is better to just run a set of Strored Procedures on a daily basis.

Can I have some Adv and Dis advantages. Please.

Thanks


Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

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

detached databases on sql server 2005

  
we have more than 500+ databases on the server. when db is idle for some time, we detach it from the server. How can I find out how many detached databases we currently have? and their names?
JulieShop

Migrating Jobs: SQL 2005 32bit > SQL 2005 64bit

  

I am trying to migrate jobs between SQL Server 2005 32 bit to SQL Server 2005 64bit.  I follow the steps in the KB Article to copy a job by scripting it and then running the SQL against the new server.  The query executes fine, but it only creates a skeleton of the job with nothing in the body.  There is no schedules, steps, name, etc.  The resource versions are different and tonight my plan is to apply the service packs to ensure there is no discrepency in either version.  Also these are all TSQL jobs not DTS packages.  Any ideas?


SQL Server 2008 / 2005 Databases Replication

  

Domain One - abc.com (IP Address scheme - 185.192.X.X)
Domain Two - xyz.com (IP Address scheme - 10.200.X.X)

There is a Forest level trust between those 2 domains.

Can you please help me if there is there any way that I can replicate the datas in 185.192.1.200 Server to 10.200.18.15 Server (which has both Ms Sql Server 2005 on it) - which will have same database in both servers. There is a chance to link those 2 servers in those 2 networks and store datas at the same time on each servers. So that if one goes down, staffs can log into another one without any down-time to access data from another server's database (which is the replica of the primary DB server). Please let me know & also if there is any good resource about that the same.


VT

executing bat files in SQL Server Agent Jobs

  

hi.

there are 2 batfiles running in daily maintenance plans. one, compressing Backups using GZIP and the other deletes old Backup Files.

the delete.bat uses "forfiles" command and successfully deletes files. recenlt the maintenance plan continued to failed and to investigate i foundout that when there are no files met for  the bat files' criteria, it is being recorded as an error and job faliure in SQL Server.

for example, when there are no files to be deleted, the bat file returns a message like "ERROR NO FILES FOUND UNDER THE GIVEN CRITERIA"

this message is interpreted as an error in the SQL Server JOb which is actually not.

 

do i have a way of handling this kind of errors?

 

please reply

 

noviceDBA

 


Advice on migrating from SQL Server 2005 Standard SP2 32-bit on Windows Server 2003 Standard SP2 32-

  

We are planning to migrate from SQL Server 2005 Standard SP2 32-bit on Windows Server 2003 Standard SP2 32-bit to SQL Server 2008 R2 Standard 64-bit on Windows Server 2008 R2 Standard SP1 64-bit. Would be grateful for any advice on making such a move.

Thanks,

SQL Servant


SQL SERVER 2005 ED (how to copy jobs from one server to another )

  
I am going to move SQL Server 2005 Ed from 1 server to another server ...
i have finished every thing, but jobs still not present on secondry server.

one option that i know create a script from primary & paste it to secondry server but I have more than 500 jobs..........
there is any other option so i can move complete jobs from primary to secondry.........

if i will deatch & attached msdb.mdf file will it work properly ? or it require any other configuration setting also ?

Regards
Ravendra
india
Categories: 
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