Link to home
Start Free TrialLog in
Avatar of sjs
sjs

asked on

Urg - Restore SQL 2005 data to sbs 2003

I am restoring a backup from a crashed sbs server with SQL 2005.  Originally the Database(s) were on drive "D:" and now I have reconfigured the server with only one dirve "C:" and SQL is on "C:".  I am not able to restore the database saing to the effect that there is not enough space .... but there is.  Urgent  ****
Avatar of James Glaubiger
James Glaubiger
Flag of United States of America image

a.  Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
b.  In the Connect to Server window, follow these steps:1. In the Server Name box, type the name of the new server that is running SQL Server.
2. In the Authentication box, click SQL Authentication.
3. In the Login box, type sa.
4. In the Password box, type the password for the sa user, and then click Connect.
 
c.  In the Object Explorer area, right-click Databases, and then click Restore Database.  
d.  In the Destination for restore area, type DYNAMICS in the To database box.  
e.  In the Source for restore area, click From Device, and then click the ellipsis button to open the Specify Backup window.  
f.  In the Backup Media list, click File, and then click Add to open the Locate Backup Files window.
g.  In the Select the file area, click the backup file for the DYNAMICS database that you backed up, click OK, and then click OK.  
h.  In the Select the backup sets to restore area, click to select the Restore check box next to the backup that you want to restore.  
i.  In the Select a Page area, click Options, and then click to select the Overwrite the existing database check box.  
j.  In the Restore the database files as area, change the Restore As column so that the data file and the log file use the correct paths on the new server.

Note The default paths for SQL Server 2005 are the following.
%systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Data.mdf
%systemroot%\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_Log.ldf
You can find these files by using Windows Explorer.
k.  Click OK.


In Step J you may need to modify the paths to restore to your C:\******\****** SQL data path.
Avatar of sjs
sjs

ASKER

That's how I was doing it but on a different file name.  It says there is not enough free space but there is because the back up is 350 MB and there is 37 GB on the HD.  
It did work wth Dynamics but I put the wrong name on it.  Can I just select it and delete?
Avatar of sjs

ASKER

The message regarding the space refers to (Microsoft.sqlServer.Smo)
Avatar of sjs

ASKER

Can  I just stop sql service and just copy my mdf and ldf then restart the service. Please remember they come from the old server with the letter "d" (if it makes a difference).
My whole backup is 8 GB and it has givin my that message on a couple of files that I am trying to restore.
You can try to Attach the mdf and ldf to SQL from in the Studio Manager.

a. Right click on Databases
b. Select Attach
c. Click Add
d. Browse to your MDF/LDF and select the MDF
e. Repeat d for each database to attack
f. Click OK to attach the databases

You will also need to run some scripts to recreate permissions etc for Dynamics.   Do the above steps first and see if the attach works first.
Avatar of sjs

ASKER

I am in the proces of copying the the ldf and the mdf to c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
What should I do with the Dynamics with the wrong name.  I name it GPSDYNAMICSlpg.ldf instead of
GPSDYNAMIClog.ldf
Avatar of sjs

ASKER

ALRIGHT I did it to one and it worked I will copy the rest and do them next.  What kind of script I need to run on DYNAMICS?  Thanks for all the help you are giving me.
Located at C:\Program Files\Microsoft Dynamics\GP\SQL\Util

You will need to run both Grant.SQL and DEXREQ.SQL using the following isntructions:

" Run the Dex_Req.sql script against the Master database. Run the Grant.sql script against the Dynamics database and against all company databases.

Then you will need to make sure that all your databases have the owner 'DYNSA', run the following command to change the owner for each database:

sp_changedbowner 'DYNSA'
Avatar of sjs

ASKER

I appologize but I'm not familiar with executing these scripts.  Can you give me more info or a link for more detailed info.
Thanks
Click on the Dynamics database in the tree, Just click the New Query button near the top left.

A new window will pop up where you can paste in the script.  (open the script in notepad, copy and paste it in).   You will see a drop down box for database along the top.  Make sure the correct database is selected, and click Execute button.   The script should run.

Run the scripts against each database as described in my last post.  Let me know your results
Avatar of sjs

ASKER

I don't see  C:\Program Files\Microsoft Dynamics\GP\SQL\  
What version of GP are you using?
Avatar of sjs

ASKER

version 9
This is the second attempt to post these files
dex-req.txt
Grant.txt
Avatar of sjs

ASKER

I will try that, but before I do, I may still be able to access the old server files.  should I try to copy the master database files to the new server.  Stop the sql services copy master files and restart services.
Avatar of sjs

ASKER

I get the error message "cannot find the principal 'DYNSA', because it does not exist or you do not have permission".
I don't find in:
master nor under sql security
ASKER CERTIFIED SOLUTION
Avatar of James Glaubiger
James Glaubiger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sjs

ASKER

Opster,
Thank you for all your help.  Since it was critical to get it working.  I called MS.  The only thing was missing is that I needed to create the DYNSA acount before running the scripts
Here's what went on:
1 - Move the databases to new server
2 - Attached and then restored a latest backup.  
3 - create the DYNSA account
4 - run the scripts that you (MS provided the same ones) as you have described.