Solved

Difference Between De attaching database and full backup in sql server

Posted on 2011-09-20
12
236 Views
Last Modified: 2012-06-22
Hi experts

I need to know what is the difference between deatching database and Full DB Backup in SQL server 2005 or 2008.
I am planning to do migration or upgrading ... I know some of u will advice me to user SQL server upgrade adviser , which already I did, but now before to do in side upgrade
I like to do full export or deataching the database as rolled back backup. so what is the difference? and which on is recomeded?

0
Comment
Question by:Haidar1
  • 6
  • 4
  • 2
12 Comments
 
LVL 5

Assisted Solution

by:Alexey Komarov
Alexey Komarov earned 83 total points
ID: 36571398
Hi
when you backup database will be online and users can connect
when you detach database and attach to different server you mast create all users in new server

http://msdn.microsoft.com/en-us/library/ms190794.aspx
0
 

Author Comment

by:Haidar1
ID: 36571417
Hi Alexey
you mean if I do full backup usrs accounts and logins will be backuped up as well.? In this case

Full Backup will be recomended in my acctivity. please advce.?
0
 

Author Comment

by:Haidar1
ID: 36571461

Please correct me if I am mistaken
Full Database backup will take all the obejcts owned by the database like
data files. log files, index, procedurs  logins and uses is that correct?

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Eyal
ID: 36571499
Hi,

If you want your application to stay online as much as possible  and don't want to hassle much
I would recommend you to:  
1) change database to readonly mode so new data can't be applied to it
2) backup (with database users)
3) copy
4) restore in new location
5) change your application to point to the new restored database.

deatching the database will allow you to copy the database files (data files and log files). and as alexeykomarov said it will not move the users because they are located in the master database.
0
 

Author Comment

by:Haidar1
ID: 36571520
Hi Eyal

2) backup (with database users)

You mean full database backup ? Tasks - backup - Full DB backup.?




0
 
LVL 15

Expert Comment

by:Eyal
ID: 36571547
yes. this is needed to move all the database to another location
0
 

Author Comment

by:Haidar1
ID: 36571723

what about logins ? do ineed to recreate them?
0
 
LVL 5

Expert Comment

by:Alexey Komarov
ID: 36571735
Full database backup not include logins
You need transfer logins http://support.microsoft.com/kb/918992 or
 backup and restore master database http://msdn.microsoft.com/en-us/library/ms190679(v=sql.90).aspx
0
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 167 total points
ID: 36571760
login data is stored in the master so it will not be taken with the backup file

see how to generate the users in the new server
http://msdn.microsoft.com/en-us/library/ee406927%28v=bts.10%29.aspx

pay attention: if you have jobs you should script them as well.
0
 

Author Comment

by:Haidar1
ID: 36571817

thanks eyal for your reply But
What if I did full System DB Backup, and full Application DB Backup?
In this way I will take backup for data, logs, procedure, index, login's and users accouts as well
Then I will upgrae from 2005 to 2008 (inside mode)
Then I will restore system DB
Then I will restore applicaotion DB
What you think?
0
 
LVL 15

Accepted Solution

by:
Eyal earned 167 total points
ID: 36571842
if you can do it that would be great  :)

the best practice will be to do a test to see every thing will be passed as expected.
skip the read only mode for the tests you don't want to do that in this stage
0
 

Author Closing Comment

by:Haidar1
ID: 36572579
Thankd to all I will implemented in my activities
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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