move sql 2005 database to another sql 2005 database - no management studio or sa password


got a dog with fleas..

client has a sql 2005 sp2 database on a unstable win2k3 sp2 server. they wish to move that sql database to another sql 2005 sp2 server, however they do not access to Management Studio on the source server nor do they have the sa password for either environment.

mdf an ldf files are available to be physically moved.

need a safe, clear cut method of moving this data without getting jammed up as well as a method to perform automated backups via management studio on the target sql server.

thank you.
Who is Participating?
David ToddConnect With a Mentor Senior DBACommented:

To move/copy the existing mdf & ldf files - first stop _all_ relevant SQL Services.

Now depending on version of SQL, the fact that you don't have the sa password isn't critical, as in older versions the local admin had sysadmin rights.

While not a biggie on the source system, I'd be really hesitant to move forward on a system where I didn't have a sysadmin login, preferably a windows group or user.

All that to say, if you can't get to a sysadmin level of control on the destination system, run away. If you have to, install another instance of SQL.

Okay, so after getting all that figured out, you should be able to attach the copied mdf & ldf files. Do check that there aren't any additional files or filegroups or anything.


PS Automated backup - for SQL 2005+ systems I like Ola Hallengren's maintenance script.
whatever you do I suggest you first make a copy of the whole drive.
Now to actually move your files you need to copy .mdf .ndf and .ldf files
you should move them to the same path on the new machine to avoid any complications.

Note: I haven't really done this before, but I believe it will work.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
If the security is working properly you should not be able to do anything without the "sa" password.

Normally you would use SSMS (free download from MS) to back u the database on the old server and then restore it on the new server.

Note: You can install SSMS on any workstation on the network. You can  manage any SQL Server instance on the network if you have the proper credentials to log on.

... as well as a method to perform automated backups via management studio on the target sql server
It is my understanding per the EE rules that the additional question about automated backups should be in a separate question.
Eugene ZConnect With a Mentor Commented:
you to be DBA on this box with all  windows \Sql rights

1. run use yourdb select * from sysfiles  to see where these files
2. stop sql server ( if you can)  or detach DB
3. copy mdf \ldf files
4. attach DB

in order to set auto backup
please select @@version and post it

SQL Server 2000/2005: Automated Database Backups and Restorations

for express edition:
How to Automate the Backup of a SQL Server 2005 Express .
jlaveryAuthor Commented:
client decided not to move forward with the activity. shared the points with what was determined to be the best answers available and rewarded their efforts
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.