Click here to monitor SSC
  • Av rating:
  • Total votes: 378
  • Total comments: 94
Grant Fritchey

SQL Server 2005 Backups

20 December 2006

In the opening chapter of Craig Mullin's book, Database Administration, he says "In many ways, business today is data". Within most organizations the person responsible for protecting data is the database administrator… you.

That's right; the entire business is in your capable hands, running on that server that doesn't ever crash, with all those end users that don't ever make mistakes using applications, built by those developers who write faultless code the first time, every time, with the able assistance of that new co-op that has 'sa' privileges thanks to your boss.

OK. Stop crying. There are things you can do to protect the SQL Server data under your care and one of the most important is running regular database backups.

Note: The source code bundle contains the SQL Backup scripts described in this article. To obtain the scripts, simply click the "CODE DOWNLOAD" link in the box to the right of the article title.

Backups

Microsoft, in SQL Server Books Online, defines backups as:

A copy of data that is used to restore and recover data after a system failure

SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. This is a huge topic. At best, I'm going to scratch the surface and give you enough information so you won't start crying again. After reading this, you should be able to set up a reasonable set of backups for your system.

Recovery Models

In order to begin working on backups, the business needs define a database recovery model. In essence, a recovery model defines what you're going to do with the transaction log data.

There are three recovery models: Full, Simple and Bulk Logged. These are pretty easy to define:

  • Simple in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.
  • Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.
  • Bulk Logged – in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.

The two most commonly used modes are Simple and Full. Don't necessarily assume that, of course, you always need to use Full recovery to protect your data. It is a business decision. The business is going to tell you if you need to recover to a point in time or if you simply need the last full backup. It's going to define if your data is recoverable by other means, such as manual entry, or if you have to protect as much as possible as it comes across the wire. You use Simple recovery if you can afford to lose the data stored since the last full or differential backup and/or you just don't need recovery to a point in time. In Simple mode, you must restore all secondary read/write file groups when you restore the primary. You use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn't a transaction log to worry about anyway. You use Full if every bit of the data is vital, you need to recover to a point in time or, usually in the case of very large databases (VLDB), you need to restore individual files and file groups independently of other files and file groups.

With both Simple and full recovery models, you can now run a Copy-Only backup which allows you to copy the database to a backup file, but doesn't affect the log, differential backup schedules or impact recovery to a point in time. I'll try to drill down on as many of these topics as possible through the article, but not the files and filegroups.

Working with Simple Recovery

Enough talk. Let's get down to running backups. Let's assume that we're in Simple recovery on a small to mid-sized database. I'm going to use AdventureWorks for all the sample scripts. To set it to simple recovery:

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

Your simplest backup strategy is to run, at regular intervals, the following SQL Server backup command, which will perform a full backup of the database:

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\Backups\AdventureWorks.BAK'

What's with all the typing you ask? Don't we have GUI tools to handle the work for us? Yes, most simple backups can be performed using SQL Server Management Studio. However, if you want to learn and understand what Management Studio is doing for you, or if you want some fine grained control over what is backed up, how and where, then you're going to have to break out the keyboard and put away the mouse.

The above command will precipitate a basic backup to disk. Most DBAs I know backup to file and then scrape the files onto a tape or some other media. This is because files on disk are simple and quick to recover, whereas media can sometimes be a bit of a pain. For example, we generally have two to three days worth of backups on our file systems for immediate recovery. We only go to the tape systems if we need to run restores for older backups.

What did that command do? It made a copy of all the committed data in the database. It also copied uncommitted log entries. These are used during recovery to either commit or rollback changes that occurred to the data during the backup process.

Copy-only backups

Normally, backing up a database affects other backup and restore processes. For example after running the previous command, any differential backups (a backup that only copies data changed since the last backup) would be using this as the starting point for data changes, not the backup you ran last night. As noted earlier, SQL 2005 introduces a new concept to backups, COPY_ONLY backups, which allow us to keep from interrupting the cycle:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COPY_ONLY;

Already we've found one of those more granular moments when the Management Studio wouldn't help you. If you want a copy only backup, you have to use the command line.

Differential backups

Let's assume for a moment, that we're still in simple recovery, but we're dealing with a larger database, say something above 100 GB in size. Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we've decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\backups\AdventureWorks.bak' 
WITH DIFFERENTIAL;

Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later).

BACKUP DATABASE Adventureworks
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH INIT;

There are a number of other backup options that I won't be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on.

You can also run a statement that will check the integrity of a database backup. It doesn't check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible.

RESTORE VERIFYONLY
FROM DISK = 'C:\backups\Adventureworks.bak'

Full recovery and log backups

We've primarily been working on a database that was in Simple recovery mode (this used to be called Truncate Log on Checkpoint). In this mode, we do not backup the transaction logs for later recovery. Every backup under this mechanism is a database backup. Log backups are simply not possible.

However, you've only protected the data as of the last good backup, either full or differential. Let's change our assumptions. Now we're dealing with a large, mission critical application and database. We want to be able to recover this database up to the latest minute. This is a very important point. In theory, since the log entries are being stored and backed up, we're protected up to the point of any failure. However, some failures can cause corruption of the log, making recovery to a point in time impossible. So, we have to determine what the reasonable minimum time between log backups will be. In this case we can live with no more than 15 minutes worth of lost data.

So, let's start by putting our database in FULL recovery mode:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

Then, on a scheduled basis, in this case every 15 minutes, we'll run the SQL backup command for the transaction log:

BACKUP LOG Adventureworks
TO DISK = 'C:\backups\AdventureWorks_Log.bak';

This script will backup committed transactions from the transaction log. It has markers in the file that show the start and stop time. It will truncate the log when it successfully completes, cleaning out from the transaction log the committed transactions that have been written to the backup file. If necessary, you can use the WITH NO_TRUNCATE statement to capture data from the transaction log regardless of the state of the database, assuming it's online and not in an EMERGENCY status. This is for emergencies only.

Note that we are not using the INIT statement in this case, but you can do so if you choose. When doing log backups, you've got options:

  1. Run all the backups to a single file, where they'll stack and all you have to do, on restore (covered later), is cycle through them.
  2. Name the backups uniquely, probably using date and time in the string.

In that latter case, safety says, use INIT because you're exercising maximum control over what gets backed up where, and you'll be able to know exactly what a backup is, when it was taken and from where based on the name. This is yet another place where operating backups from the command line gives you more control than the GUI. We've used both approaches in our systems for different reasons. You can decide what is best for your technology and business requirements.

Most of the options available to the database backup are included in Log backup, including COPY_ONLY. This would allow you to capture a set of transaction data without affecting the log or the next scheduled log backup. This would be handy for taking production data to another system for troubleshooting etc.

If you have your database set to FULL Recovery, you need to run log backups. Sometimes, people forget and the transaction log grows to the point that it fills up the disk drive. In this case, you can run:

BACKUP LOG Adventureworks WITH NO_LOG;

Attaching NO_LOG to the log backup, and not specifying a location for the log, causes the inactive part of the log to be removed and it does this without a log entry itself, thus defeating the full disk drive. This is absolutely not recommended because it breaks the log chain, the series of log backups from which you would recover your database to a point in time. Microsoft recommends running a full backup immediately after using this statement. Further, they're warning that this statement may be deprecated in a future release.

Restoring Databases

As important as SQL Server backups are, and they are vital, they are useless without the ability to restore the database.

Restoring a full database backup

Restoring a full database backup is as simple as it was to create:

RESTORE DATABASE Adventureworks
FROM DISK = 'C:\Backup\AdventureWorks.bak';

It's really that simple – unless, as we we are backing up everything to a file as if it were a backup device. In that case, you'll need to specify which file within the "device" you're accessing. If you don't know which file, you'll need to generate a list:

RESTORE HEADERONLY
FROM DISK = 'C:\Backup\Adventureworks.bak';

This will give you the same list as I showed above from Management Studio. So now, if we wanted to restore the second file in the group, the COPY_ONLY backup, you would issue the following command:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\Adventureworks.bak'
WITH FILE = 2;

Unfortunately, if you're following along, you may find that you just generated this error:

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorks" has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What this means is, that your database is in full recovery mode, but you haven't backed up the "tail of the log", meaning the transactions entered since the last time you ran a backup. You can override this requirement if you change the previous syntax to:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2,
REPLACE;

That's the first time we've stacked the WITH clauses (WITH FILE=2 and WITH REPLACE is represented as WITH FILE=2, REPLACE), but it won't be the last. Read through the books online. Most of the WITH clause statements can be used in combination with the others.

What happens if we want to restore to a different database than the original? For example, we want to make a copy of our database from a separate backup. Maybe we want to move it down to a production support server where we are going to do some work on it, separate from the production copy of the database. If we take the simple approach, well, try this:

RESTORE DATABASE AdventureWorks_2
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2;

In this case, you should see a whole series of errors relating to files not being overwritten. You really can create new databases from backups, but if you're doing it on a server with the existing database, you'll need to change the location of the physical files using the logical names. In order to know the logical names of the files for a given database, run this prior to attempting to move the files:

RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2;

This can then be used to identify the appropriate logical names in order to generate this script:

RESTORE DATABASE AdventureWorks_2
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2,
   MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_data.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\backups\aw2_log.ldf';

Restoring a differential backup

The last method is to apply the differential backup. This requires two steps. First, we'll restore the database, but with a twist and then we'll apply the differential backup:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = ,
   NORECOVERY,
   REPLACE;

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH FILE = 3;

Most of this is probably self-explanatory based on what we've already covered. The one wrinkle is the inclusion of the NORECOVERY keyword. Very simply, during a restore, transactions may have started during the backup process. Some of them complete and some don't. At the end of a restore, completed transactions are rolled forward into the database and incomplete transactions are rolled back. Setting NORECOVERY keeps transactions open. This allows for the next set of transactions to be picked up from the next backup in order.

We're mainly dealing with simple backups and restores in this article, but a more advanced restore in 2005 allows secondary file groups to be restored while the database is online. Its primary file group must be online during the operation. This will be more helpful for very large database systems.

Restoring SQL Server databases to a point in time

Restoring logs is not much more difficult than the differential database restore that we just completed. There's just quite a bit more involved in restoring to a moment in time. Assuming you're backing up your logs to a single file or device:

RESTORE HEADERONLY
FROM DISK = 'C:\Backups\Adventureworks_log.bak';

Otherwise, you simply go and get the file names you need. First run the database restore, taking care to leave it in a non-recovered state. Follow this up with a series of log restores to a point in time.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 1,
   NORECOVERY,
   REPLACE,
   STOPAT = 'Oct 23, 2006 14:30:29.000';

RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks_log.bak'
WITH FILE = 1,
   NORECOVERY,
   STOPAT 'Oct 23, 2006 14:30:29.000';
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks_log.bak'
WITH FILE = 2,
   NORECOVERY,
   STOPAT 'Oct 23, 2006 14:30:29.000';
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks_log.bak'
WITH FILE = 3,
   NORECOVERY,
   STOPAT 'Oct 23, 2006 14:30:29.000';
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\Adventureworks_log.bak'
WITH FILE = 4,
   STOPAT 'Oct 23, 2006 14:30:29.000';

Now what we have is a database that is up to the exact, last committed transaction at 14:30:29 on the 23rd of October. Remember, during multi-step restores such as this, you have to leave the database in a recovering status. That means appending NORECOVERY to each statement until you've completed the restore process. If for some reason you've added NORECOVERY to all your statements, or you simply stop in the middle, and would like to bring the database back online, you can use this statement to complete the process:

RESTORE DATABASE Adventureworks
WITH RECOVERY;

Database snapshots

SQL Server 2005 introduced the concept of a snapshot, or a read-only, static view of a database. Snapshots are primarily created in order to supply a read-only version of a database for reporting purposes. However, they do function in a similar way to backups. The one primary difference is that all uncommitted transactions are rolled back. There is no option for rolling forward, capturing logs, etc., that backups provide, nor are very many SQL Server resources used at all. Rather, disk technology is used to create a copy of the data. Because of this they are much faster than backups both to create and restore.

NOTE:
For more details on SQL 2005 Snapshot, please refer to http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/.

A good use of snapshots, in addition to reporting, might be to create one prior to maintenance after you've already removed all the active users (and their transactions) from the system. While snapshots don't support the volatility of live backups, their speed and ease of recovery make a great tool for quick recovery from a botched rollout. Snapshots are stored on the server, so you must make sure you've got adequate storage.

The syntax is different because you're not backing up a database; you're creating a new one:

CREATE DATABASE Adventureworks_ss1430
ON (NAME AdventureWorks_Data,
FILENAME 'C:\Backups\AdventureWorks_data_1430.ss')
AS SNAPSHOT OF AdventureWorks;

Now it will be accessible for read-only access. Since we're primarily concerned with using this as a backup mechanism, let's include the method for reverting a database to a database snapshot.

First, identify the snapshot you wish to use. If there is more than one on any database that you're going to revert, you'll need to delete all except the one you are using:

DROP DATABASE Adventureworks_ss1440;

Then you can revert the database by running a RESTORE statement (mixed metaphors, not good):

RESTORE DATABASE Adventureworks
FROM DATABASE_SNAPSHOT Adventureworks_ss1430;  

That's it. On my system, running the database snapshots of Adventureworks took 136 ms. The full backup took 5,670 ms. The restore of the snapshot took 905ms and the database restore took 13,382ms. Incorporating this into a production rollout process could result in significant benefits

Again, it's worth noting that there are some caveats to using the snapshot. You have to have enough disk space for a second copy of the database. You need to be careful dealing with snapshots since most of the syntax is similar to that used by databases themselves. Last, while there are snapshots attached to a database you can not run a restore from a database backup of that database.

Best practices

The manner in which you perform database backups should not be a technical decision. It should be dictated by the business. Small systems with low transaction rates and/or reporting systems that are loaded regularly will only ever need a full database backup. Medium sized systems and large systems become dependent on the type of data managed to determine what types of backup are required.

For a medium sized system, a daily backup with log backups during the day would probably answer most data requirements in a timely manner.

For a large database the best approach is to mix and match the backups to ensure maximum recoverability in minimum time. For example, run a weekly full backup. Twice a day during the week, run a differential backup. Every 10 minutes during the day, run a log backup. This gives you a large number of recovery mechanisms.

For very large databases, you'll need to get into running filegroup and file backups because doing a full backup or even a differential backup of the full database may not be possible. A number of additional functions are available to help out in this area, but I won't be going into them here.

You should take the time to develop some scripts for running your backups and restores. A naming convention so you know what database, from which server, from which date, in what specific backup and format will be very conducive to your sanity. A common location for backups, log, full or incremental, should be defined. Everyone responsible should be trained in both backup and recovery and troubleshooting the same. There are many ways of doing this, but you can find a few suggestions in Pop backs up and Pop Restores.

The real test is to run your backup mechanisms and then run a restore. Then try a different type of restore, and another, and another. Be sure that, not only have you done due diligence in defining how to backup the system, but that you've done the extra step of ensuring that you can recover those backups. If you haven't practiced this and documented the practice and then tested the document, in effect, you're not ready for a disaster.

Summary

Backups within your enterprise should be like voting in Chicago, early and often. Setting up basic backups is quite simple. Adding on log backups and differentials is easy as well. Explore the options to see how to add in file and file group backups and restores to increase the speed of your backups and restores both of which will increase system availability and up time. Keep a common naming standard. Be careful when using snapshots, but certainly employ them. Store your files in a standard location between servers. Practice your recoveries. Finally, to really make your backups sing, download a free trial of Red Gate's SQL Backup™. It offers high-performance compression and network resilience to make the process of writing or copying backups across flaky networks fault-tolerant.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 378 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Excellent
Posted by: RR (not signed in)
Posted on: Friday, December 22, 2006 at 12:39 PM
Message: This write-up is *exactly* what I was looking for. Thanks for a clear and concise article with good insight and suggestions!

Subject: Glad it helped
Posted by: Granted (view profile)
Posted on: Monday, December 25, 2006 at 6:07 AM
Message: Let me know if you have any questions. Remember, test your recovery model.

Subject: Glad it helped
Posted by: Granted (view profile)
Posted on: Tuesday, December 26, 2006 at 9:09 AM
Message: Let me know if you have any questions. Remember, test your recovery model.

Subject: Error on restoring
Posted by: Anonymous (not signed in)
Posted on: Thursday, December 28, 2006 at 12:58 AM
Message: I get the following error message when restoring a database:

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

I only have 1 bak file, after a crash I should recover the database. Is there any chance to do that? All data are included in that file ...

Maybee there is a possibility to extract the single files out from the bak-file and then attach mdf and ldf as new database?

Subject: Hmmm
Posted by: Granted (view profile)
Posted on: Wednesday, January 03, 2007 at 11:15 AM
Message: You've got more than one destination defined for your backup. You'll need to restore from both destinations at the same time.

Effectively you striped your backup.

Subject: hmm
Posted by: Anonymous (not signed in)
Posted on: Wednesday, January 10, 2007 at 8:36 AM
Message: "who right faultless code the first time"
Shouldn't that be
"who WRITE faultless code the first time"

Subject: re: hmm (spelling mistake)
Posted by: Tony Davis (view profile)
Posted on: Wednesday, January 10, 2007 at 10:56 AM
Message: Yes, you are write ;). I have corrected it now.

Tony (Simple-Talk Ed)

Subject: Yet another piece of spam to promote RedGate's backup product
Posted by: Anonymous (not signed in)
Posted on: Thursday, January 11, 2007 at 3:27 PM
Message: Nothing in this article is new. It was written for the sole purpose of promoting Red Gate's back up solution as included in the summary... So why post it in SQL Server Central?



Subject: re: Yet another .......
Posted by: Robyn Page (view profile)
Posted on: Friday, January 12, 2007 at 3:46 AM
Message: I can't answer specifically on this article, but quite a few of us who write articles for Simple-Talk got to know the people at Red-Gate because we initially bought Red-gate products. It is human nature to evangelise any products if you like and trust them, but it is genuinely meant. We're rather like the bar-room bores who go on about how wonderful their cars are. If you have a different view that is interesting to read, and you don't object to peer-review, then why not contact Tony and be persuaded to write an article too? If Steve likes it, I imagine he'd post a link on SSC!

Subject: Article appreciation
Posted by: Mihai Tache (not signed in)
Posted on: Saturday, January 13, 2007 at 1:23 AM
Message: Really concise information.Well done!

Subject: Spam
Posted by: Granted (view profile)
Posted on: Wednesday, January 17, 2007 at 2:47 PM
Message: Well, that was my fault. I wasn't asked to put in the plug, I just did.

Yes, if you read BOL, you can get a lot of what I wrote here, albeit in a different form. Same can be said of most articles I see on most web sites. The basic idea is to attempt to help people who don't/can't/won't read the BOL. With only a few exceptions, if everyone read the BOL, all these web sites would be out of business.

I'd also point out that after 49 votes it's still getting four stars, so some people see this type of article, basic though it certainly is, as valuable.

I agree with Robyn. I'm a geek and I can, demonstrably, go on and on with a topic that will bore most people into a coma.

Subject: re: Article Appreciation
Posted by: Granted (view profile)
Posted on: Wednesday, January 17, 2007 at 2:48 PM
Message: Thanks. Glad it was helpful in any way.

Subject: BACKUP
Posted by: Anonymous (not signed in)
Posted on: Friday, January 19, 2007 at 3:27 AM
Message: i still did not understand the diffrence between simple and full recovery

Subject: Simple vs. Full
Posted by: Anonymous (not signed in)
Posted on: Monday, January 22, 2007 at 1:03 PM
Message: Simply put, simple recovery truncates the log every time the database goes through a checkpoint operation. This means that any ability to recover data from the transaction log is gone. Full recovery only truncates the log afer an explicit log backup command. That's about as fundamental as I can make it.

Subject: Anonymous
Posted by: Granted (view profile)
Posted on: Friday, January 26, 2007 at 6:28 AM
Message: Sorry about that last post being Anonymous. I keep thinking that I'm logged in here and don't check.

Subject: changing data file strcuture
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 30, 2007 at 8:19 AM
Message: I have a data base that has one data file that is 150 GB. I would to backup data base then restore to new loaction with different file strcucture

Subject: changing data file structure
Posted by: Granted (view profile)
Posted on: Tuesday, February 20, 2007 at 2:16 PM
Message: You can't really change the file structure on restore because you can't change the files that objects are assigned to as part of the restore.

What you can do is restore the database, then go and add new files & file groups, etc. After that add the tables or indexes to the new file groups.

Subject: Restore Operation
Posted by: Anonymous (not signed in)
Posted on: Sunday, March 25, 2007 at 11:28 AM
Message: Hello,

thanks for this nice article.
But I think the following sentence is wrong:

"Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later)."

Only one differential backup can be restored.

Despite that, I have some trouble with the following batch in my SQL Server Installation:
DROP DATABASE Test
GO
CREATE DATABASE Test
GO
BACKUP DATABASE Test TO DISK = 'E:\Backup\AW.bak' WITH INIT, NOFORMAT
BACKUP LOG Test TO DISK = 'E:\Backup\AWTail.TRN'
WAITFOR DELAY '00:00:00.711'
RESTORE DATABASE Test FROM DISK = 'E:\Backup\AW.bak' WITH NORECOVERY

About every 5th time I execute this batch, I get the following error msg:
Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "Test" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Without the delay, I dont get the error message.
Would you be able to tell me what I am doing wrong here?

Thanks a lot,
Chris

Subject: Restore Operation
Posted by: Anonymous (not signed in)
Posted on: Sunday, March 25, 2007 at 12:08 PM
Message: Hello,

thanks for this nice article.
But I think the following sentence is wrong:

"Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later)."

Only one differential backup can be restored.

Despite that, I have some trouble with the following batch in my SQL Server Installation:
DROP DATABASE Test
GO
CREATE DATABASE Test
GO
BACKUP DATABASE Test TO DISK = 'E:\Backup\AW.bak' WITH INIT, NOFORMAT
BACKUP LOG Test TO DISK = 'E:\Backup\AWTail.TRN'
WAITFOR DELAY '00:00:00.711'
RESTORE DATABASE Test FROM DISK = 'E:\Backup\AW.bak' WITH NORECOVERY

About every 5th time I execute this batch, I get the following error msg:
Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "Test" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Without the delay, I dont get the error message.
Would you be able to tell me what I am doing wrong here?

Thanks a lot,
Chris

Subject: re Restore
Posted by: Granted (view profile)
Posted on: Thursday, April 05, 2007 at 1:21 PM
Message: Thanks for the correction. You're right.

I have to guess that with the delay included, a transaction is occurring and you're not at the tail of the log any longer.

Subject: Online Backup option
Posted by: Anonymous (not signed in)
Posted on: Friday, April 27, 2007 at 10:44 AM
Message: Hello,

I am not familiar with SQL but very familiar with Oracle. In SQL 2005 database, how I can put the database in online backup mode (quiesced) so I can use my SAN technology (i.e. EMC BCV or NetApp snapshot) to take a snapshot then put the database back in normal mode again? Is there such a way? I know in Oracle, I can mark the database online backup mode, use my SAN to take a snap of the disk volumes that hold the datafiles and archived logs, then put the database back in normal mode.

Any help is much appreciated.

Subject: Online Backup option
Posted by: Anonymous (not signed in)
Posted on: Friday, April 27, 2007 at 10:52 AM
Message: Hello,

I am not familiar with SQL but very familiar with Oracle. In SQL 2005 database, how I can put the database in online backup mode (quiesced) so I can use my SAN technology (i.e. EMC BCV or NetApp snapshot) to take a snapshot then put the database back in normal mode again? Is there such a way? I know in Oracle, I can mark the database online backup mode, use my SAN to take a snap of the disk volumes that hold the datafiles and archived logs, then put the database back in normal mode.

Any help is much appreciated.

Subject: Online Backup option
Posted by: Anonymous (not signed in)
Posted on: Friday, April 27, 2007 at 11:23 AM
Message: Hello,

I am not familiar with SQL but very familiar with Oracle. In SQL 2005 database, how I can put the database in online backup mode (quiesced) so I can use my SAN technology (i.e. EMC BCV or NetApp snapshot) to take a snapshot then put the database back in normal mode again? Is there such a way? I know in Oracle, I can mark the database online backup mode, use my SAN to take a snap of the disk volumes that hold the datafiles and archived logs, then put the database back in normal mode.

Any help is much appreciated.

Subject: Online Backup option
Posted by: Anonymous (not signed in)
Posted on: Friday, April 27, 2007 at 12:13 PM
Message: Hello,

I am not familiar with SQL but very familiar with Oracle. In SQL 2005 database, how I can put the database in online backup mode (quiesced) so I can use my SAN technology (i.e. EMC BCV or NetApp snapshot) to take a snapshot then put the database back in normal mode again? Is there such a way? I know in Oracle, I can mark the database online backup mode, use my SAN to take a snap of the disk volumes that hold the datafiles and archived logs, then put the database back in normal mode.

Any help is much appreciated.

Subject: Online Backup option
Posted by: Anonymous (not signed in)
Posted on: Friday, April 27, 2007 at 1:10 PM
Message: Hello,

I am not familiar with SQL but very familiar with Oracle. In SQL 2005 database, how I can put the database in online backup mode (quiesced) so I can use my SAN technology (i.e. EMC BCV or NetApp snapshot) to take a snapshot then put the database back in normal mode again? Is there such a way? I know in Oracle, I can mark the database online backup mode, use my SAN to take a snap of the disk volumes that hold the datafiles and archived logs, then put the database back in normal mode.

Any help is much appreciated.

Subject: sql 2005
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 08, 2007 at 11:32 PM
Message: get the following error message when restoring a database:

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

I only have 1 bak file, after a crash I should recover the database. Is there any chance to do that? All data are included in that file ...

Maybee there is a possibility to extract the single files out from the bak-file and then attach mdf and ldf as new database?

Subject: Online Backup Option
Posted by: Granted (view profile)
Posted on: Thursday, May 10, 2007 at 8:53 AM
Message: I am honestly not that familiar with SAN technology. I know that we don't do that with our current SAN system. I'll see if I can find someone around here that knows the answer.

Subject: re sql 2005
Posted by: Granted (view profile)
Posted on: Thursday, May 10, 2007 at 8:57 AM
Message: It sounds like you ran the backup through the GUI and had two destinations configured when you ran the backup. The only way to restore that backup is to have both destinations available. Try rerunning the backup and make sure you only have a single destination.
There is a way to extract individual backup files from a set. The problem is they're still backups, a different binary storage format, not simply a copy of of the mdf & ldf files that make a database. So this wouldn't help you.

Subject: SQL 2005 Backup
Posted by: Hamid (not signed in)
Posted on: Wednesday, May 23, 2007 at 2:11 PM
Message: This is very helpuful. I have been looking for something like this. It is really interesting.

Thanks a million.

Subject: SQL 2005 Backup
Posted by: Hamid (not signed in)
Posted on: Wednesday, May 23, 2007 at 2:39 PM
Message: This is very helpuful. I have been looking for something like this. It is really interesting.

Thanks a million.

Subject: SQL 2005 Backup
Posted by: Hamid (not signed in)
Posted on: Wednesday, May 23, 2007 at 2:46 PM
Message: This is very helpuful. I have been looking for something like this. It is really interesting.

Thanks a million.

Subject: SQL 2005
Posted by: Hamid (not signed in)
Posted on: Wednesday, May 23, 2007 at 2:48 PM
Message: Sorry, this page disappeared for some reason and I kept clicking on the "Refresh button". Then multiple entries of my comments were added.

oops...

Subject: It's cool
Posted by: Granted (view profile)
Posted on: Friday, May 25, 2007 at 2:24 PM
Message: Extra praise is a good thing.

Subject: Transaction Logs
Posted by: Newbie (not signed in)
Posted on: Thursday, June 14, 2007 at 11:01 AM
Message: Ok - so I am one of those that didn't get my transaction log backups correct the first time around and now it is huge! I now have my logs on a maintenance plan, but the log is not getting any smaller... You mentioned in your article that "BACKUP LOG Adventureworks WITH NO_LOG;" could be used, but was, in my terms, spooky. Are there any other options to reduce my transaction log? Or recommended reading that I might get a better understanding of the relationship between DB, Transaction logs and back ups?

thx,
c

Subject: Transaction Logs
Posted by: Granted (view profile)
Posted on: Thursday, June 21, 2007 at 2:11 PM
Message: After you've backed up the logs, they don't shrink automatically. Whatever size they've grown to is where they'll remain until you manually shrink the file. Don't use that backup log with no_log unless it's an emergency. Just run the regular log backup, checkpoint, run the log backup again then you should be able to shrink the file.
As to extra reading, BOL, of course, and Inside SQL Server the Storage Engine by Kalen Delany will give you lots of detail.

Subject: SQL SERVER 2005 BACKUP
Posted by: FADHL ALSHAREE (not signed in)
Posted on: Friday, July 20, 2007 at 1:16 PM
Message: THANK YOU VERY MUCH FOR YOUR SERVICES.

Subject: MOVE in Restore
Posted by: Ken (view profile)
Posted on: Friday, July 20, 2007 at 8:09 PM
Message: In the last part of "Restoring a full database backup", you have

MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_log.mdf', MOVE 'AdventureWorks_Log' TO 'C:\backups\aw2_log.ldf';

Shouldn't the first one be this?
MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_DATA.mdf',

Subject: Re: MOVE in Restore
Posted by: Granted (view profile)
Posted on: Thursday, August 09, 2007 at 8:09 AM
Message: Oops!

Yes, you're right of course. Thanks for pointing it out.

Subject: hi
Posted by: Anonymous (not signed in)
Posted on: Friday, October 12, 2007 at 4:05 AM
Message: why u not modify the changes in the article after the comments pointed that mistakes.correct the mistakes.other than it is a good and useful article

Subject: Re: Changes
Posted by: Granted (view profile)
Posted on: Monday, October 15, 2007 at 7:01 AM
Message: I can't edit the articles. The editor of the site can, but, being very busy posting new content all the time, he doesn't always get around to fixing the typos of the idiots, speaking specifically of myself only, that write for him.

Subject: my database of sql 2005 does not take.
Posted by: uday (view profile)
Posted on: Monday, October 29, 2007 at 7:14 AM
Message: my database is on server. i try all the queries for backing up the database from the server but it does not provide any error but it also does not create any backup file into the local drive what can i do now? please tell me.

Thanks in advance.

Subject: scheduled backups
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 06, 2007 at 7:58 AM
Message: hi..the article was exactly what i was looking for....was wondering if we can have scheduled backups with sqlserver 2005..and if so how?

Subject: Ridiculously Complex
Posted by: Anonymous (not signed in)
Posted on: Sunday, November 11, 2007 at 8:26 AM
Message: Sql Server is supposed to be replacing Access. This backup process is ridiculously complex in comparison. With access you could simply xcopy the file. Now you practically have to have a DBA for every small application that needs a database.

This article is much better than the crap on Microsoft Tech Net but I am still mystified as to how to create a full backup. You seem to show here that you simply set the db to full and run log file backups every 15 minutes. This is a full backup?

Subject: SQL Snapshot Replication
Posted by: Neil McGowan - AKL, NZ (not signed in)
Posted on: Wednesday, November 14, 2007 at 5:10 PM
Message: Hi there,

Ive inherited two SQL 2005 boxes to look after. Not having played much with SQL im learning as I go. The last tech here tried to set up snapshot replication between our primary and standby server with mixed success. The secondary server kept filling up its disk and subsequently would grind to a halt. I have managed to fix it by setting up proper backups and maintenance plans but now find the disk is being filled up even faster by hourly snapshot replications. Ive been hunting now for two days to try to find clear and concise instructions on how to either set up a maintenance task to clear the snapshots/data or whether I just bowl in and start deleting things without fear - taking into account again that this is a standby server that is not mission critical, we have solid backups of our primary box so deleting things isnt too much of a problem if necessary. Having seen some of the clever mechanisms for tidying itself up I would have assumed SQL would having something native to stop this happening. There is also the chance that the tech who set it up got something wrong and its not working correctly and I dont have enough knowledge yet to spot it. I managed to set up a test replication from the secondary server to my Dev box without the same issues. Any help would be greatly appreciated!

Subject: Cool stuff
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 20, 2007 at 4:42 AM
Message: It was really helpfull
Thanks :P

Subject: re: my database of sql 2005 does not take.
Posted by: Grant Fritchey (not signed in)
Posted on: Monday, December 10, 2007 at 9:32 AM
Message: It sounds like you're trying to backup across the network to your own local machine. SQL Server is probably running under the context of some system user that probably doesn't have access to your machine. I'd look at the SQL Server instance to see if you're writing stuff out to the C: drive there.

Subject: re: scheduled backups
Posted by: Granted (view profile)
Posted on: Monday, December 10, 2007 at 9:34 AM
Message: Sure. Just use the SQL Server Agent and run the appropriate scripts for your system. You can also use these scripts from any other scheduler that has the appropriate access to your SQL Server system.

Subject: re: Ridiculously Complex
Posted by: Granted (view profile)
Posted on: Monday, December 10, 2007 at 9:36 AM
Message: A full backup is the backup from the script:
BACKUP DATABASE x TO wherever

The other scripts are log backups.

Yeah, this is a lot more complex, but remember that unlike Access, you have lots of simultaneous connections and we can make a backup without interrupting their work in any way. I don't know if MS is going to ever really replace Access, but this is certainly what they see as the upgrade path.

Subject: re: SQL Snapshot Replication
Posted by: Granted (view profile)
Posted on: Monday, December 10, 2007 at 9:38 AM
Message: I don't know the purpose that you're hoping to achieve, but from the sounds of it, I'd use transactional replication or mirroring rather than snapshots all the time.

Subject: For those posting
Posted by: Granted (view profile)
Posted on: Monday, December 10, 2007 at 9:39 AM
Message: I check in every so often to see who has posted. There doesn't seem to be a mechanism for getting alerted to posts. I do try to respond so please be patient.

Subject: Nice Article about SQL server backups
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 18, 2007 at 12:56 AM
Message: I don't know anything about backups in SQL Server. After reading this article I cleared myself about backups. Hey man!!! you provide us such a nice article.. Thank you... For this article...

Subject: Export Data
Posted by: Arif (not signed in)
Posted on: Monday, December 24, 2007 at 7:32 AM
Message: How can i export data from sql server to text file

Subject: Erroe in Backup
Posted by: Arif (not signed in)
Posted on: Tuesday, December 25, 2007 at 3:31 AM
Message: BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\AdventureWorks.BAK'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Subject: re: Export Data
Posted by: Granted (view profile)
Posted on: Friday, December 28, 2007 at 3:59 PM
Message: This is different than backups. There are articles here at simple talk on bcp and sqlcmd. I'd check those out. They'll help.

Subject: re: Error in Backup
Posted by: Granted (view profile)
Posted on: Friday, December 28, 2007 at 4:01 PM
Message: Probably you ran this against a server where you don't have access to the C: drive. Just because you're running the command from your machine doesn't mean that it's your C: drive. Usually it's better to do something like this:

BACKUP DATABASE X
TO DISK = '\\machine\share\x.bak'

Subject: SQL Backup
Posted by: Mac (view profile)
Posted on: Wednesday, January 09, 2008 at 1:24 AM
Message: I have recently noticed that my sql continuously creates *.bak file and never overwrites them, I beleive that I understand what to do to get them to overwrite but was wondering if there was a way to change this for all of my databses in a one shot deal as my sql is running 30 different databases at a total of 73gb (not to big) but the weely backups are killing drive space like mad. I am failry new to sql database management, any input would be greatly appreciated.

Subject: RE: SQL Backup
Posted by: Granted (view profile)
Posted on: Thursday, February 07, 2008 at 10:04 AM
Message: If your backups are from a single script, you can change it there. But if you've got multiple backup routines for each database, you'll have to modify them individually. Sorry.

Subject: Backup Process
Posted by: Ash (not signed in)
Posted on: Tuesday, April 01, 2008 at 12:36 AM
Message: When a backup runs, does it take a snapshot of the database first, or does it back up in a rolling sort of fashion so it starts at point A and goes to point Z? What I'm trying to get at is, what happens if people are writing to the database during the backup process? Do their changes get backed up?

Subject: sql online backup
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 02, 2008 at 8:04 AM
Message: Can any body clearly specify how sql server online backup can be done

Subject: Re: Backup Process
Posted by: Granted (view profile)
Posted on: Tuesday, April 08, 2008 at 6:52 AM
Message: When the backup process starts, it puts a marker in the transaction log. Transactions continue while the backup occurs. When the back is at the end of it's process, it then captures all completed transactions since the start marker. Roughly, it does like you outline. Uncommitted transactions aren't backed up.

Subject: Re: sql online backup
Posted by: Granted (view profile)
Posted on: Tuesday, April 08, 2008 at 6:53 AM
Message: If I understand the quesion, the basic backup procedure described above is an online backup. At no time will the users be locked out of the system or prevented from committing transactions.

Subject: SQL server migration
Posted by: Mark Spurgen (not signed in)
Posted on: Wednesday, April 09, 2008 at 9:36 AM
Message: We have a process whereby a database is generated weekly on one server and then backup/restored to another server for use by a separate web application. we are looking to upgrade to SQL Server 2005 from 2000...but we want to phase the implementation. Is it feasible to upgrade the last (web) app database first and continue to generate the SQL 2000 database and back-up/restore it to SQL 2005 without making further changes?

Subject: SQL server Back up and recovery
Posted by: Milind More (not signed in)
Posted on: Wednesday, April 16, 2008 at 12:37 AM
Message: This article is help me lot for lerning sql server
2005 thank


Subject: Error Restore
Posted by: KnownBeam (not signed in)
Posted on: Friday, May 02, 2008 at 2:16 AM
Message: RESTORE DATABASE MRP
FROM DISK = 'mybackup02-05-2551.bak'

i found this error
"RESTORE cannot process database 'Mydatabase' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally.

Subject: Nice Article
Posted by: Sindhu (not signed in)
Posted on: Monday, May 12, 2008 at 1:16 AM
Message: This is of great help !!
Nice article !!

Subject: re: SQL Server Migration
Posted by: Granted (view profile)
Posted on: Wednesday, May 14, 2008 at 1:41 PM
Message: Sure. You can restore 2000 databases to a 2005 server. Make sure that they're fully 2005 compatible though. Otherwise it'll restore them with an 8.0 compatibility set and you don't want that.

Subject: re: Error Restore
Posted by: Granted (view profile)
Posted on: Wednesday, May 14, 2008 at 1:42 PM
Message: If you're connected to the database, you can't restore it at the same time. Change your connection so that you're connected to a different db. Master is a fine option.

Subject: re Nice Article & SQL Server backup and recovery
Posted by: Granted (view profile)
Posted on: Wednesday, May 14, 2008 at 1:43 PM
Message: I'm glad you found it useful. That was the intent.

Subject: Very nice
Posted by: Anonymous (not signed in)
Posted on: Friday, May 30, 2008 at 8:11 AM
Message: Very nice article ...go ahead!

Subject: Over writting backup filies
Posted by: Larry (not signed in)
Posted on: Tuesday, June 03, 2008 at 3:18 PM
Message: When doing a backup, you over write you backup files on a drive and their not much room on the drive. How would SQL Server handle it? Please email me back at green_hornet@live.com

Subject: Over writing backup files
Posted by: Granted (view profile)
Posted on: Thursday, June 05, 2008 at 8:12 AM
Message: If there's not enough room, you'll get an error and SQL Server will stop. The file won't be any good.

Subject: Restoring from one database to another
Posted by: Andy (view profile)
Posted on: Friday, June 13, 2008 at 1:20 PM
Message: First, thanks for the article - nice! I have a question.. I have Databases A and B. At one point in time, A & B had the same set of data. A now has new rows and B now has new rows. I need to get the new rows from A into B. I don't need new rows from B to go to A.

Is there a way to do this with differential backups? I suppose some scripting could be done, but there could be many tables with updates and I figured the backup method would be easiest... advice is greatly appreciated!

Subject: Restoring from one database to another
Posted by: Andy (view profile)
Posted on: Friday, June 13, 2008 at 1:25 PM
Message: First, thanks for the article - nice! I have a question.. I have Databases A and B. At one point in time, A & B had the same set of data. A now has new rows and B now has new rows. I need to get the new rows from A into B. I don't need new rows from B to go to A.

Is there a way to do this with differential backups? I suppose some scripting could be done, but there could be many tables with updates and I figured the backup method would be easiest... advice is greatly appreciated!

Subject: Restoring from one database to another
Posted by: Andy (view profile)
Posted on: Friday, June 13, 2008 at 1:27 PM
Message: First, thanks for the article - nice! I have a question.. I have Databases A and B. At one point in time, A & B had the same set of data. A now has new rows and B now has new rows. I need to get the new rows from A into B. I don't need new rows from B to go to A.

Is there a way to do this with differential backups? I suppose some scripting could be done, but there could be many tables with updates and I figured the backup method would be easiest... advice is greatly appreciated!

Subject: Restoring from one database to another
Posted by: Andy (view profile)
Posted on: Friday, June 13, 2008 at 1:28 PM
Message: First, thanks for the article - nice! I have a question.. I have Databases A and B. At one point in time, A & B had the same set of data. A now has new rows and B now has new rows. I need to get the new rows from A into B. I don't need new rows from B to go to A.

Is there a way to do this with differential backups? I suppose some scripting could be done, but there could be many tables with updates and I figured the backup method would be easiest... advice is greatly appreciated!

Subject: Restoring from one database to another
Posted by: Anonymous (not signed in)
Posted on: Monday, June 16, 2008 at 10:30 AM
Message: Hey,

I'm glad you found the article helpful.

If you only want to move A's data to B, you'd have to do a FULL backup & restore. I've never been able to move incrementals between different FULl backups. Maybe there's a way, but I haven't seen it.

The problem is, if you also want to retain B's new data, then you can't use backups to solve this issue at all. What you actually need is to set up replication. Then you can have all the new data from A moved into B.

The problem with both these solutions... how is B structured? Specifically, do the primary keys on database B allow for inserts from database A without blowing stuff up? Most systems are going to have issues with this unless you've designed for it up front.

Subject: Thanks
Posted by: faruk2011 (view profile)
Posted on: Thursday, July 24, 2008 at 5:15 AM
Message: Excellent Solution.

Subject: Nice Artical
Posted by: Yamini (not signed in)
Posted on: Tuesday, July 29, 2008 at 4:07 AM
Message: It's a very good artical. Thanks for claryfing my doubts through this artical.

Subject: I need to try
Posted by: Rajganesh (not signed in)
Posted on: Thursday, August 07, 2008 at 10:06 AM
Message: I hve not tried it . I will get back to you when its done.

Subject: great article!
Posted by: wakko (not signed in)
Posted on: Saturday, August 09, 2008 at 2:40 PM
Message: Hey, thanks for this great article! I have a sql a/a cluster question: What will happen, if my instance fails over during a backup?

Subject: re: Failover
Posted by: Granted (view profile)
Posted on: Friday, August 15, 2008 at 9:07 AM
Message: When the failover causes that connection to close that backup will be no good. You'll have to start another.

Subject: Clean up older than 23 hours
Posted by: akpoto (view profile)
Posted on: Tuesday, September 16, 2008 at 12:32 PM
Message: How do you backup and do a clean up task older than 23 hour?

Subject: Clean up...
Posted by: Granted (view profile)
Posted on: Thursday, September 18, 2008 at 11:19 AM
Message: I'm sorry, what do you mean clean up?

Database backups can be be done over weeks & months if that's all you need. If you mean log files, assuming the log is in full recovery, it will keep committed transactions stored in it for as long as you want, assuming you've got the disk space. When you do the backup of the log, if it's a week worth of data, it will clean out the committed transactions, the same as it will do if it's only 1/2 hour.

I'm not sure I'm answering the question.

Subject: Really, can I extract a single db backup from a multi-db backup file?
Posted by: RobKraft (view profile)
Posted on: Wednesday, December 03, 2008 at 12:45 PM
Message: In one of your responses above you state that "There is a way to extract individual backup files from a set. The problem is they're still backups, a different binary storage format". How can you do this? I don't see any documentation for this related to the RESTORE or BACKUP commands. I could use this feature occasionally. I know that I could restore a single file, then back it up separately; but I was hoping for something easier.

Subject: Extract backup
Posted by: Granted (view profile)
Posted on: Tuesday, December 16, 2008 at 2:16 PM
Message: What I meant was that you can restore from the media. I'm not aware of a way to actually extract the file. Sorry for the misleading statement.

Subject: BackupExec agent anyone ?
Posted by: Mike Elliott (view profile)
Posted on: Friday, November 06, 2009 at 4:46 PM
Message: I have always used SQL to backup to Network shares or the local file system for years. Then, BackupExec copies those to tape.

My Network Admin purchased the Backup Exec SQL Agent, and has started backing up some less critical servers. I am not comfortable with the product, and wonder if anyone here has any experience (positive or negative). Best practices all tell me to let SQL do the backups, and something else to go to tape...

Thoughts?

Subject: Error back up
Posted by: khalidjan9 (view profile)
Posted on: Tuesday, March 16, 2010 at 2:45 PM
Message: when i run this script

BACKUP DATABASE SDS
TO DISK = 'C:\Documents and Settings\khalz\Desktop\SDS.bak'
WITH COPY_ONLY

sql gives an error like this:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Documents and Settings\khalz\Desktop\SDS.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

i have read above resolution but e didnt help either!!!

Subject: Error back up
Posted by: khalidjan9 (view profile)
Posted on: Tuesday, March 16, 2010 at 2:46 PM
Message: when i run this script

BACKUP DATABASE SDS
TO DISK = 'C:\Documents and Settings\khalz\Desktop\SDS.bak'
WITH COPY_ONLY

sql gives an error like this:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Documents and Settings\khalz\Desktop\SDS.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

i have read above resolution but e didnt help either!!!

Subject: automatic sql backup
Posted by: alexey.k (view profile)
Posted on: Thursday, April 22, 2010 at 1:25 AM
Message: Guys, you also can use this tool to create backups: http://sqlbackupandftp.com/. I use a free version.

BTW, see this post http://www.sqlbackupandftp.com/forum/yaf_postst25_What-are-SqlBackupAndFTP-commandline-options.aspx to learn how you can do full recovery, log, copy-only and differential backups.

Subject: backup and restore MS SQL Server in running mode
Posted by: mp1963 (view profile)
Posted on: Saturday, June 19, 2010 at 12:04 PM
Message: you can use a tool like this: http://www.sql-server-backup.de/en/

This is a .Net Framework based solution to backup and restore MS SQL Server in running mode.
It's free

MS SQL Server backups with one click or via MS Windows based schedule jobs.
DBSave is very easy to set up.
Backups Local, to Network or via FTP.
Remote Backup via VDI.
Supports up to 4 SQL servers instances on different servers.
Compression of the backups regardless of the SQL Server version.
Encryption of the backups via AES regardless of the SQL Server version.
DBSave supports german and english language.

Subject: Backup 300GB using Sql Native backup.
Posted by: akmenaria (view profile)
Posted on: Monday, October 04, 2010 at 2:26 AM
Message: Hello Guys, I have question, could you plz help me on it. I have a Production Database Server which is 300GB.It takes approximately 7 hours to backup the data.Now we have another Test server whereby we have a job which copies the backup from the production database into one of its drive, restores the data and verifies it.Previously step to copy the database into the test server took only 7 hours but now since last week it is taking around 20 hours.There is no network issues found.No blocking.Is there a way to take split/partial backup and copy into Test one by one..Maybe it could help in copying faster.

Subject: To Mike Elliott
Posted by: Grant Fritchey (view profile)
Posted on: Sunday, February 13, 2011 at 7:46 AM
Message: sorry that I'm a year late on the response. This site doesn't let me know when there are posts here.

Yes, I've used BackupExec. It works ok. All it's doing is running SQL backup behind the scenes. I don't like backing up to a tape device directly because it just takes longer, which means more chances for things to go wrong.

Subject: To khalidjan9
Posted by: Grant Fritchey (view profile)
Posted on: Sunday, February 13, 2011 at 7:48 AM
Message: That's a permissions error. It sounds like you're running the backup on a production system but trying to backup to your local drive. You can only do that if you make your local drive a share and then use the UNC path to backup to it:

\\mymachine\mysharename\backup.bak

Subject: To akmenaria
Posted by: Grant Fritchey (view profile)
Posted on: Sunday, February 13, 2011 at 7:50 AM
Message: 7 hours for a 300gb backup is kind of long. Are you backing up across the network?

In your situation, you should look into two things, backup compression, which will make your backups faster and smaller, and a way to do a virtual restore. Both these are offered by Red Gate software in the SQL Hyperbac suite of tools. Please check them out.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.