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

Hi,

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.
jlaveryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aboo_sCommented:
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.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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.
0
David ToddSenior DBACommented:
Hi,

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.

Regards
  David

PS Automated backup - for SQL 2005+ systems I like Ola Hallengren's maintenance script.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eugene ZCommented:
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

--
check\try:
SQL Server 2000/2005: Automated Database Backups and Restorations
http://www.codeproject.com/Articles/23737/SQL-Server-2000-2005-Automated-Database-Backups-an



for express edition:
How to Automate the Backup of a SQL Server 2005 Express .
http://www.brianmadden.com/blogs/guestbloggers/archive/2007/05/07/how-to-automate-the-backup-of-a-sql-server-2005-express-data-store.aspx
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.