Sql-server – export a maintenance plan without using Integration Services

Maintenance plans are certainly one way to perform database backups and a multitude of customers use them with success each day. My guess here, looking at the inclusion of the shrink job and the reorganize followed by rebuild that the maintenance plan was just quickly created without understanding the tasks.

When you say you have a "backup database" task - what type of backup is it? My assumption in this answer is that you are taking Full Backups and then Taking Differentials and possibly Log backups.

The problem likely isn't your backup, per se (though I suggest you spend some time understanding recovery models and the backup command at books online as you are likely not creating your backups in the most optimal order for your recovery intent).

I believe you are likely having an issue because you are restoring in this order:

1.) Restore the full database backup (.bak) first keeping all the defaults 2.) Attempt to restore the differential or the log backup.

Or you are just attempting to restore the log or differential on top of an existing database.

If this is the case - the issue is understanding how recovery models work and the proper restore sequence. (One more link to read - about transaction log restores) but in a nutshell:

If you plan on restoring any differentials or log backups, you have to first restore the full backup AND you must specify "WITH NORECOVERY" when restoring that full bakup. If you keep the default (WITH RECOVERY) then SQL Server performs the necessary recovery processes to make your database operational (basically rolls forward transactions that were done and consistent at the time of the backup, rolls back ones that weren't). If you want to restore additional backups (only the latest differential or each log file between the last full or differential backup and the time you want to restore to) then you must specify WITH NORECOVERY for each restore until the last one where you specify WITH RECOVERY finally.

I'd definitely spend some significant time with the Microsoft Books Online links I sent here and click on the links they refer to as well to best understand backup and recovery.

I would suggest reading up on SSIS at MSDN: Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.

Quote from above link:

SQL Server Integration Services is installed through the SQL Server 2008 Setup program. You can install Integration Services alongside other SQL Server components or you can install Integration Services as a stand-alone component. Integration Services includes both client and server applications.

To use a dedicated server for extraction, transformation, and loading (ETL) processes, we recommend that you install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages. If the ETL server does not have an instance of the Database Engine, you will have to schedule or run packages from a server that does have an instance of the Database Engine. This means that the packages will not be running on the ETL server, but instead on the server from which they were started. As a result, the resources of the dedicated ETL server are not being used as intended. Furthemore, the resources of other servers might be strained by the running ETL processes.

Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.