July 27, 2011 at 4:35 pm
I am pulling a database from a 3rd party contained in a .7z nightly, running a command to extract it and now need a SQL script that will restore this database as the name changes daily.
Does anyone have an elegant script that I can use for this?
Thanks in advance 🙂
July 27, 2011 at 7:02 pm
I don't quite understand. What is .7z ?
Is the database backup file changing nightly, but the databse will ulimately have the same name each day ? Could you clarify ?
July 27, 2011 at 7:52 pm
homebrew01 (7/27/2011)
I don't quite understand. What is .7z ?Is the database backup file changing nightly, but the databse will ulimately have the same name each day ? Could you clarify ?
A .7z extension signifies a 7-zip compressed archive file. The program has become more and more prevalent because it handles many different compression algorithms including .zip and .rar, plus it adds one of its own to the mix, .7z. Read more here.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 8:03 pm
chrisph (7/27/2011)
I am pulling a database from a 3rd party contained in a .7z nightly, running a command to extract it and now need a SQL script that will restore this database as the name changes daily.Does anyone have an elegant script that I can use for this?
Thanks in advance 🙂
Doing this work in T-SQL will require you to enable xp_CmdShell, which I would advise against. It is disabled by default and enabling it not only opens a can of worms in terms of security, it is cumbersome to use for this type of work and will eventually leave you cold in terms of functionality and interaction with T-SQL. Resist the temptation to turn your SQL Server into a scripting platform.
I prefer to handle tasks like this with PowerShell (PS). PowerShell is not only a complete replacement for CmdShell, it offers a full-featured scripting language built on top of .NET. You can call the 7-zip executable in a PS script just as you would from a CmdShell prompt, plus you have a full scripting language at your disposal to determine the name of the resulting backup file and call into SQL Server to issue the restore command.
Since you're on SQL Server 2005, if you have left things at their default levels and have not installed any SQL Server 2008 R2 components, you'll only have PS 1.0 installed on your servers and workstations. I recommend upgrading those to PS 2.0 from here: http://support.microsoft.com/kb/968929
On your workstation you can use the Windows PowerShell ISE that is bundled free within the PS 2.0 download above. It is a PS development app with an immediate window and an interactive debugger...very useful.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 28, 2011 at 10:50 am
Great idea opc, I will be going the powershell route, much easier.
July 29, 2011 at 8:32 am
For anyone interested, the powershell script is the way to go and very easy to configure for this particular purpose:
remove-item d:\database\db_name.bak -force #removes the oldest backup, to "make way" for the latest daily
start-sleep -s 5 #pauses script for 5 seconds
rename-item d:\database\db_name*.bak db_name.bak -force #renames the "db_name20110730.bak" or whatever date iteration to "db_name.bak" that can easily be restored within SQL, no need for a complex script now.
Note that this backup is pulled down nightly and does not need to be retained.
July 29, 2011 at 9:50 am
Elegant, thanks for posting back and sharing your PS experience :satisfied:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply