Project Server Databases Restore Failure - 3203 Error

Phew ..... wasnt really sure that i was able to overcome this and thought that all my efforts are lost now. But finally managed to fix it, and decided to put all bits and pieces in blog to help others.

I took the backup of my PWA i.e. 5db set, and when i tried to restore it to SQL so that i provision it on my VM environment, the following error has been thrown by SQL restore dialog box:

I am pasting the additional error information from the screen shot so that google can find it out for you:
______________________________________________________________________________________

an exception occurred while executing a transact-sql statement or batch. ( Microsoft.sqlserver.connectioninfo)

Additional Information :

Read on "C:\ProjectServer_Published" failed : 38(Reached the end of the file.) ( Microsoft sql server, error : 3203)

___________________________________________________________________________________

The Problem was that the source server database path was not found in destination server, so now you manually need to restore databases using sqlcmd with replace option. You can follow the steps to resolve this:

1. open command prompt using adminsitrator.
2. Type sqlcmd to connect to the default instance of SQL Server on the local computer, and the contents of the Command Prompt window will be:

   C:\>sqlcmd

   1> _
3. This means you have connected to the default instance of SQL Server and sqlcmd is now ready to accept Transact-SQL statements and sqlcmd commands. The flashing underscore after the 1> is the sqlcmd prompt that marks the location at which the statements and commands you type will be displayed.
4. Type the following script to get the Physical and Logical file name and its path of your database :
1. RESTORE FILELISTONLY FROM DISK = N'D:\<file_name>'
2. Go
3. 

   
   
5. Using the information you will get after running the above script, type the following script to restore your databases:
1. RESTORE DATABASE <file_name_New>
   FROM DISK = N'D:\<file_name>'
   WITH REPLACE ,
   MOVE '<Source physical file name returned by prev command>'
   TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<file_name_New>.mdf',
   MOVE '<Source Logical file name returned by prev command>'
   TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<file_name_New>_log.ldf'
2. go
6. This will now take some time depending upon the size of your database, you will see the cursor blink while its processing in the back. After a while output lines will start appearing indicating that status, and if it all goes smooth than the last line should be like this:
1. RESTORE DATABASE successfully processed 16668 pages in 8.765 seconds <size mb/sec>

Repeat the same above steps to restore all your databases, once all your database will restore successfully provision the PWA using the restored dbs.

Until next time, happy and prosperous new year ahead.

Posted in: Misc