April 26, 2016 at 9:43 am
Just for ***** & giggles I ran BACKUP DATABASE [dbname] TO DISK = 'Nul' on a test instance in VM hosted on my home computer. It took about same amount of time as it takes to create an actual backup (normally 4GB) and there was a lot of disk activity as if it were writing a backup file (on the system drive rather than the backup drive), but I have no idea what it was doing.
June 18, 2018 at 4:17 am
I realize this is a very old thread, but people do read archived posts for information, so I'm going to add my two cents worth. Adjust for inflation as necessary.
I use this technique of backing up to the nul device quite a lot. I have two databases: Production and Development. Development always starts as an exact copy of Production. I work on stuff there, test application code against it, and frequently trash things. When the data there is either no longer usable, or I simply want to have the latest real data to test against, I run a stored procedure that empties every table in Development, does a real backup of the empty structure, then copies, table by table, everything from Production over to Development. Naturally, this procedure has to be modified as I go, to account for the differences in the new Development version. The last step in the copy procedure is a backup of the newly copied Development data and log to the nul device.
When I am happy with how Development is working, I choose a time when no users are connected (night, weekend, holiday, field work day...), make a full backup of Production, run my data-copy procedure, make a full backup of Development, remove old Production, restore the just-backed-up Development to Production, back it up again and continue using Development with it's new copy of Production data for further development work. This creates a fresh starting point for the Production backup chain, simultaneously live-tests my restore capability, and my normal backup jobs continue uninterrupted. (And yes, I regularly perform full restore tests on other parts of my backup procedures, to be sure that everything is functional.)
During one development cycle, I may run this copy procedure dozens of times. If I allowed everything to accumulate, I would have a massive transaction log, or a huge pile of completely useless backups. Backing up Development to the nul device allows me to renew Development data without littering the disk with junk. The manual full backups when I switch to my new version keep the log chains for real data intact.
June 19, 2018 at 3:19 pm
Why not just change the Dev database to the SIMPLE recovery model?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2018 at 4:29 pm
Would that have the same effect as what I'm doing? I have studied backup manuals and online tutorials until I'm blue in the face, but I'm still a little vague on some details. I have a working setup, and I'm a little reluctant to tinker too much, because I'm always short on time, and a little nervous about breaking a working system with live data. This may not be the most efficient way to do it all, but it works, and (I think, anyway) I understand it. I happened to run across this thread while looking for something else, so I put in my comment about how I use the nul device, since that is what the thread addresses.
June 20, 2018 at 7:13 pm
pdanes - Wednesday, June 20, 2018 4:29 PMWould that have the same effect as what I'm doing? I have studied backup manuals and online tutorials until I'm blue in the face, but I'm still a little vague on some details. I have a working setup, and I'm a little reluctant to tinker too much, because I'm always short on time, and a little nervous about breaking a working system with live data. This may not be the most efficient way to do it all, but it works, and (I think, anyway) I understand it. I happened to run across this thread while looking for something else, so I put in my comment about how I use the nul device, since that is what the thread addresses.
I agree and I mean no irony in the following... sometimes it's best to not mess with Mother Nature, especially at the suggestion of a relative stranger on the forum that doesn't know squat about your actual data, etc ( might not be a stranger to you but I know nothing of your systems). If what you have is working and it's not causing you pain nor taking huge amounts of time to keep doing what you're doing, then making a change would require a whole lot that might not make such a change worth it. No matter what the change is, there is always some risk.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply