How do i do a simple backup and restore in SSIS?
I've been searching google and to my avail i've found nothing. How do i do a simple Database Backup to Database A, and from the Backup File, restore it (incremental backups) to Database B using BIDS? I just wanted to design a control flow to deal with thisSIRIUS
March 19th, 2009 9:12pm

If backup and restore database is one part of your whole process, how about using Script Task to complete the work? Write VB or C# code in Script Task to backup and restore database.
Free Windows Admin Tool Kit Click here and download it now
March 20th, 2009 4:15am

Hi,To backup a database using SSIS, drag and drop a Execute SQL task onto your SSIS package designer. Double click on it and setthe connection type to OLEDB. Set the Connection property to the Master datatabase, or any database for that matter, on your SQL server. The wizard is pretty simple and you should be able to follow it pretty easily.Once done, click on the SQL Statement property of the package, and click the small ellipsis button. A new pop-up window opens. Copy and paste below script in the window, save the changes and you are done!!Script to backup database:------------------------------------------------------------------- Backup database to file-----------------------------------------------------------------declare @backupFileName varchar(100), @backupDirectory varchar(100), @databaseDataFilename varchar(100), @databaseLogFilename varchar(100), @databaseDataFile varchar(100), @databaseLogFile varchar(100), @databaseName varchar(100), @execSql varchar(1000)-- Set the name of the database you want to backupset @databaseName = 'myDatabase'-- Set the path for a directory on SQL server machine where backup files will be stored after backup process-- This can be a path of a shared folder on another machine as well, provided Write permissions are granted-- on this folder.set @backupDirectory = 'aboslute_path_to_backup_directory' -- such as 'c:\temp\'-- Create the backup file name based on the backup directory, the database name and today's dateset @backupFileName = @backupDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'-- Get the data file and its pathselect @databaseDataFile = rtrim([Name]), @databaseDataFilename = rtrim([Filename])from master.dbo.sysaltfiles as files inner join master.dbo.sysfilegroups as groups on files.groupID = groups.groupIDwhere DBID = ( select dbid from master.dbo.sysdatabases where [Name] = @databaseName )-- Get the log file and its pathselect @databaseLogFile = rtrim([Name]), @databaseLogFilename = rtrim([Filename])from master.dbo.sysaltfiles as fileswhere DBID = ( select dbid from master.dbo.sysdatabases where [Name] = @databaseName ) and groupID = 0print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with 'print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'set @execSql = 'backup database [' + @databaseName + ']to disk = ''' + @backupFileName + '''withnoformat,noinit,name = ''' + @databaseName + ' backup'',norewind,nounload,skip'exec(@execSql)----------------------------------------------------------End of Backup Script-----------------------------------------Note: You'll need to change the database name and the directory path in the above script as per your requirements.Hope that helps.Cheers!!Muqadder.
March 20th, 2009 7:04pm

Next , to restore a database from a backup file, Drag and drop a Execute SQL task on your package designer and set the connection to the server/db you want to use (see above post for details). In the SQL Statement windoow, copy and paste below script, save changes and you are done!!Script to restore database:------------------------------------------------------------------------------------------ Restore database from a backup file-- NOTE: If the database gets locked in Single user mode, execute-- the below command on it and it will restore the Multiuser mode:-- ALTER DATABASE yourdatabasename SET MULTI_USER---------------------------------------------------------------------------------------use mastergodeclare @backupFileName varchar(100), @restoreDirectory varchar(100),@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),@databaseDataFile varchar(100), @databaseLogFile varchar(100),@databaseName varchar(100), @execSql nvarchar(1000)-- Set the name of the database to restoreset @databaseName = 'myDatabase'-- Set the path to the directory containing the database backup file.--IMPORTENT:: Make sure theer is a \ character at the end of your directory path below, else-- the script will error outset @restoreDirectory = 'aboslute_path_to_restore_directory' -- such as 'c:\temp\'-- Create the backup file name based on the restore directory, the database name and today's dateset @backupFileName = @restoreDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'-- Get the data file and its pathselect @databaseDataFile = rtrim([Name]),@databaseDataFilename = rtrim([Filename])from master.dbo.sysaltfiles as filesinner joinmaster.dbo.sysfilegroups as groupsonfiles.groupID = groups.groupIDwhere DBID = (select dbidfrom master.dbo.sysdatabaseswhere [Name] = @databaseName)-- Get the log file and its pathselect @databaseLogFile = rtrim([Name]),@databaseLogFilename = rtrim([Filename])from master.dbo.sysaltfiles as fileswhere DBID = (select dbidfrom master.dbo.sysdatabaseswhere [Name] = @databaseName)andgroupID = 0print 'Killing active connections to the "' + @databaseName + '" database'-- Create the sql to kill the active database connectionsset @execSql = ''select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' 'from master.dbo.sysprocesseswhere db_name(dbid) = @databaseNameandDBID <> 0andspid <> @@spidexec (@execSql)print 'Restoring "' + @databaseName + '" database from "' + @backupFileName + '" with 'print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'set @execSql = 'restore database [' + @databaseName + ']from disk = ''' + @backupFileName + '''withfile = 1,move ''' + @databaseDataFile + ''' to ' + '''' + @databaseDataFilename + ''',move ''' + @databaseLogFile + ''' to ' + '''' + @databaseLogFilename + ''',norewind,nounload,replace'exec sp_executesql @execSql---------------End of Restore script---------------------------------------Cheers!!Muqadder.
Free Windows Admin Tool Kit Click here and download it now
March 20th, 2009 7:17pm

Thank you, i will go over the script on monday, but thanks alot, it looks good to go.SIRIUS
March 22nd, 2009 5:36am

Just wanted to add, if you know which database(s) you'll need to backup well in advance, you can use a Backup Database Task found under Miantenance Plan Tasks tab of toolbox. This will be simpler for situations where DBAs need to run a planned backup process, say, every weekend.Cheers,M.
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2009 7:10pm

Some slight adjustments and i got it to work with 2 databases. But it works, thanks.SIRIUS
March 24th, 2009 4:34pm

Is there a way to set the value for one of the declared variables through a user defined variable in SSIS?SIRIUS
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2009 9:15pm

You can use the UserVariables in SSIS package. To do so, click on the package designer area, go to SSIS -> Variables in the options tab. This will show a window where you can add user variables, set their Type and values. Once you set up teh variables, double click on the control in which you want to use it and set a Parameter mapping between the user variable and the variable place holder in the query you want to use it in.If you haven't worked with user variables before, this post will be helpful:http://weblogs.asp.net/rrobbins/archive/2009/01/15/ssis-package-user-variables.aspxCheers,M.
March 26th, 2009 12:51pm

Dar All; Iam trying to backup a database from server A and restore it in server B .. Is that possible? The control of SSIS from Server B. I have used Muqadder script for the backup in the SSIS package from Server B. It backup the DB in server A and store it locally in C:/TEMP. Now i want to pull the DB from Server A and restore it in Server B. I have tried to set the connection through properties but as you know I need to specify 1 server only, and there is no option to add 2 servers, for example: form - to .. Any idea how can i do this using SSIS?
Free Windows Admin Tool Kit Click here and download it now
February 1st, 2011 9:18am

You can put File System Task in between backup and restore task to transfer the backup from server A to server B.
August 14th, 2011 9:12pm

Hi Muqaddar, The backup script worked fine. We are trying to restore the backup on a different instance on the same server. It was throwing error since the new instance did not have the database already. How do we restore the backup file on a different instace with a new database name? Thanks
Free Windows Admin Tool Kit Click here and download it now
December 18th, 2011 2:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics