I want to have the complete backup of MSSQL db implemented in MSSQL 2008, but...

Hi there;

I am using MSSQL Management Studio.

I want to have the complete backup of MSSQL db implemented in MSSQL 2008, and yes I can have the backup file, but the issue is:

I can connect the local server of mine whereas in the other machine that I want to use that backup, I cannot since I fail to connect to server, I am using windows auth. in the server (and can connect successfully in that one).

So, my aim is that I want not only transfer the complete backup of db but also complete settings of the server (that is operating successfully).

I also tried to find mdf or ldf (which I am not sure their relevancy), and even cannot find the files (in the server operating normally). Where are they?

Kind regards.
LVL 12
jazzIIIloveAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
You can't import the mdf and ldf files directly..
Either you need to detach the database and then copy mdf and ldf files or stop SQL Server service and then copy mdf and ldf files..
Or else you can proceed with Backup as I mentioned earlier..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> my aim is that I want not only transfer the complete backup of db but also complete settings of the serve

Follow the steps below to migrate SQL Server completely to a new server..

1. Take a full backup of all user databases from your old server.
2. Take a Script backup of all your Agent Jobs, Maintenance Plans and Linked Servers if any.
3. Take a backup of your Logins using the script given below:
http://support.microsoft.com/kb/918992

4. Restore your User databases in new SQL Server 2008 machine.
5. Restore Agent jobs, Maintenance Plans.
6. Restore Logins script in the destination instance.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jazzIIIloveAuthor Commented:
Guys, thanks for your interest.

Does ldf extension file do good for this situation as I have the ldf file and bak file of the database.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Does ldf extension file do good for this situation as I have the ldf file and bak file of the database.

No, you need to have either latest backup (bak) file or the combination of mdf and ldf to restore your database properly.
0
 
jazzIIIloveAuthor Commented:
How can i retrieve the mdf as I cannot see mdf?

Kind regards.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ideally ldf would be in the same location as that of your ldf..
If you are not sure where your mdf file is, then run this script to know where your mdf is

use ur_db_name
GO

select name, physical_name
from sys.database_files
0
 
jazzIIIloveAuthor Commented:
>>Ideally ldf would be in the same location as that of your ldf..
You mean mdf.right?

to clarify; the command I should apply is:
my db name is dummy

Followings are what I should apply, right?

use dummy
GO

select name, physical_name
from sys.database_files
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, when you run the script above in SSMS -New query window, you would be able to find the physical path on which your mdf file is located.
0
 
jazzIIIloveAuthor Commented:
ok, i find it, how to import it?
0
 
jazzIIIloveAuthor Commented:
>>copy mdf and ldf files..
to where, which location?

I have MSSQL Management Console installed btw.

Kind regards.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> to where, which location?

seems like I missed your comment and sorry for the delay.

You can copy it to any location on your new server and use it accordingly.
0
 
jazzIIIloveAuthor Commented:
no probem :)
0
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.