I have been having trouble migrating a SQL2005 database from one server to another. What I am trying to do is setup a test server for the database that's already on the production server. So, I backed up the database on the production server. Then I copied the backup to an external hard drive and then plugged the external hard drive to the test server. Moved the file over to the test server and then, tried to restore it thru SQL Server Mgt studio. I got an error message when I tried to do this.
System.Data.SqlClient.Error: The operating system returned the error '5(Access is denied.)' while attempting 'OpenForRestore' on 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ix_STS_BP-NY-DC-01_1\SQL.HDR'. (Microsoft.SqlServer.Smo)
Production server and the test server are in 2 different locations. Production server is on a domain whereas the test server is standalone. I am not sure if it even matters.
I'd really appreciate your help on this problem.