Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Operating system Error 112


Operating system Error 112

Author
Message
Hotfix
Hotfix
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 651
Comments posted to this topic are about the item Operating system Error 112
GilaMonster
GilaMonster
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58657 Visits: 44718
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, MVP, M.Sc (Comp Sci)
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


philcart
philcart
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 1435
Eck ... totally agree Gail, we have certainly dropped the standards a bit :-D

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
Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2031 Visits: 797
Might also mention that WITH TRUNCATE_ONLY is not available in SQL Server 2008.
jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1188 Visits: 5009
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).
magarity kerns
magarity kerns
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 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.
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4073 Visits: 1619
Intentions might be good of this writer but advice is horrible. Backup log WITH TRUNCATE_ONLY.w00t

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

SQL DBA.
Hotfix
Hotfix
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 651
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
magarity
magarity
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58657 Visits: 44718
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search