Solved

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

Posted on 2011-02-26
13
304 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:jazzIIIlove
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34987094
>> 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
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34998475
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35047130
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35051352
>> 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
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35063186
How can i retrieve the mdf as I cannot see mdf?

Kind regards.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35063574
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
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35063660
>>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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35063678
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
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35070459
ok, i find it, how to import it?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 35077371
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
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35089332
>>copy mdf and ldf files..
to where, which location?

I have MSSQL Management Console installed btw.

Kind regards.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35212554
>> 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
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 35222415
no probem :)
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question