473,385 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

back everything up in a single file?

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

Jul 20 '05 #1
10 3276

"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
Jul 20 '05 #2
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!
Jul 20 '05 #3

"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
Jul 20 '05 #4
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!
Jul 20 '05 #5
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


Jul 20 '05 #6
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


Jul 20 '05 #7

"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
Jul 20 '05 #8
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


Jul 20 '05 #9
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
Jul 20 '05 #10

"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
Jul 20 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Belinda | last post by:
Hello All I need to read a SQL Server table into a Web Page and within the Web Page to permit my users to make changes to the records, delete or add new records and then save the entire contents...
24
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> ...
33
by: DFS | last post by:
An application I wrote has been deployed on Citrix, and the Citrix admin tells me all users run the same .mde file. There aren't a lot of concurrent users, but even 2 could be cause for concern. ...
5
by: Tom wilson | last post by:
I'm developing an application that is a benefits enrolment website. The benefits can be of any type in any order as stored in a SQL server. On each page is a Back and Next button. At a certain...
5
by: Jim Hubbard | last post by:
There is another alternative which looks promising - REALbasic. I have just downloaded the demo - so don't lynch me if I'm wrong - but REALbasic looks like a very strong contender to take over...
26
by: gswork | last post by:
i hadn't designed a web page from the ground up for about 9 years, then i was asked to do one. I'd dabbled with html and vaigly kept up with some of the developments but other than that i've been...
5
by: Rico | last post by:
Hello, I'm trying to create a simple back up in the SQL Maintenance Plan that will make a single back up copy of all database every night at 10 pm. I'd like the previous nights file to be...
3
by: TonyJ | last post by:
Hello! I supposed to modify some text files. In the file(s) which consist of different section. In one section you might have Path = <Installation_path>. The program will then replace the text...
176
by: . | last post by:
9/11 Mysteries http://video.google.com/videoplay?docid=-8172271955308136871 http://www.911weknow.com Ignore those who would go to great effort and expend much of heir time in poo-pooing this...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.