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 a Database Restore
Automate 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 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 creating other 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.
Comparing SQL Server Native Backup Times to SnapManager Backups
What are the backup and restore time differences between different implementations of SnapManager (snapshots vs. streaming) and native SQL Backups? In this tip we look at a few comparisons.
Configuring Snap Backups with SMSQL for NetApps and SQL Server
I've configured my server to use SnapManager for SQL (SMSQL) and installed SQL Server, SnapDrive, and SMSQL. In this tip we cover how to configure backups.
Considerations for Implementing SnapManager for SQL Server
Our company virtualized a large number of SQL Servers to NetApp appliances. The infrastructure team purchased NetApp's application Snap Manager for SQL Server (SMSQL) for snapshot backup and recovery. I'm a DBA, not a storage administrator! In this tip we cover some best practices prior to implementing this backup tool.
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 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?
Estimating Data Compression ratios for all
One of my favorite features with SQL 2008 has been Data and Backup compression (which I discuss in more detail technically here) - this is not only because of the actual functionality it brings to the table, but also because of all the technical intricacies that it involves and the impact it can have on many other fun topics (fragmentation, storage, internals, etc.). Of course, the functionality is pretty cool too...
Function to Return Default SQL Server Backup Folder
I have a number of scripts I run against new SQL Server installs to, among other things, create my SQL Server Agent backup jobs. Typically, the jobs for my backup processes are all the same, across my environment with the exception of a few variables. One of those is the default backup folder where I plan to store my backup files locally prior to moving them from disk to tape for long term, offsite storage. In the past I've had to do one of two things when it came to replacing the references to the backup directory in my code.
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 monitor backup and restore progress in SQL Server 2005 and 2008
My developers often ask me to create a database backup before any critical deployment or to restore a database to one of the test environments. They often ping me during the process to know the progress and when it will be completed. Sometimes the backup/restore is running through a SQL Agent job which looks hung and I want to know if it's actually doing something. In this tip I have tried to put forth a solution to monitor your backup/restore progress.
How to Restart an Interrupted SQL Server Database Restore
One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He was restoring a large database on a Failover Cluster Production Server and while the restore was in progress, due to network failure, the backup failed. Once the SQL Server came up on the other node all the databases came up, except for the database which he was restoring prior to the failover. In this tip we will take a look at the command RESTORE DATABASE…WITH RESTART to see how this command can be helpful during such scenarios.
Identify when a SQL Server database was restored, the source and backup date
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.
Issues with SQL Server backup log with no_log or truncate_only
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?
Large log backups with bulk operations and read only databases
Migrating a VLDB in SQL Server with Log Shipping
You just had a great weekend. You go grab your coffee and before you take a sip a manager grabs you and says, "we need your help." We need to migrate a very large database to our new data center. We need to keep the data in sync and have a short period of downtime when we cutover. In this tip I walk you through how I did this using log shipping.
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.
Options to Improve SQL Server Backup Performance
Database systems seem to be growing almost exponentially every year and as a DBA one of our primary responsibilities is to make sure this data is backed up regularly. In this tip we will look at options in SQL Server 2008 that can decrease the duration of your backups and/or reduce the overhead that this process has on your system.
Point in Time Recovery Using New Timeline Feature in SQL Server 2012
SQL Server backups are key to recovering from a disaster or some type of data failure, but the real magic happens when the backup is restored. In this tip we look at a new enhancment in SQL Server 2012 for point in time recovery using the new timeline feature.
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?
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 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 2008 Backup Compression
Databases keep getting larger, and the time we are given for backups to complete seldom keeps pace. With SQL 2008 you have the ability to compress your database backups, saving disk space but more importantly saving time.
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 Backup Tutorial
In this tutorial we will step through a number of key SQL Server backup topics that all SQL Server Professionals should know. The tutorial high level outline is: Recovery Models, Backup Types, Backup Commands and Creating Backups. Check out this tutorial to get your SQL Server backups started in the right direction.
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?
SQL Server Restore Tutorial
In this tutorial we will step through a number of key SQL Server restore topics that all SQL Server DBAs should know. The high level tutorial outline is: restore commands, restore options and exclusive access to the database. Check out this tutorial to make sure you know what to do when you need to restore a SQL Server database.
Standardize SQL Server backups and maintain existing job schedules
After starting a new SQL Server DBA position I discovered that our servers, and there were hundreds, all had backups running, but they were all different. Some had backups to local storage, some across the network or to the SAN, and the list went on and on. I wanted to change them all to a standard script and keep the existing schedules, so I needed to find a way to do this without manually accessing every SQL Server. Check out this tip to learn about how to standardize your SQL Server backups.
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.
Striping SQL Server Database Backups
As databases get larger, the time for database backups to complete grows as well. Unfortunately, the time window for your backups may not increase. It could very well be the case that you reach a point where your backups will not finish in a specified amount of time.
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.
Why Can't I Restore a Database to an Older Version of SQL Server?
We recently upgraded one of our SQL Server to a newer version. When I took the backup from one of the databases on that server and attempted to restore it to our test restore server (where we verify backups), I received an error. The test server wasn't upgraded to the newest version, but there's nothing in the database that's incompatible with the older version. I walked through the prompts and it seemed like SQL Server recognized the backup just fine. However, when it went to actually do the restore, it failed. What am I doing wrong?
