back everything up in a single file?

[Bing Du]

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing

[Simon Hayes]

"Bing Du" <bd*@iastate.edu> wrote in message
news:c1**********@news.iastate.edu...

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing

In the code above, DBBackup is not a file, it's a backup device, so in
theory it's possible that your DBA used to point the device at different
physical files to provide some sort of rotation, although from your
description that sounds unlikely. In fact, personally, I can't see how it
would be possible to manage backups effectively using her approach.

In any case, if you need a quick solution, I would suggest creating one or
more database maintenance plans, and back up all your databases to a
convenient disk location. After that, use your standard backup software or
methods to copy the backup files to tape and/or another physical server. The
maintenance plan wizard (Enterprise Manager, Tools menu) should be easy
enough to use that you can set this up fairly quickly. It will back up
directly to files, and can also remove old backups after a period you
specify.

I'm not saying that that is the best or only approach, and some
functionality (eg differential backups) isn't available from the wizard, but
as a way of quickly putting something manageable in place, it should be
fine.

After that, find a new DBA and/or start reading the Books Online information
on "Recovery Models" and "Backup and Restore Operations".

Simon

[comp.databases.ms-sqlserver]

Thanks much for your response, Simon. I'll check out the online
information you mentioned. I understand DBBackup is not a file. It's a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:

==========
Name Physical Location Device Type

DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========

So, I don't think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I'm wrong.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"comp.databases.ms-sqlserver" <an*******@devdex.com> wrote in message
news:40*********************@news.frii.net...

Thanks much for your response, Simon. I'll check out the online
information you mentioned. I understand DBBackup is not a file. It's a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:

==========
Name Physical Location Device Type

DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========

So, I don't think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I'm wrong.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

A single file or device can contain multiple backups, and you can restore
any individual backup from it, as long as it makes sense in the context of
what you're restoring (you can't restore database A logs to database B, for
example, or restore logs out of order). It doesn't matter if you mix backup
types and databases in one file, except from a management perspective.

You can select the individual backup to restore in the Enterprise Manage
restore dialogue using the "First backup to restore" drop down. This may be
a clearer way to demonstrate what you can do (on a test server only, of
course), assuming you have sysadmin permissions:

/* Create a test DB */
create database foo
go

/* Back up the DB twice, and the log once, to the same file,
** with an msdb backup in the middle to show that we can
** mix backups.
*/
backup database foo to disk = 'c:\foo.bak'
go
backup database foo to disk = 'c:\foo.bak'
go
backup database msdb to disk = 'c:\foo.bak'
go
backup log foo to disk = 'c:\foo.bak'
go

/" View the backups available in the file */
restore headeronly from disk = 'c:\foo.bak'
go

/* Restore the 1st backup and make the DB available */
restore database foo from disk = 'c:\foo.bak' with file = 1, recovery
go
/* Restore the 2nd backup, but do not recover (so we can apply the log) */
restore database foo from disk = 'c:\foo.bak' with file = 2, norecovery
go
/* Restore the log and make the DB available */
restore log foo from disk = 'c:\foo.bak' with file = 4, recovery
go

/* Clean up */
drop database foo
go
exec master..xp_cmdshell 'del c:\foo.bak', no_output
go

Simon

[comp.databases.ms-sqlserver]

Great. Thanks a lot again for the lucid explanations with examples!
Very helpful. Seems one file is not a big problem. I need to read
more to work out a best solution for our situation.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bing Du]

I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
if the backup made on s1 can be restored on s2. Briefly, what hit me
was the backup file made on s1 could not be viewed on s2.

On server s1, back up (both full and differential) all the databases and
logs into a backup device which points to the disk file
e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
DBBackup.BAK on s1.

Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
DBBackup.BAK as a file in My Documents fine. But in the Enterprise
Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
error 'This device does not contain any backup sets'.

How so?

Bing

Bing Du wrote:

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing

[Bing Du]

Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

Bing Du wrote:

I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
if the backup made on s1 can be restored on s2. Briefly, what hit me
was the backup file made on s1 could not be viewed on s2.

On server s1, back up (both full and differential) all the databases and
logs into a backup device which points to the disk file
e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
DBBackup.BAK on s1.

Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
DBBackup.BAK as a file in My Documents fine. But in the Enterprise
Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
error 'This device does not contain any backup sets'.

How so?

Bing

Bing Du wrote:

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is
currently set up. When I did 'view contents' of DBBackup, I saw it
contained the mixture of log, full and diff backups of verious dates.
She has quit. Other coworkers said (in a not-sure tone) she was able
to restore the databases from such a single file, although nobody ever
saw or knew how she did recovery. My knowledge about SQL Server,
especially regarding its backup/restore is limited. But I've ever
worked with other databases, e.g. Oracle, MySQL and Postgres. I think
log backups, full backups and differential backups should be
completely separated. Also, in each type of backups, each database
should have its own backup file. Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing

[Simon Hayes]

"Bing Du" <bd*@iastate.edu> wrote in message
news:c1**********@news.iastate.edu...

Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

<snip>

FYI, it is possible to backup and restore from UNC paths, provided that the
account used to run MSSQL has access to the path:

backup database foo to disk = '\\MyServer\MyBackups\foo.bak'

Enteprise Manager only displays local drives, but I believe you can type in
a UNC path in the backup/restore dialogues, although I don't use EM much, so
I'm not 100% sure about that.

Simon

[Bing Du]

I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

Bing

Simon Hayes wrote:

"Bing Du" <bd*@iastate.edu> wrote in message
news:c1**********@news.iastate.edu...

Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

<snip>

FYI, it is possible to backup and restore from UNC paths, provided that the
account used to run MSSQL has access to the path:

backup database foo to disk = '\\MyServer\MyBackups\foo.bak'

Enteprise Manager only displays local drives, but I believe you can type in
a UNC path in the backup/restore dialogues, although I don't use EM much, so
I'm not 100% sure about that.

Simon

[Erland Sommarskog]

Bing Du (bd*@iastate.edu) writes:

I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

And what does "did not work" mean?

As Simon says, it depends on the account under which SQL Server is running.
If SQL Server is installed to run as Local Service, then you cannot access
network resources from SQL Server.

A word of caution, though, about backing up or restore from network
devices. I don't think is fully supported. That is, you can do it, but
you will be safe if you back up to local disk, and copy of the network.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

Bing Du (bd*@iastate.edu) writes:

I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.
And what does "did not work" mean?

As Simon says, it depends on the account under which SQL Server is

running. If SQL Server is installed to run as Local Service, then you cannot access
network resources from SQL Server.

A word of caution, though, about backing up or restore from network
devices. I don't think is fully supported. That is, you can do it, but
you will be safe if you back up to local disk, and copy of the network.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

According to Books Online (under BACKUP):

"If using a network server with a Uniform Naming Convention (UNC) name or
using a redirected drive letter, specify a device type of disk."

But anyway, a backup to a UNC path may fail because of network issues, so
it's fair to say that a local backup is a safer option.

Simon