SQLSafe Freeware Edition – Simple Backup
In a previous post, I blogged about the freeware edition of SQLSafe which can be used to compress and speed up backups and restores of SQL Databases.
Here is a very simple script which will backup a database using the SQLSafe extended stored procedures
EXEC [master].[dbo].[xp_ss_backup] @database = 'AdventureWorks', @filename = 'D:\Backup\AdventureWorks.safe', @backuptype = 'Full'
The variables are fairly self explanitory, however @Database is the database to back up, @filename is the full path and filename of the backup filename and @Backuptype is for Full, Differential, Log or File.
More can be done with this particular XP, such as adding a name and description to the backup not truncating the transaction log etc – but all this can be taken from the sample script that is included with the installation.
One, important thing to note is that if you are trying to backup a database on a named instance of SQL rather than teh default instance, then you HAVE to specify the instance name in your backup script, otherwise the XP will try to connect to a default instance – definatley something to be wary of, especially in a restore statement.! See below for an example
EXEC [master].[dbo].[xp_ss_backup] @database = 'AdventureWorks', @Instancename = 'Instance', @filename = 'D:\Backup\AdventureWorks.safe', @backuptype = 'Full'
Pingback: SQLSafe Freeware Edition - Log Backup « Tips For Microsoft SQL Server
I was directly to create a SSIS package that runs a SQL Safe script that does a backup and copy of a SQL Server database. Do you know if this is even possible and if so how should I go about it?
Hi Sean,
It’s not something I’ve done, however as the script simply runs some extended SP’s, you should be able to create an “Execute SQL Task” within SSIS and put the script in there.
Thanks Jack,
That is pretty much where I had came to as a solution. Unfortunately, I have just started this job this week and my manager wants me to work this all out without having any means of testing it. Meaning I can’t run the script because it would make changes to the Production system and he isn’t giving me access to any testing/staging servers. I know the script has to be run in SQLCMD mode which makes me think that I need to run the script by adding a Execute Process Task. I know the below code will run the script through implementation of a Job on the server – at least I believe so because I ran it using a basic update table statement on my local install. Do you think this would work by just implementing a SQL Task? How would the package call out to the scripting file on the server? I was hoping to implement the package without having to have a seperate file.
EXEC master.dbo.xp_cmdshell ‘sqlcmd -S Server\Insstance -E -i “C:\Script.sql”‘
Hi Sean,
The SQLSafe script doesn’t need to be run in SQLCMD mode, you can just run it as a T-SQL statement – I’ve used it as a T-SQL job step.
If I understand correctly, you want to backup a database with SQLSafe and copy the backup file somewhere.? If so, perhaps the easiest method would be to backup to where you want the file, perhpas a unc path? If you have to do it in SSIS, an “Execute SQL Task” for the backup, and then a “File System Transfer Task” to copy the file might be acceptable.
Just to add, while all the information I provide has worked for me in my environements, I cannot guarantee that you will have the same results – so I am not responsible for any data loss, corruption or if you server self-destructs.
I also wouldn’t reccommend running anything on a production system without prior testing.