backup strategy for a 1000GB database

Hi,

Could anyone tell me the backup strategy for a 1000GB database?

Thank you!

Peter Wang

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!The size of the database is one consideration, but probably more important
is how much data loss and downtime you can afford in the event of a problem.
You really need to get this information first, so you can decide how often
to do full/differential/log backups. This will also help you to decide how
much money you can reasonably allocate to your backup/availability solution.
There is a lot of information in BOL in the section called "Desiging a
Backup and Restore Strategy".

If you have the database on a NAS or SAN already, you may be able to use
features of the hardware (some storage devices support MSSQL snapshot
backup/restore). On the other hand, backing up to multiple disk devices
might be a good choice, depending on your requirements and storage
configuration.

Finally, don't forget that as a rule, backing up is easier than restoring.
So also consider how easily your solution will allow you to restore a
database if you need to. It's hard to be specific, as a lot depends on your
environment and your application/user requirements.

Simon

"peter wang" <anonymous@.devdex.com> wrote in message
news:3ef88c70$0$201$75868355@.news.frii.net...
> Hi,
> Could anyone tell me the backup strategy for a 1000GB database?
> Thank you!
> Peter Wang
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Backup Query

Hello Faculties,
Is it necessary to set a database to RESTRICTED USER before taking
backups?

*** Sent via Developersdex http://www.developersdex.com ***No.

Backups are done online and normal operations can continue against it. You
will only notice it is running when it slows your server down slightly due
to the additional Disk IO.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:v2Dre.10$GU5.4871@.news.uswest.net...
> Hello Faculties,
> Is it necessary to set a database to RESTRICTED USER before taking
> backups?
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Naturally, that is when you use Enterprise Manager or use the built in T-SQL
backup command.

Never backup the LDF, NDF and MDF files directly using a backup software.

--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:42af0082_3@.news.bluewin.ch...
> No.
> Backups are done online and normal operations can continue against it. You
> will only notice it is running when it slows your server down slightly due
> to the additional Disk IO.
> Regards
> ----------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "debian mojo" <debian_mojo@.yahoo.com> wrote in message
> news:v2Dre.10$GU5.4871@.news.uswest.net...
>> Hello Faculties,
>> Is it necessary to set a database to RESTRICTED USER before taking
>> backups?
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***|||Assuming that you're using the TSQL BACKUP command, then no - in MSSQL,
all types of backup can be made with the database in use. See the
Remarks section under BACKUP in Books Online.

Simon

backup over tcp/ip

Hello,
Is it possible to place the backup on another location via tcp ip?
Thnx
The BOL states you can use a UNC path and I have done this before, you
should be able to specify an IP address and share folder too. I am not in
front of a sql server to test though. You can always use a mapped drive
letter too.
hth
Eric
Samuel wrote:
> Hello,
> Is it possible to place the backup on another location via tcp ip?
> Thnx
|||And SQL will need permissions to the destination resource too. Best if SQL
Server runs under a domain account.
The maintenance plan UI does not support UNC paths, but T-SQL does (BACKUP
database...).
Cheers
Mike
"Eric Sabine" wrote:

> The BOL states you can use a UNC path and I have done this before, you
> should be able to specify an IP address and share folder too. I am not in
> front of a sql server to test though. You can always use a mapped drive
> letter too.
> hth
> Eric
>
> Samuel wrote:
>
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:270FDC17-5046-48CF-8E87-BBE46C72F57A@.microsoft.com...
> And SQL will need permissions to the destination resource too. Best if SQL
> Server runs under a domain account.
> The maintenance plan UI does not support UNC paths, but T-SQL does (BACKUP
> database...).
Yes it does. I've set up several backups this way.
However, you ahve to manually type them in, you can't click on the gui to
get it to fill in the box.
[vbcol=seagreen]
> Cheers
> Mike
> "Eric Sabine" wrote:
in[vbcol=seagreen]
|||try something like this:
backup database DB1 to disk =
'\\remoteserver\f$\backups\DB1_db_200409171500.bck '
remoteserver: the remote server you want to back up to.
f$ : the drive on said remote server.
backups: directory or folder on said drive on said remote server.
DB1_db_200409171500.bck: backup file name with date and time.
"Samuel" <samuel@.hotrmail.com> wrote in message
news:Oo34fH$mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> Hello,
> Is it possible to place the backup on another location via tcp ip?
> Thnx
>
|||Hi Greg,
Do you have a example which i could use?
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:RAh2d.42$yg.19@.twister.nyroc.rr.com...[vbcol=seagreen]
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:270FDC17-5046-48CF-8E87-BBE46C72F57A@.microsoft.com...
SQL[vbcol=seagreen]
(BACKUP[vbcol=seagreen]
> Yes it does. I've set up several backups this way.
> However, you ahve to manually type them in, you can't click on the gui to
> get it to fill in the box.
>
not[vbcol=seagreen]
> in
drive
>
|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23Y27XVLnEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Hi Greg,
> Do you have a example which i could use?
Not sure what you mean by an example. It's tough to show what the graphical
interface will look like in a text forum.
However, if you're using the GUI to setup a maintenance plan to do backups,
when it asks you where to save them to, rather than using the button to pull
up the directory "tree" and drilling down from there, simply type in the UNC
you want to use.
Note, the account SQL Agent runs under MUST have rights to that UNC.

> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:RAh2d.42$yg.19@.twister.nyroc.rr.com...
> SQL
> (BACKUP
to[vbcol=seagreen]
you
> not
> drive
>
|||The other name for this is replication.
Eric Sabine wrote:

> The BOL states you can use a UNC path and I have done this before, you
> should be able to specify an IP address and share folder too. I am not in
> front of a sql server to test though. You can always use a mapped drive
> letter too.
> hth
> Eric
>
> Samuel wrote:
>
>
|||"Adrian Edwards" <a.n.other@.hotmail.com> wrote in message
news:cier3e$edm$3@.sparta.btinternet.com...
> The other name for this is replication.
No, replication is something completely different.
[vbcol=seagreen]
> Eric Sabine wrote:
in[vbcol=seagreen]
|||I was being facetious.
Greg D. Moore (Strider) wrote:

> "Adrian Edwards" <a.n.other@.hotmail.com> wrote in message
> news:cier3e$edm$3@.sparta.btinternet.com...
>
> No, replication is something completely different.
>
>
> in
>
>

backup over tcp/ip

Hello,
Is it possible to place the backup on another location via tcp ip?
ThnxThe BOL states you can use a UNC path and I have done this before, you
should be able to specify an IP address and share folder too. I am not in
front of a sql server to test though. You can always use a mapped drive
letter too.
hth
Eric
Samuel wrote:
> Hello,
> Is it possible to place the backup on another location via tcp ip?
> Thnx|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:270FDC17-5046-48CF-8E87-BBE46C72F57A@.microsoft.com...
> And SQL will need permissions to the destination resource too. Best if SQL
> Server runs under a domain account.
> The maintenance plan UI does not support UNC paths, but T-SQL does (BACKUP
> database...).
Yes it does. I've set up several backups this way.
However, you ahve to manually type them in, you can't click on the gui to
get it to fill in the box.
> Cheers
> Mike
> "Eric Sabine" wrote:
> > The BOL states you can use a UNC path and I have done this before, you
> > should be able to specify an IP address and share folder too. I am not
in
> > front of a sql server to test though. You can always use a mapped drive
> > letter too.
> >
> > hth
> > Eric
> >
> >
> > Samuel wrote:
> > > Hello,
> > >
> > > Is it possible to place the backup on another location via tcp ip?
> > >
> > > Thnx
> >
> >
> >|||try something like this:
backup database DB1 to disk ='\\remoteserver\f$\backups\DB1_db_200409171500.bck'
remoteserver: the remote server you want to back up to.
f$ : the drive on said remote server.
backups: directory or folder on said drive on said remote server.
DB1_db_200409171500.bck: backup file name with date and time.
"Samuel" <samuel@.hotrmail.com> wrote in message
news:Oo34fH$mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> Hello,
> Is it possible to place the backup on another location via tcp ip?
> Thnx
>|||Hi Greg,
Do you have a example which i could use?
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:RAh2d.42$yg.19@.twister.nyroc.rr.com...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:270FDC17-5046-48CF-8E87-BBE46C72F57A@.microsoft.com...
> > And SQL will need permissions to the destination resource too. Best if
SQL
> > Server runs under a domain account.
> >
> > The maintenance plan UI does not support UNC paths, but T-SQL does
(BACKUP
> > database...).
> Yes it does. I've set up several backups this way.
> However, you ahve to manually type them in, you can't click on the gui to
> get it to fill in the box.
>
> >
> > Cheers
> > Mike
> >
> > "Eric Sabine" wrote:
> >
> > > The BOL states you can use a UNC path and I have done this before, you
> > > should be able to specify an IP address and share folder too. I am
not
> in
> > > front of a sql server to test though. You can always use a mapped
drive
> > > letter too.
> > >
> > > hth
> > > Eric
> > >
> > >
> > > Samuel wrote:
> > > > Hello,
> > > >
> > > > Is it possible to place the backup on another location via tcp ip?
> > > >
> > > > Thnx
> > >
> > >
> > >
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23Y27XVLnEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Hi Greg,
> Do you have a example which i could use?
Not sure what you mean by an example. It's tough to show what the graphical
interface will look like in a text forum.
However, if you're using the GUI to setup a maintenance plan to do backups,
when it asks you where to save them to, rather than using the button to pull
up the directory "tree" and drilling down from there, simply type in the UNC
you want to use.
Note, the account SQL Agent runs under MUST have rights to that UNC.
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:RAh2d.42$yg.19@.twister.nyroc.rr.com...
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:270FDC17-5046-48CF-8E87-BBE46C72F57A@.microsoft.com...
> > > And SQL will need permissions to the destination resource too. Best if
> SQL
> > > Server runs under a domain account.
> > >
> > > The maintenance plan UI does not support UNC paths, but T-SQL does
> (BACKUP
> > > database...).
> >
> > Yes it does. I've set up several backups this way.
> >
> > However, you ahve to manually type them in, you can't click on the gui
to
> > get it to fill in the box.
> >
> >
> > >
> > > Cheers
> > > Mike
> > >
> > > "Eric Sabine" wrote:
> > >
> > > > The BOL states you can use a UNC path and I have done this before,
you
> > > > should be able to specify an IP address and share folder too. I am
> not
> > in
> > > > front of a sql server to test though. You can always use a mapped
> drive
> > > > letter too.
> > > >
> > > > hth
> > > > Eric
> > > >
> > > >
> > > > Samuel wrote:
> > > > > Hello,
> > > > >
> > > > > Is it possible to place the backup on another location via tcp ip?
> > > > >
> > > > > Thnx
> > > >
> > > >
> > > >
> >
> >
>|||The other name for this is replication.
Eric Sabine wrote:
> The BOL states you can use a UNC path and I have done this before, you
> should be able to specify an IP address and share folder too. I am not in
> front of a sql server to test though. You can always use a mapped drive
> letter too.
> hth
> Eric
>
> Samuel wrote:
>>Hello,
>>Is it possible to place the backup on another location via tcp ip?
>>Thnx
>
>|||"Adrian Edwards" <a.n.other@.hotmail.com> wrote in message
news:cier3e$edm$3@.sparta.btinternet.com...
> The other name for this is replication.
No, replication is something completely different.
> Eric Sabine wrote:
> > The BOL states you can use a UNC path and I have done this before, you
> > should be able to specify an IP address and share folder too. I am not
in
> > front of a sql server to test though. You can always use a mapped drive
> > letter too.
> >
> > hth
> > Eric
> >
> >
> > Samuel wrote:
> >
> >>Hello,
> >>
> >>Is it possible to place the backup on another location via tcp ip?
> >>
> >>Thnx
> >
> >
> >|||I was being facetious.
Greg D. Moore (Strider) wrote:
> "Adrian Edwards" <a.n.other@.hotmail.com> wrote in message
> news:cier3e$edm$3@.sparta.btinternet.com...
>>The other name for this is replication.
>
> No, replication is something completely different.
>
>>Eric Sabine wrote:
>>
>>The BOL states you can use a UNC path and I have done this before, you
>>should be able to specify an IP address and share folder too. I am not
> in
>>front of a sql server to test though. You can always use a mapped drive
>>letter too.
>>hth
>>Eric
>>
>>Samuel wrote:
>>
>>Hello,
>>Is it possible to place the backup on another location via tcp ip?
>>Thnx
>>
>|||And SQL will need permissions to the destination resource too. Best if SQL
Server runs under a domain account.
The maintenance plan UI does not support UNC paths, but T-SQL does (BACKUP
database...).
Cheers
Mike
"Eric Sabine" wrote:
> The BOL states you can use a UNC path and I have done this before, you
> should be able to specify an IP address and share folder too. I am not in
> front of a sql server to test though. You can always use a mapped drive
> letter too.
> hth
> Eric
>
> Samuel wrote:
> > Hello,
> >
> > Is it possible to place the backup on another location via tcp ip?
> >
> > Thnx
>
>

Backup options with MSDE

Does MSDE support the full, complete set of backup options that SQL Server
does? If not, what options does MSDE support and are they available via the
Enterprise Manager interface?
Thanks in advance.
hi,
collectivedc wrote:
> Does MSDE support the full, complete set of backup options that SQL
> Server does? If not, what options does MSDE support and are they
> available via the Enterprise Manager interface?
> Thanks in advance.
MSDE supports all the kind of backup expesed by Enterprise Manager...
only a caveat... usually, you are not allowed to use EM with MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.

|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.