The following link shows an example of backing-up and restoring the databases supporting Repor Server. My question is not so much Report Server-related but I question the restore syntax for restoring the logs.
I honestly do not see how the following code would work. At least, I was not able to until I deleted everything after the "NORECOVERY". Then it seemed to work fine but maybe I missed something!
-- Restore the report server log file to new instance folder
RESTORE LOG ReportServer
FROM DISK='C:ReportServerData.bak'
WITH NORECOVERY, FILE=2
MOVE 'ReportServer' TO
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataReportServer.mdf',
MOVE 'ReportServer_log' TO
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataReportServer_Log.ldf';
GO
Followed the steps in Books Online to move the Reporting Services databases from one server to another. After resetting all the keys and configurations, the site comes up. However, all my shared datasources have lost their connection strings, and have reverted to requesting login information. Is this how it is supposed to work? If not, how do I fix this easily (as I have about 25 data sources)?
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.
And my ReportServer log: <Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.1399.00</Product> <Locale>en-US</Locale> <TimeZone>Pacific Standard Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__03_24_2006_16_45_06.log</Path> <SystemName>USPROFILING</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> w3wp!webserver!1!3/24/2006-16:45:06:: i INFO: Reporting Web Server started w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing ConnectionType to '1' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!1!3/24/2006-16:45:06:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!resourceutilities!1!3/24/2006-16:45:06:: i INFO: Reporting Services starting SKU: Enterprise w3wp!resourceutilities!1!3/24/2006-16:45:06:: i INFO: Evaluation copy: 0 days left w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!1!3/24/2006-16:45:06:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!library!1!03/24/2006-16:45:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetWebConfigAuthenticationAttribute(String attrName) at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetAuthenticationType() at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.get_UsingWindowsAuth() at Microsoft.ReportingServices.WebServer.Global.EnableAntiDos() at Microsoft.ReportingServices.WebServer.Global.ShouldRejectAntiDos() at Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e) --- End of inner exception stack trace --- w3wp!library!1!03/24/2006-16:45:09:: i INFO: Exception dumped to: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson w3wp!library!1!03/24/2006-16:45:11:: i INFO: Catalog SQL Server Edition = Enterprise w3wp!library!1!03/24/2006-16:45:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetWebConfigAuthenticationAttribute(String attrName) at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.GetAuthenticationType() at Microsoft.ReportingServices.Diagnostics.WebConfigUtil.get_UsingWindowsAuth() at Microsoft.ReportingServices.WebServer.Global.EnableAntiDos() at Microsoft.ReportingServices.WebServer.Global.Application_EndRequest(Object sender, EventArgs e) --- End of inner exception stack trace --- w3wp!library!1!03/24/2006-16:45:14:: i INFO: Exception dumped to: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson
Any help would be appreciated:
System 2003 Server IIS6 and Sql2005 enterprise all housed on Virtual Server. all housed locally.
I have moved my databases to 2000 to instance of 2005 on the same server. Now i set databases offline in 2000 as i did upgrade use backup & recovery method.
& my connection string Password=test;Persist Security Info=True;User ID=test;Initial Catalog=databasename;Data Source=ipaddress
i was using above connection string to connect when using 2000 databases form applications in the production machine
I get the following error when i am running my applications now after moving to 2005
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
IS it because i have two server on one server or What do i need to do in order to fix this....tried several thing by searching nothing worked out..
Hi, I am in the process of moving reportserver and reportmanager from one server to another due to upgrade. SQL Server remains in the same place. I am getting the above error when trying to conenct to my reportmanger https://reportshare.thegmcgroup.local/reports I have SSL set up using SELFSSL.exe I can access the lisy of report vis https://reportshare.thegmcgroup.local/reportserver BUT get the error when trying the previous URL
Any ideas?
SQL Server 2005 Windows 2003 Server All SP added and up to date.
I am trying to move my Reporting Services databases from one server to another one. On the old server there was only a single instance so my databases were named ReportServer & ReportServerTempDB. On my new server I have multiple instances and, after installation (which I didn't perform), the databases on the new server are ReportServer$SQL_VS11 & ReportServer$SQL_VS11TempDB.
I was able to successfully restore both databases and rename them at the time of restore and also restore my encryption key but now when I try to view one of the reports I'm given the error:
Invalid object name 'ReportServerTempDB.dbo.PersistedStream'.
It states that "This error occurs because the ReportServerTempdb name is stored internally and used by stored procedures to perform internal operations. Renaming the temporary database will prevent the stored procedures from working properly."
This leads me to believe that what I'm attempting to do is not possible. I asked the developer in charge of the reports if we could simply republish but he informed me that we would lose history in this case. I may have to simply leave the old database names alone which would be inconsistent with our corporate naming schemes. Can anyone offer a suggestion?
Is it possible to cleanup ReportServer DB and ReportServerTempDB by setting the time interval ? For example, I want data in these two dbs to be deleted automatically after 5 or 10 minutes. How can I do that because as new reports are added and executed data goes on increasing in very short time and hurts performance.
Please help everytime i call http://servername/reportserver it is presenting the login screen, if i enter the username and password then i can access the reportserver screen, same with reportmanager tool.
under iis manager for the reportserver virtual directory i have given anonymous login access.
do i have to set any other extra permissions other than anonymous login(iusr_machinenname)
I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar. I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server. What do the best way to to this? PaulTucson, AZ
What is the best way to move the databases to a new hard drive connected to the system? We added a new larger hard drive and I would like to move just the databases to the new hard drive. Thanks
I move SQL databases quite often from one server to another. What is the most efficient way to do this? I normally perform a log dump then a database dump and then copy the backup DAT file over to the new server and then perform a restore. This is OK except that the User accounts permissions usually get messed up in the process and I have to redo them. It's OK for a few user accounts but some databases I work with have many user accounts. The version of MS SQL I use is 6.5 (sp4). Thanks in advance. JLS
hi i am trying to move my databases from one drive to another..sometimes could be into different network. i know how to do this through management studio. but how easy it could be doing it from query analyzer or may be using batch file. if you have experience please drop some words.
I have managed to get the Forms Authentication sample to work bu there is a hitch. I am running Report Server and Report Manager on the same(test) PC. I can logon to the Report Server without a problem but when I try logon from the UILogon.aspx page for Report Manager I receive the following error:
An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)An error occured while attempting to get the ReportServer Url. The RPC server is unavailable.(Exception from HRESULT: 0x800706BA).
I can register users which suggests that the page can connect to the database.
I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data. What methods are available to do this swap of data between databases? I was thinking of doing something like this: Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db. This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.
I'd like to hear the "best practices" for moving databases from one server to another with minimal impact (minimum downtime) to users. The database is about 1GB, but growing at an average rate of 5MB/day.
Only 2 databases on that source server are to be moved. There are other databases on that server that shouldn't be affected as much as possible.
Any good suggestions?
I'm sorry, but I'm a complete newbie to SQL Server although I'm very familiar with Exchange/NT stuff. Got thrown this responsibility lately :(
We are going to be moving over about 29 databases from one server to another (space issues). The new database will then be renamed to the old server name. The database sizes range from 100mb to 29gig. I was planning on doing a dump/restore which will take over 3 hours to dump and 12-15 hours to restore. Our NTAdmin suggested shutting down the SQL Servers and just copying over the data/log files to the new box and then bringing up the new server. Had anyone ever tried this? What are the effects on files over 22gig? Should I also copy over the model/msdb/master database files? What other potential problems should I be aware of? My fear is that I start copying them over, run into a problem half way through and then have to revert back to the dump/restore method.
Any help would be appreciated, we are scheduled to do the move this weekend.
how can I easily move my whole SQL Server 7 installation to another different machine as the current one is going down for maintenance during one week? Thanks.
I was hoping to get some input on the best approach for the following situation: I have been tasked with moving several SQL server databases from one SQL server to another to free up the hardware on the first one for another purpose. The SQL server is version 6.5 and has service pack 5 on it.
I have installed SQL server on the server where the data is going to end up with the same code page and sort order as the current server.
For my next step I was going to create devices for the databases on the new server, then add the databases to the devices. I was going to use the "Create database for Load" statement. Then I was going to load a transaction dump from each database on the old server into the new database on the new server. Is there anything else I will need to do? Will I have to do anything with the master database or is it sufficient to do what I have stated above?
Can anyone direct me to a good technet article or white paper on this process? I did find a short page in SQL books on line which helped a bit.
I have a few views i created in a database.I want them to be moved to another database with credentials like dbo.dsatabasename.tablename automatically adding up to the new views. How can i do that
Hi Experts, I need an advice on this documentation. What are the things i missed. Can you please help me.1.Make a full backup of all databases from server 1 (for our safety)
2.Copy all logins , Jobs , DTS packages, folders from server 1 to server 2 3.Copy any folders or files from the server 1 to the new server 2 that will be accessed by Jobs, DTS packages, stored procedures etc. 4.Change all databases to Single user mode so no one can access the server( Downtime)
5.Detach all databases from server1 ( There will be downtime)
6.Stop the SQL Server, SQL Agent Services on server 1 7.Attach all databases to server 2 since both servers are in the same Datacenter.
8.Connect to the server 2, using Enterprise Manager and make sure all the databases are displayed and that there is no suspect databases. Scan through the SQL Server error logs, SQL Agent error logs, and System and Application event logs, to make sure there are no errors. If we find any errors, fix them before you proceed any further.
9.Once we are happy with the new server’s server 2 state, shutdown the SQL Server, SQL Agent and MS Search services.
10. Rename the alias on server 1 11.Rename the alias on server 2 so all the applications will point towards it and reboot the server.
12.Start the SQL Server, SQL Agent and MS Search services on server 2.
13. After we have completed these steps, server 2 is ready to go.
Hi all,I am pretty new to dB programming and a have a few questions which I willput in separate threads. I have only coded as a hobby and some limited workprojects. I have recently been asked if I could help implement a simple helpdesk for work. This has only been running for a few weeks and the data iscurrently being stored in excel (I know...I know!! :-)I have installed a copy of MSDE on my development machine and plan on doingthe same on my office 'server'....in fact it may well already be on there. Ialso have another testbed to act as the client.My first question (in no particular order) is how to move the sql db fromthe development machine to the server? I don't have any sql/db tools otherthan anything that is included with MSDE (which I don't think is much).I will be posting other Q's for any experts out there!Thanks in advance.Phil
We've all data and log files located in SAN mount point. In case ofprimary server crash, is it possible to unmount the volumes andremount at secondary server? Do we have any problems with master DBmoving this way. Detach,attach or backup/restore are not our options.Thanks
Not sure if this is the exact forum I need but here goes.
Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.
One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!
I have to periodically migrate a copy of database on a production web server down to a web server used by some developers. The names of the databases are different, as are the underlying file names and locations. I use Management Studio to do this. we're running 2008 R2.
I make a backup on the production server, then move the file to the developer server. On that server, I take the db offline, then restore the file into the proper database. Before I start the restore I change the files to match the default names for the target database. Then I do the restore.
The main practical problem I have is that the security ends of breaking for some logins, but not for others, and I don't know why. I use the same login names for users on the source and destination machines, but sometimes they don't come through cleanly, and I have to recreate them. It's always the same users that break. I can fix them, but I'd like to understand why some users come across while others don't.Also, what's the best way to move a db from one engine to another, with a name change along the way? And how do you do it if you have different physical environments and different paths for the actual underlying files between the two systems?
I have to move 5 Databases from production server to another server. I know it can be done by Backup and Restore and then make sure that all User names, Passwords and roles are not changed.
Some one can please help me out in giving a step by step process.
I just got hosted on a site which supports .Net, and my forms seem to be coming up okay. I have a database residing on my computer that accompanies the pages I have written.
I was told that I need to export my database to their site, and I have an ip address to use. Can anyone tell me how I would go about with this export ?
whenever we are moving system databases from one drive to another, do we need to move the path physically? or it automatically moves.I want to try the rebuild the sql server. So I want to corrupt the master database so, I deleted the master mdf file and restore it back but instead of database corruption it is giving the following error message. How can I corrupt the master database and can practice the rebuild the server.
And one more thing when I try to use the repair option I couldn't able to get all the things back to normal. The database engine service, replication is not working but SSAS, SSRS things are showing successfully repaired.
2014-01-10 21:34:26.44 Server Error: 26055, Severity: 16, State: 1. 2014-01-10 21:34:26.44 Server The SQL Server failed to initialize VIA support library [QLVipl.dll]. This normally indicates the VIA support library does not exist or is corrupted. Please repair or disable the VIA network protocol. Error: 0x7e. 2014-01-10 21:34:26.44 Server Error: 17182, Severity: 16, State: 1. 2014-01-10 21:34:26.44 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60. Reason: Unable to initialize the VIA listener. The specified module could not be found.
We tried to move the two 2005 ReportServer databases to another platform. The actual db move worked fine (detatch/copy/attach) but when the report guy tried to change the connection info in Report Server he could not seem to make the change. So we backed-out the changes and regrouped.
This should be very cook-book. I have no experience with Report Server but I know some of you guys could to this with your eyes closed. After the databases are moved I think all we have to do is change the server name and credentials in Report Server. Is that correct? Do we have to bounce any services? Are there any tricks I can impress the other guys with?
