Tips
Auto generate SQL Server database restore scripts
When a failure occurs you need to act quickly to possibly restore your database. When you are performing both full and transaction log backups there are multiple files that will need to be restored and therefore your restore script could get quite long and tedious to write. When using Enterprise Manager or SQL Server Management Studio the GUI gives you the list of files that should be restored, but what if you need to do this manually or you would rather have a script to perform the restore process instead of using the GUI. How can you easily generate the restore script?
Auto generate SQL Server restore script from backup files in a directory
One of the ongoing challenges of a DBA is to backup and restore databases. Backups are done on an automated schedule, but restores can take on many different versions, you may need to restore a production database, restore a development or test database or just create another copy of the database somewhere else. There are several ways of automating the restore process and creating a script, but this approach shows a way this can be done by just reading the contents of a directory for the backup files that exist.
Auto generate SQL Server restore scripts after each backup completes
There are a lot of scripts out there that will allow you to use the msdb to auto-generate restore scripts, but what if you cannot access the msdb database. What is the easiest way to make sure I have the proper restore scripts on hand without relying on the msdb database?
Automate Restoration of Log Shipping Databases for Failover in SQL Server 2000
When failing over to a standby server in a log shipping pair, we need to restore the latest transaction log backup to make the standby databases ready for access. This process may be cumbersome if it involves manually restoring more than five database in a single server while at the same time getting the standby server available as fast as we can. We need to automate the process of identifying the log shipping databases on the standby server, location of the transaction log backups being shipped from the primary server, and the latest transaction log backup that was restored so we can use that to recover the databases. How do we do it?
Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files
As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server 2005 Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server 2005 Express Edition similar to how we do it in other editions?
Automating the SQL Server RESTORE VERIFYONLY Process with Maintenance Tasks
No doubt you likely have a monstrous database infrastructure to manage and the tasks required to keep it maintained and healthy can be daunting. For that reason those tasks should not be performed all in one sitting, nor during peak-hours of use. As a general rule, maintenance tasks should be performed during down times or periods of relative inactivity, which likely means you aren't sitting in the office when it needs to be done.
Taking time to think about maintenance strategy and dividing up the tasks into smaller, manageable chunks saves resources on your server and allows for reaction to issues that arise. The first and most important aspect of database maintenance is the backup and restore strategy. To make sure your backup files a
Automating Transaction Log Backups for All SQL Server Databases
Maintenance plans are a great thing, but sometimes the end results are not what you expect. The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code. For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups. What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?
Backup and Restore SQL Server databases programmatically with SMO
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.
Backup to multiple files for faster and smaller SQL Server backup files
Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.
Changing the default SQL Server backup folder
When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups. Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?
Check for full SQL Server database backups before generating any other types of backups
For very large databases, we usually include either differential, file or filegroup backups together with the full database backup in our backup plan. In some cases, differential backups are used before a major change is implemented in the database. The backups can, then, be used to rollback those changes if and when necessary. The challenge with using these other types of backups is that they require a full backup as the starting point for the restore process. In this tip I will show you how to make sure you have a copy of the full database backup before generating other types of backups.
Copy Only Backup for SQL Server 2005 and SQL Server 2008
I have implemented a backup plan for my SQL Server databases. I take a daily full backup supported with hourly differential backups. A restore plan is documented based on this backup plan along with the location and time of the backups mentioned in the plan. Often I am required to update the test or development server with a recent copy of the production database. For this purpose I take a full backup of the required database. The problem is that such ad hoc backups interrupt my planned recovery sequence in case of a needed recovery. Is there any way that my ad hoc backups will not interrupt the sequence of my backup plan?
COPY_ONLY Backups with SQL Server 2005
One issue with creating backups is that the LSNs (log sequence numbers) get stored in the backup files and these LSNs are referenced when you do a restore of your database. With SQL Server 2005 a new backup option COPY_ONLY has been added that allows you to take full and transaction log backups in between your regularly scheduled backups without affecting the LSNs and therefore the sequence of files that would need to be restored.
Differential Database Backups for SQL Server
I know the 'typical' backups are full database backups executed every day. I have looked into transaction log backups and I am not sure if that is something I can support. I have seen some information on differential backups, but I am not sure exactly how they work, how they are different and how they could help me? Could you please explain some of the considerations with differential backups to determine if they would be beneficial to me and my situation?
Do you really know if your SQL Server database backups are successful?
Are you absolutely sure? Are you? After hearing a presentation from Microsoft's Paul Randall, at a recent Northern Virginia SQL Server Users Group session, I wondered how many DBAs\Developers\Network Admins realize their SQL Server database backups are failing. According to Paul they find out when it is too late - when they are experiencing corruption and facing lost data after significant amounts of downtime. Both of which are detrimental for the success of the organization and inexcusable for DBAs\Developers\Network Admins responsible for SQL Server Service Level Agreements (SLAs).
Encrypt and safeguard your SQL Server database backups
The SQL Server backup process allows you to perform full, differential, transaction log and file level backups. The problem with all of these backups methods is that the data that is created in the backup is stored in clear text and can easily be comprised if someone cares to take the time to hack through these backup files. In addition, SQL Server makes it very easy for you to move a database from one server to another server by restoring a complete backup or by having the MDF and LDF files and using the attach functionality. So what are the best practices to combat this?
Getting exclusive access to restore SQL Server databases
A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis. This may be a scheduled process or it may be an ad hoc process. One of the things that you need to do when restoring a database is to ensure you have exclusive access to the database otherwise the restore process will not work. What options are there to ensure you have exclusive database access, so the restore process will work without issue?
How to Backup and Restore a SQL Server FILESTREAM Enabled Database
Most SQL Server DBAs have questions about backing up and restoring FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to perform a backup and restore of a FILESTREAM database. This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008. This is followed by examples and scripts to setup the backup and recovery process in your environment.
How to find out how long a SQL Server backup took
Many shops do full backups of their databases as part of a job within SQL Agent, typically as part of a maintenance plan. This job will often times do all database backups in secession. As such, while you may know how long the job takes, you may not know how long any one particular database takes to have a full backup taken. When deploying changes it is advised to take a full backup of a database prior to deploying the change and a common question faced will be how long will it take.
How to identify when a SQL Server database was restored, the source of the backup and the date of the backup
After restoring a database your users will typically run some queries to verify the data is as expected. However, there are times when your users may question whether the restore was done using the correct backup file. In this tip I will show you how you can identify the file(s) that was used for the restore, when the backup actually occured and when the database was restored.
How to stop logging all successful backups in your SQL Server error logs
The SQL Server error log is a great place to find information about what is occurring on your database server. One helpful thing that each log offers is that it shows you all of the failures and/or errors that have occurred since the last start of SQL Server or since the last time you cycled the error log. On the flip side of that, it also shows you success messages such as backups, logins, DBCCs, etc... Although this may be helpful to log all of this information it unfortunately clutters up your error logs pretty quickly especially if you are doing a lot of database backups every day. So how can you turn off all of those successful backup messages.
Issues with running backup log with no_log or truncate_only in SQL Server
On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?
Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008
Last week, when I was performing the quarterly disaster recovery exercise, I realized that the latest Full database backup turned out to be corrupted. I wanted to know if there was a way in which I could avoid such a scenario in the future. In this tip we will look at the usage of the Mirrored Database Backup feature and RESTORE VERIFYONLY command which will help us avoid such an issue to a great extent in the future.
Purging MSDB Backup and Restore History from SQL Server
In one of your recent tips, you mention that SQL Server keeps a history of all backups and restores that have been made in the system. Our organization performs backups and restores frequently and I've noticed our msdb database is very large because of this. How can I purge some of this data and free some database space?
Restore a SQL Server Database to a New Database to Minimize Downtime
At our organization we have a number of existing processes that backup and restore databases for reporting and disaster recovery purposes. As our databases have grown we have began to use a third party product to minimize the backup and restore times. Unfortunately, we need to shrink the restore time even further. I have seen your tip about using differential backups in a log shipping like scenario. I could see how these would require less time and disk space, but it would be a huge process and mentality change for us. We issue full backups across all of our databases and would like to continue to do so for consistency's sake. Do you know of any other options to reduce our backup and restore times?
Scheduling Backups for SQL Server 2005 Express
One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.
Script to find SQL Server databases without transaction log backups
One issue DBAs are faced with is growing transaction log files. One reason transaction logs continue to grow is if you have the database in the full or bulk-logged recovery model and do not issue transaction log backups. In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.
Script to retrieve SQL Server database backup history; last week, most recent and no backups
There is a multitude of data to be mined from within the Microsoft SQL Server system views. This data is used to present information back to the end user of the SQL Server Management Studio (SSMS) and all third party management tools that are available for SQL Server Professionals. Be it database backup information, file statistics, indexing information, or one of the thousands of other metrics that the instance maintains, this data is readily available for direct querying and assimilation into your home-grown monitoring solutions as well. This tip focuses on that first metric: database backup information. Where it resides, how it is structured, and what data is available to be mined.
Selecting the SQL Server database recovery model to ensure proper backups
One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.
Simple script to backup all SQL Server databases
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.
SQL Server 2000 Database Restores Mapping Users to Logins
When restoring a database to a different server there is often the problem of matching up logins and users. The reason for this is that login information is stored in the master database and user information is stored in the specific database you are working with. SQL Server offers several different commands to allow you to relink the logins and the users. Following is a list of the different commands and how they can be used.
SQL Server 2005 Backup Product Options
With the criticality of SQL Server backups, it is necessary to ensure you have the right solution, because many options exist. At a high level, there are three categories: first are native backups, second are products that have a single interface for multiple types of backups and third are specialized backup and recovery products designed for SQL Server.
SQL Server 2005 transaction log backup maintenance plan via SSIS
In SQL Server 2005, when you configure a transaction log backup maintenance task for "all user databases", the transaction log backup task is not automatically checking the Recovery Model of the databases configured to be backed up. The result is that if the recovery model of the database is Simple, the task will simply fail. In SQL Server 2008 this issue has been resolved and only the databases with FULL recovery model will have their transaction logs backed up. One common solution to this "issue" in SQL Server 2005 is coding the transaction log backup task. To me, this is like inventing the wheel (I am the lazy DBA of the gang), so I would like to provide a much simpler solution here, which will also allow reverting back to the default
SQL Server backup and restore of the Resource database
I have been hearing about the Resource database in SQL Server 2005 and that it should be included in our system database backups. Unfortunately, this database does not show up in SQL Server Management Studio for me to include in my database backups. How do we backup and restore the Resource database?
SQL Server backup and restore to network drive
Sometimes there is a need to backup or restore your database to another server because of a lack of available disk space on the current server. In this situation you have no other choice but to either remove unneeded files, find additional local storage or backup/restore across the network to another server. When using Enterprise Manager you can only see the local drives, so how do you address the drives on the other server?
SQL Server Backup History Analysis
Database backups hold primary importance among daily DBA tasks. This task is typically automated through maintenance plans, scheduled SQL Server Agent Jobs or third party tools. With the importance of backups it is necessary to regularly analyze the performance and efficiency of the process. So how can we get insight into the performance of a backup process for any database?
SQL Server Backup Plan
In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The first item on the list was to address and implement a sound backup strategy. This is probably one of the easiest things to take care of, but often the wrong options are selected or the wrong or no backup plan is put in place. This tip will address some of the items you should consider when putting your backup process in place.
SQL Server Database Backup and Restore Failure Notifications
My backups and restores fail infrequently, but when I find out it is too late. How can I find out sooner? SQL Server ships with native alerts that can be configured to notify an operator when a backup or restore failure occurs. Reference the chart below for the SQL Server 2000 backup and restore alerts as well as the link to the T-SQL code below to implement these alerts.
SQL Server Database Backup Retention Periods
As a best practice we issue full SQL Server database, differential and transaction log backups. We have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. The tapes are rotated on a weekly basis in order to maintain 4 weeks of data on the tapes stored off site. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
SQL Server Database Restore Verification Script
I have over night processes on a few different SQL Servers in my environment that are backup and restore related. I have used the scripts on MSSQLTips.com to check if the SQL Server Agent Jobs\Job Steps have run as well as the backup verification code. Some of the backup and restore processes I have written and others I have inherited. Some of the processes seem to be very reliable and others are not. I need a way to validate the restore processes have completed properly just like the SQL Server Agent Job and backup code. Can you provide this script so I can include it in my daily verification process?
SQL Server point in time recovery
At some point, a detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued. So what are the steps in order to do a point in time recovery?
Using the FORFILES Command to Delete SQL Server Backups
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances. This backup process will not only backup all the databases I point it towards, but at the same time will script out the backup commands to a single file in the format of F_YYYYMMDD.sql if the backup process is a full database backup or D|T_YYMMDD_HHMMSS.sql if the backup process is a differential (D) or transaction log (T) backup. These script files are then stored in a subfolder under the backup directory on the SQL Server. The process works great, but I only don't want to keep every .sql file. I cant see the need to keep these scripts after 30 days. Can I automate the file deletion process with T-SQL code?
Verifying Backups with the RESTORE VERIFYONLY Statement
There are a number of ways to make sure data in your organization is highly available, yet technology always seems to find a way of making data disappear. Data backups should be the cornerstone of every organization's disaster recovery plan. So, how do you know whether the backups are actually readable? One way is by issuing a RESTORE VERIFYONLY statement against the backup file.
What is in your SQL Server backup files?
Sometimes you come across SQL Server backup files stored on your file system and it is hard to determine what is in the file. From the filename you may be able to decipher that it is a full backup, differential backup or transaction log backup, but how do you really tell what is in the file. Luckily SQL Server offers a few additional commands that you can use with your database backup files to determine the contents of the backup files. These options include HEADERONLY, FILELISTONLY and LABELONLY.
When was the last time your SQL Server database was restored
Often times we are asked the question "when was the last time my database was restored, and where was it restored from?" In this tip, we will look at some of the system tables that capture restore history information and how you can query these system tables to answer this question.
Top 10
Script to find SQL Server databases without transaction log backups
One issue DBAs are faced with is growing transaction log files. One reason transaction logs continue to grow is if you have the database in the full or bulk-logged recovery model and do not issue transaction log backups. In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.
How to identify when a SQL Server database was restored, the source of the backup and the date of the backup
After restoring a database your users will typically run some queries to verify the data is as expected. However, there are times when your users may question whether the restore was done using the correct backup file. In this tip I will show you how you can identify the file(s) that was used for the restore, when the backup actually occured and when the database was restored.
Backup and Restore SQL Server databases programmatically with SMO
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.
Scheduling Backups for SQL Server 2005 Express
One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.
How to Backup and Restore a SQL Server FILESTREAM Enabled Database
Most SQL Server DBAs have questions about backing up and restoring FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to perform a backup and restore of a FILESTREAM database. This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008. This is followed by examples and scripts to setup the backup and recovery process in your environment.
Simple script to backup all SQL Server databases
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.
Auto generate SQL Server restore script from backup files in a directory
One of the ongoing challenges of a DBA is to backup and restore databases. Backups are done on an automated schedule, but restores can take on many different versions, you may need to restore a production database, restore a development or test database or just create another copy of the database somewhere else. There are several ways of automating the restore process and creating a script, but this approach shows a way this can be done by just reading the contents of a directory for the backup files that exist.
Changing the default SQL Server backup folder
When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups. Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?
Selecting the SQL Server database recovery model to ensure proper backups
One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.
Issues with running backup log with no_log or truncate_only in SQL Server
On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?
Last 10
Script to find SQL Server databases without transaction log backups
One issue DBAs are faced with is growing transaction log files. One reason transaction logs continue to grow is if you have the database in the full or bulk-logged recovery model and do not issue transaction log backups. In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.
Check for full SQL Server database backups before generating any other types of backups
For very large databases, we usually include either differential, file or filegroup backups together with the full database backup in our backup plan. In some cases, differential backups are used before a major change is implemented in the database. The backups can, then, be used to rollback those changes if and when necessary. The challenge with using these other types of backups is that they require a full backup as the starting point for the restore process. In this tip I will show you how to make sure you have a copy of the full database backup before generating other types of backups.
How to identify when a SQL Server database was restored, the source of the backup and the date of the backup
After restoring a database your users will typically run some queries to verify the data is as expected. However, there are times when your users may question whether the restore was done using the correct backup file. In this tip I will show you how you can identify the file(s) that was used for the restore, when the backup actually occured and when the database was restored.
How to Backup and Restore a SQL Server FILESTREAM Enabled Database
Most SQL Server DBAs have questions about backing up and restoring FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to perform a backup and restore of a FILESTREAM database. This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008. This is followed by examples and scripts to setup the backup and recovery process in your environment.
Backup and Restore SQL Server databases programmatically with SMO
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.
Restore a SQL Server Database to a New Database to Minimize Downtime
At our organization we have a number of existing processes that backup and restore databases for reporting and disaster recovery purposes. As our databases have grown we have began to use a third party product to minimize the backup and restore times. Unfortunately, we need to shrink the restore time even further. I have seen your tip about using differential backups in a log shipping like scenario. I could see how these would require less time and disk space, but it would be a huge process and mentality change for us. We issue full backups across all of our databases and would like to continue to do so for consistency's sake. Do you know of any other options to reduce our backup and restore times?
Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008
Last week, when I was performing the quarterly disaster recovery exercise, I realized that the latest Full database backup turned out to be corrupted. I wanted to know if there was a way in which I could avoid such a scenario in the future. In this tip we will look at the usage of the Mirrored Database Backup feature and RESTORE VERIFYONLY command which will help us avoid such an issue to a great extent in the future.
Copy Only Backup for SQL Server 2005 and SQL Server 2008
I have implemented a backup plan for my SQL Server databases. I take a daily full backup supported with hourly differential backups. A restore plan is documented based on this backup plan along with the location and time of the backups mentioned in the plan. Often I am required to update the test or development server with a recent copy of the production database. For this purpose I take a full backup of the required database. The problem is that such ad hoc backups interrupt my planned recovery sequence in case of a needed recovery. Is there any way that my ad hoc backups will not interrupt the sequence of my backup plan?
SQL Server 2005 transaction log backup maintenance plan via SSIS
In SQL Server 2005, when you configure a transaction log backup maintenance task for "all user databases", the transaction log backup task is not automatically checking the Recovery Model of the databases configured to be backed up. The result is that if the recovery model of the database is Simple, the task will simply fail. In SQL Server 2008 this issue has been resolved and only the databases with FULL recovery model will have their transaction logs backed up. One common solution to this "issue" in SQL Server 2005 is coding the transaction log backup task. To me, this is like inventing the wheel (I am the lazy DBA of the gang), so I would like to provide a much simpler solution here, which will also allow reverting back to the default
How to find out how long a SQL Server backup took
Many shops do full backups of their databases as part of a job within SQL Agent, typically as part of a maintenance plan. This job will often times do all database backups in secession. As such, while you may know how long the job takes, you may not know how long any one particular database takes to have a full backup taken. When deploying changes it is advised to take a full backup of a database prior to deploying the change and a common question faced will be how long will it take.
