Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Operating system Error 112 Expand / Collapse
Author
Message
Posted Friday, April 17, 2009 12:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:20 PM
Points: 34, Visits: 643
Comments posted to this topic are about the item Operating system Error 112
Post #699142
Posted Friday, April 17, 2009 4:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
Oh my, that is some frighteningly bad advice.

Backup log with truncate only? Shrink data files? Both with no mention of the downsides and consequences of them?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #699267
Posted Friday, April 17, 2009 5:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
Eck ... totally agree Gail, we have certainly dropped the standards a bit



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #699287
Posted Friday, April 17, 2009 6:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
Might also mention that WITH TRUNCATE_ONLY is not available in SQL Server 2008.
Post #699326
Posted Friday, April 17, 2009 7:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
I guess it wouldn't be so bad if this was talking about restoring a test databases to a different location - I often find that becuase test databaes aren't always fully maintained their log files can grow. But my guess is this is an example of restoring a production database to a local machine. My recomendation would be to get a proper test server with enough room on it for the required databases(s).
Post #699376
Posted Friday, April 17, 2009 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, Visits: 397
People make external drives FAT32 for a variety of legitimate reasons - switching it to NTFS as the only solution isn't much help and is a pretty well known and simple process. I can think of several ways to move a database copy to another system rather that use the backup and restore method if NTFS to FAT is stumping you.
Post #699410
Posted Friday, April 17, 2009 11:32 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Intentions might be good of this writer but advice is horrible. Backup log WITH TRUNCATE_ONLY.

Dude, are you serious enough to execute it on production ! ! !


SQL DBA.
Post #699652
Posted Friday, April 17, 2009 12:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:20 PM
Points: 34, Visits: 643
I missed a step in including that is, restoring the database to a server where there is enough space and test this hypothesis, execute WITH TRUNCATE_ONLY
and the remaining steps to an NTFS external hard drive.

Thanks for leaving the feedback though.

Mahi
Post #699680
Posted Saturday, April 18, 2009 3:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 30, 2009 6:40 PM
Points: 127, Visits: 66
Three options for not having to convert your FAT32 drive to NTFS and also not having to truncate the log file on the original to save space:

1. Detach and copy the original mdf and lfd files. This works on my SQL Express between NTFS HDD and FAT32 USB. Is there something that prevents this with Standard or Enterprise?

2. Temporarily set up a trust between servers and copy all objects with data. Actually, you can pipe it all through your Management Studio login via the 'import data' or 'export data' if you have login access to both systems (and of course the correct read/write permissions). Depending on your workstation connection this might be too slow if you are working remote, but it is an excellent option for a workstation on the same LAN as the servers or if you can log in to Management Studio with remote desktop to one of the servers.

3. Generate a script that includes all data. Edit the first couple of lines where the 'create database' commands are located to put the mdf and ldf files on the FAT32. Also change the logging to be simple or bulk instead of full logged. Then run the script with sqlcmd on the recipient machine.

In all three, problem solved - the log file does not need to be truncated and the FAT32 does not need to be converted. If the ldf is too big to do #1 then #2 and #3 will work.

Note that if the script file from #3 is exceptionally large due to the amount of data you might have to be creative in how you edit the first few lines. UltraEdit on a Windows system or your choice of a Linux command line editor should do the trick.

In summary, copying an entire database via the backup and restore method leaves a lot to be desired and several cases where problems can crop up. There are ways that are easier, and sometimes they're faster. Method 2 above, for example, does not first output a backup file that must be moved and then read; the output goes straight into the recipient database without an intermediary file.

To summarize the summary: Use backup for backup and use one of the variety of copy methods to copy.

Should I see if I can get this post put up as an article? lol... (I'd have to flesh out the steps a lot more)
Post #699962
Posted Saturday, April 18, 2009 3:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
magarity (4/18/2009)

1. Detach and copy the original mdf and lfd files. This works on my SQL Express between NTFS HDD and FAT32 USB. Is there something that prevents this with Standard or Enterprise?

No, but if you're trying to copy a production DB to a test environment, taking it offline for the duration of the copy may not be an option

2. Temporarily set up a trust between servers and copy all objects with data.

Fine if the source DB is dev, but not so much an option on a busy production DB. The load that the copy will impost may be unacceptable. If there's a downtime window where it can be done, ensure it's big enough.

3. Generate a script that includes all data. Edit the first couple of lines where the 'create database' commands are located to put the mdf and ldf files on the FAT32. Also change the logging to be simple or bulk instead of full logged. Then run the script with sqlcmd on the recipient machine.

Same as above. Fine for a small DB or a dev DB, may be an unacceptable load or take far too long on large, busy production servers

In summary, copying an entire database via the backup and restore method leaves a lot to be desired and several cases where problems can crop up.

True, but if it's a production DB that you're copying, there will always be a backup file available.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #699963
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse