Frustrating Backup/Restore Issue

Im running SQL Server 2005

I set up 4 maintenance plans:

1) Full production DBS backup every day at midnight

2) Transaction Backups every 10 minutes starting at 12:10 AM ending at 11:59 PM

3) Full System DBS backups

4) Maintenance on production database: index rebuild, stats, etc.

All the files are sucessfully there but when I try to restore the backup to a new database to make sure its valid it seems I can restore the full backup but the transaction logs seem to be completely disassociated with the full backup. Says there not from the same backup set

I tried the same thing earlier just taking a full backup and then transaction logs in two separate plans and it worked fine. Sometimes it seems to sync up and sometimes it does not. Any help on this issue would be much appreciated

Thanks

Scott

What you're attempting should work just fine, so we need to figure out exactly where it's going wrong.

Information that will help us figure that out:

The exact error text, cut and pasted if possible. That allows us to track down what code is generating the error.|||

Okay first and foremost here is the error message I can when I try to restore the full backup with the first transaction log after the full backup. (Im having trouble copying and pasting) its says:

The volume on Device 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\radb\radb_backup_200609281200.trn' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)

- I am using separate files and separate database maintenance plans (since they need to be schedule at different times) for the full backup and transactional backups

- I am using separate transaction files for each log and not appending

- I am backing up to disk and not directly to tape

- I am using Management Studio and maintenance plans for these operations

Thanks

Scott

|||

It sounds from your description and the error message as if you might be attempting to restore the full backup and the log backup in one command. If that's the case, this is your problem.

You need to restore the full backup first, WITH NORECOVERY, and then restore transaction log backups one by one in order, specifying WITH NORECOVERY until you've got them all applied. At that point you can restore with recovery, and you're done.

|||

Okay I understand what your saying but ...

I have gotten it to work where I can restore and check the full backup with the transaction logs. Its worked twice like that and hasnt worked twice so its the consistency that bothers me. In the meantime I will try to restore the full backup first and then apply the logs.

sql

Frustrating Backup/Restore Issue

Im running SQL Server 2005

I set up 4 maintenance plans:

1) Full production DBS backup every day at midnight

2) Transaction Backups every 10 minutes starting at 12:10 AM ending at 11:59 PM

3) Full System DBS backups

4) Maintenance on production database: index rebuild, stats, etc.

All the files are sucessfully there but when I try to restore the backup to a new database to make sure its valid it seems I can restore the full backup but the transaction logs seem to be completely disassociated with the full backup. Says there not from the same backup set

I tried the same thing earlier just taking a full backup and then transaction logs in two separate plans and it worked fine. Sometimes it seems to sync up and sometimes it does not. Any help on this issue would be much appreciated

Thanks

Scott

What you're attempting should work just fine, so we need to figure out exactly where it's going wrong.

Information that will help us figure that out:

The exact error text, cut and pasted if possible. That allows us to track down what code is generating the error.|||

Okay first and foremost here is the error message I can when I try to restore the full backup with the first transaction log after the full backup. (Im having trouble copying and pasting) its says:

The volume on Device 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\radb\radb_backup_200609281200.trn' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)

- I am using separate files and separate database maintenance plans (since they need to be schedule at different times) for the full backup and transactional backups

- I am using separate transaction files for each log and not appending

- I am backing up to disk and not directly to tape

- I am using Management Studio and maintenance plans for these operations

Thanks

Scott

|||

It sounds from your description and the error message as if you might be attempting to restore the full backup and the log backup in one command. If that's the case, this is your problem.

You need to restore the full backup first, WITH NORECOVERY, and then restore transaction log backups one by one in order, specifying WITH NORECOVERY until you've got them all applied. At that point you can restore with recovery, and you're done.

|||

Okay I understand what your saying but ...

I have gotten it to work where I can restore and check the full backup with the transaction logs. Its worked twice like that and hasnt worked twice so its the consistency that bothers me. In the meantime I will try to restore the full backup first and then apply the logs.

From 6.5 databases to 7.0

Hello,

Is it possible to restore a 6.5 database to a 7.0? I backup the entire 6.5 database to a BAK file then tried to restore it to SQL 7.0 but it didn't work. SQL 7.0 states that the disk device is not a valid Microsoft Tape Format backup set.

We have an application that was developped long long time ago by a old team and I don't want to upgrade the server. I tried to build a second 6.5 server (new hardware) but I couldn't restore the database as SQL 6.5 states that the server is in single user mode. I don't know how to switch to multi users mode. The database I want to restore is not in DBO or single user mode.

Any help will be appreciated.
Thanks.

Toan.You will have to restore the database to 6.5 and then run the upgrade wizard to move the database to 7.0.

I found that the user logins, and roles didn't transfer. I did this a while back so I'm a little fuzzy on the particulars.

Freezing Database or Server

In Oracle there a way to 'freeze' a tablespace when doing a backup. Does SQL
Server have a way to 'freeze' the tables or the database?Jorge,
I don't quite understand what you are trying to do. If you can
ALTER database SET Read_Only
before the backup, then you will ensure that no changes are being made
during the backup. (But a backup picks up the transaction log changes made
during the backup as well, so your data would be complete as of the
completion of the backup.) I suspect that this is not what you are after.
Russell Fields
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:1C6E1130-BCBB-4E0D-A7EC-86DFA941B9B7@.microsoft.com...
> In Oracle there a way to 'freeze' a tablespace when doing a backup. Does
SQL Server have a way to 'freeze' the tables or the database?

Freezing Database or Server

In Oracle there a way to 'freeze' a tablespace when doing a backup. Does SQL Server have a way to 'freeze' the tables or the database?Jorge,
I don't quite understand what you are trying to do. If you can
ALTER database SET Read_Only
before the backup, then you will ensure that no changes are being made
during the backup. (But a backup picks up the transaction log changes made
during the backup as well, so your data would be complete as of the
completion of the backup.) I suspect that this is not what you are after.
Russell Fields
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:1C6E1130-BCBB-4E0D-A7EC-86DFA941B9B7@.microsoft.com...
> In Oracle there a way to 'freeze' a tablespace when doing a backup. Does
SQL Server have a way to 'freeze' the tables or the database?

Freeze replication

I would like to do a backup of a publishing database and restore as opposed
to syncing from scratch that might take a long time for a huge database. In
order to do so, I would like to freeze changes made at the publisher
somehow... backup the database after its frozen, restore the database
establish replication and then unfreeze.
Ive read some google posting a year ago on some ways of achieving that
freeze by running selects with tablockx hints.. Im not 100% certain but
either would like to know more about how to freeze it.. I am willing to
accept blocked transactions and timeouts at the time...
This is more from a DR restore perspective.Using SQL 2000
put your database in read only mode, do your backup, and then restore it on
the subscriber.
This way you are guaranteed to have a consistent publisher and subscriber.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uyoTWyelEHA.3428@.TK2MSFTNGP14.phx.gbl...
> I would like to do a backup of a publishing database and restore as
opposed
> to syncing from scratch that might take a long time for a huge database.
In
> order to do so, I would like to freeze changes made at the publisher
> somehow... backup the database after its frozen, restore the database
> establish replication and then unfreeze.
> Ive read some google posting a year ago on some ways of achieving that
> freeze by running selects with tablockx hints.. Im not 100% certain but
> either would like to know more about how to freeze it.. I am willing to
> accept blocked transactions and timeouts at the time...
> This is more from a DR restore perspective.Using SQL 2000
>

Fragmented databases and database backup

Hi,

If a database file size grows and shrinks it becomes fragmented. When creating a backup of a database and to restore it on another SQL Server, does it increase the fragmentation on that server?

How can I keep database files defragmented? Does SQL Server have tools for that or should Windows defragmentation tools be used?

/M

I think you are confusing data file fragmentation with disk fragmentation. They are two separate issues although they both affect performance of SQL Server.

If you backup your database and restore to a different server, it will not have any disk fragmentation if there is enough contiguous space to hold all of the data. Otherwise, it is at the mercy of the new disk drive.

For file fragmentation, creating a backup does not relocate data within the files. So a restored backup on a new server will have the exact same file fragmentation as the original version on the old server.

|||

Thank you for your answer, I was not aware of the two different framentation problems. Now I am curious on fragmentation solutions. Our SQL Server database is set to grow 10 % when needed and not to auto shrink.

I guess disk fragmentation can be solved using the standard Disk Defragmenter in Windows.

How can file fragmentation be solved within the database files?

/M