Solved

de-attach/attach versus backup

Posted on 2009-05-11
10
184 Views
Last Modified: 2012-06-27
I am getting ready to move an application and database off-site. I am going to be receiving weekly backups of the database to store locally. I wanted to leave the current production database alone and restore the weekly backups to a separate location. I was going to just replace the weekly backups with each other leaving the original production database intact. I unfortunately do not have enough room on the server. What is the best way to preserve the local production database as of the time we start processing the application off-site?
0
Comment
Question by:audreymjhha
[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
  • 5
  • 5
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24357980
detach / attach means downtime.
backup is 0 downtime
0
 

Author Comment

by:audreymjhha
ID: 24358496
If I want to preserve my present database as of Monday. I will not be able to keep it on the server because I don't have the space. Should I back it up and then delete the database? Should I detach it and preserve that file? I'm not concerned with downtime?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358522
a simple detach will work to have the "backup".
however, as from the moment you detached, you cannot access the db at all...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:audreymjhha
ID: 24358541
Other than downtime what is the difference between a backup and a detach? Should I do both just to be safe? First create a backup file and then detach it.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358642
backup creates a separate file in a completely different format, only restorable with restore.
however, it can be smaller than the actual database.

the detach will just deregister the database from the instance.

both methods are safe. doing both is double safe, at the cost of double size space used.
0
 

Author Comment

by:audreymjhha
ID: 24364137
If I create a .bak - I can compact it to save space. I guess I would feel better dettaching the database rather than just deleting it. I could also move the database.bak, database.mdf, and database.ldf files to another location. Do the .mdf and .ldf contain the logs and indexes? What is shrinking? Is this like a defrag? I noticed that all the databases have the .mdf and .ldf. So dettaching doesn't create them so I will have to move them to another server to gain back the space. The database is 10 gigabytes. Will I be ok moving just the .mdf and .ldf files? Do you like this idea?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24364326
>Do the .mdf and .ldf contain the logs and indexes?
yes

>What is shrinking?
releasing not used space in the files to the os

>Will I be ok moving just the .mdf and .ldf files?
yes, that is possible
0
 

Author Comment

by:audreymjhha
ID: 24379113
I have 2 SQL Servers that were pre-configured before I came here. I just took the same backup file. One server just has the operating partition and just one data partition. I restored it using a brand new name and it was fine. The second server has an operating system partition, and 2 data partitions. The "live database .mdf goes to the D:" and the "test .mdf, test .log and the live.log" are located on the "E: partition". I put the backup file and tried to restore but I got an error - "Restore Failed - Directory Lookup E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\CompanyData.MDF" failed with the operating system error - the system cannot find the specifed path". Why did I restore successfully on the first server. The .mdf and .log files seemed to be created automatically on the first restore. I tried to copy them over to the second SQL Server but it says they are in use. Do I need to dettach them first? Why would the restore which is on D: try to create or look for a file on E:?
Thank you
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24379178
for the restore, you need to ensure the destination path exists, sql server will not create the folders itself.
also, for the restore, when you choose the advanced tab, you can specify the exact path each file so it fits your needs.

0
 

Author Comment

by:audreymjhha
ID: 24384569
Thank you - that worked. I realy appreciate your help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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