| Author |
Topic |
|
sgondesi
Posting Yak Master
200 Posts |
 Posted - 2013-12-16 : 12:45:12
|
| I am trying to copy the data and log file from "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008\MSSQL\DATA"to any other drive. Iam getting the following error."The action cant be completed because the is open in SQL Server"-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-16 : 13:08:01
|
| Detach the database, copy the files, attach the database. It can be done with the GUI or via sp_detach_db/sp_attach_db.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-16 : 13:45:01
|
quote:
Originally posted by tkizer
Detach the database, copy the files, attach the database. It can be done with the GUI or via sp_detach_db/sp_attach_db.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Thank you madam.Is there any way to make a copy of data and log file to share it with other's.?-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-16 : 13:49:10
|
| Create a backup file and share that.BACKUP DATABASE is the command (also available through the GUI). Consider a COPY_ONLY backup though.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-17 : 03:59:29
|
quote:
Originally posted by tkizer
Create a backup file and share that.BACKUP DATABASE is the command (also available through the GUI). Consider a COPY_ONLY backup though.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Ya that is one way.But we can download the AdventureWorks database from servers right?when i downloaded that database i got an ".mdf" and a ".ldf" file.How would they share those two files?-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-12-17 : 10:18:27
|
quote:
Originally posted by sgondesi
quote:
Originally posted by tkizer
Create a backup file and share that.BACKUP DATABASE is the command (also available through the GUI). Consider a COPY_ONLY backup though.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Ya that is one way.But we can download the AdventureWorks database from servers right?when i downloaded that database i got an ".mdf" and a ".ldf" file.How would they share those two files?-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc.
I'd have the same question, why noy share the mdf/ldf?I'm not saying it's better though.At least you could save time since detatch/attach is faster than backup/restore. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-17 : 12:27:41
|
| I don't see why you'd want to share the mdf/ldf instead of a backup file. Detach/attach requires downtime, and there is the potential for corruption (rare but it does happen).I would argue that detach/attach is not faster. You should already have backups, so you just share the already created backup file. Plus the backup file is going to be smaller than the mdf+ldf, so copying the backup file is going to be faster than the mdf+ldf. Additionally if you used the compression option in your backup jobs, it'll be even smaller.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-17 : 12:30:44
|
| To answer sgondesi's question regarding how to share the mdf and ldf files, you either have to stop the SQL Server service or detach the database. Once either of those is done, then copy the files. Once the copy is done, you can startup the service or attach the database. This is downtime.Backups are an online operation.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-18 : 13:40:03
|
| Some thoughts:I always use BACKUP because:Its a slow job copying (large) MDF/LDF files. Thus I prefer BACKUPsHowever ... doing RESTORE of a database with huge LDF files takes A LONG TIME to pre-create the empty space. (Well ... it does on MY hardware at least!!)I have no idea if the time to pre-create the LDF during RESTORE is longer than the time it would take to physically copy the file instead - it certainly seems like it when I am watching the screen!!, particularly as there is NO thermometer for that bit. (RESTORE goes 10%,20%, ... and when it gets to 90% it just sits there going chug-chug-chug ... until the LDF is built).Of course if the aim is to copy a database from Server-A to Server-B and the link between them is not very fast then copying the Backup file across the link will be faster than copying the (bigger) MDF/LDF files. (Or ZIP it using the fastest compression - otherwise the time to compress/de-compress the file will be a killer. There is only 3%-5% difference between "fastest" and "smallest" compression)But if you just want a copy of a (big) database available on your Server's Disk - so that you can quickly recreate a known-state for a database - then a copy of MDF/LDF and ATTACH might be quicker than RESTORE and all that pre-create of the LDF file.If you are restoring to a pre-existing database, which already has an LDF of the right size, or nearly the right size, then RESTORE won't have any work to do on the pre-create, so should be fastest option of all. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-19 : 07:05:17
|
quote:
Originally posted by tkizer
I don't see why you'd want to share the mdf/ldf instead of a backup file. Detach/attach requires downtime, and there is the potential for corruption (rare but it does happen).I would argue that detach/attach is not faster. You should already have backups, so you just share the already created backup file. Plus the backup file is going to be smaller than the mdf+ldf, so copying the backup file is going to be faster than the mdf+ldf. Additionally if you used the compression option in your backup jobs, it'll be even smaller.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Thank you for the valuable points mentioned in the post.I just felt like knowing how those adventurewords database files are shared. that's it.And i understood the disadvantages of that from your post.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-19 : 07:09:24
|
quote:
Originally posted by tkizer
To answer sgondesi's question regarding how to share the mdf and ldf files, you either have to stop the SQL Server service or detach the database. Once either of those is done, then copy the files. Once the copy is done, you can startup the service or attach the database. This is downtime.Backups are an online operation.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Thank you for your support.I will try that and i will get back to you.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-20 : 07:47:44
|
quote:
Originally posted by Kristen
Some thoughts:I always use BACKUP because:Its a slow job copying (large) MDF/LDF files. Thus I prefer BACKUPsHowever ... doing RESTORE of a database with huge LDF files takes A LONG TIME to pre-create the empty space. (Well ... it does on MY hardware at least!!)I have no idea if the time to pre-create the LDF during RESTORE is longer than the time it would take to physically copy the file instead - it certainly seems like it when I am watching the screen!!, particularly as there is NO thermometer for that bit. (RESTORE goes 10%,20%, ... and when it gets to 90% it just sits there going chug-chug-chug ... until the LDF is built).Of course if the aim is to copy a database from Server-A to Server-B and the link between them is not very fast then copying the Backup file across the link will be faster than copying the (bigger) MDF/LDF files. (Or ZIP it using the fastest compression - otherwise the time to compress/de-compress the file will be a killer. There is only 3%-5% difference between "fastest" and "smallest" compression)But if you just want a copy of a (big) database available on your Server's Disk - so that you can quickly recreate a known-state for a database - then a copy of MDF/LDF and ATTACH might be quicker than RESTORE and all that pre-create of the LDF file.If you are restoring to a pre-existing database, which already has an LDF of the right size, or nearly the right size, then RESTORE won't have any work to do on the pre-create, so should be fastest option of all.
Thank you for sharing your thoughts. They are helpful in my learning.Thanks a lot.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-12-30 : 09:39:43
|
quote:
Originally posted by tkizer
To answer sgondesi's question regarding how to share the mdf and ldf files, you either have to stop the SQL Server service or detach the database. Once either of those is done, then copy the files. Once the copy is done, you can startup the service or attach the database. This is downtime.Backups are an online operation.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Sorry for the delay.I am not able to access my configuration manager all these days. that's why i could not perform the things which you suggested.I did them now. It worked well. Thanks a lot..-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-30 : 13:19:37
|
| I just use Services in Admin Tools to stop/start services. I use Configuration Manager only when needing to add startup parameters, change the service account on a cluster or change the port.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2014-01-02 : 05:19:38
|
quote:
Originally posted by tkizer
I just use Services in Admin Tools to stop/start services. I use Configuration Manager only when needing to add startup parameters, change the service account on a cluster or change the port.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Thank you for sharing most valuable information.But, i am very new to this administration. So, i am sorry to say that i did not understand your terminology used in the above post.I think i need to know about so many things before trying to understand your post.-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
|
Attaching and Detaching database