sql backup files: sql2000

Showing posts with label sql2000. Show all posts

Tuesday, March 27, 2012

Backup SQL2000 on EMC

Hi all,
one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
They're using EMC SnapView for filesystem backup together with Veritas
NetBackup.
They won't use the SQL Integration Modules for SnapView so there is the
question of backing up the databases. Is there any other way to backup the
databases while using the SnapView functionality - and keeping SQL2000
services running?
Any help would be appreciated...
Wolfgang
"wso" <wso@.discussions.microsoft.com> wrote in message
news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
> Hi all,
> one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
> They're using EMC SnapView for filesystem backup together with Veritas
> NetBackup.
> They won't use the SQL Integration Modules for SnapView so there is the
> question of backing up the databases. Is there any other way to backup the
> databases while using the SnapView functionality - and keeping SQL2000
> services running?
> Any help would be appreciated...
> Wolfgang
The data and log files are kept open and active while SQL Server is running.
I am assuming that Snap View won't touch them in this state.
The quick and easy solution is to schedule a regular SQL Server job to
perform SQL based backups to disk. Once the backup is complete, the backup
files are closed and Snap View can collect them (or Veritas for that
matter).
It's sort of doing double duty, but it does give you some flexibility.
Rick Sawtell
MCT, MCSD, MCDBA
|||Your only problem is that you might get torn pages and/or log file
fragments, especially if you are storing data and logs on different LUNS
mapped to different physical disks. I suggest testing the backups heavily,
especially where you take backups under heavy load. I suspect that there
will be a fairly high failure rate. They may have this feature now, but
when I was looking, they couldn't synchronize multiple LUNs for a snapshot,
thus rendering the backups unreliable.
I don't even bother with Snapview. I do SQL-level backups to a separate
network file share (non-SAN based) and then spin those to tape to get an
archive history. The extra storage is far cheaper than the Snapview license
for a large SAN.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"wso" <wso@.discussions.microsoft.com> wrote in message
news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
> Hi all,
> one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
> They're using EMC SnapView for filesystem backup together with Veritas
> NetBackup.
> They won't use the SQL Integration Modules for SnapView so there is the
> question of backing up the databases. Is there any other way to backup the
> databases while using the SnapView functionality - and keeping SQL2000
> services running?
> Any help would be appreciated...
> Wolfgang
|||Hello Geoff,
I was wondering if you can help me with the SAN/SQL Cluster backup question?
We have 3-Node (2 Virtual Instances with Standby) Cluster and one dedicated
LUN (Raid 5) for online backup.
Question1:
I think we need to expose the BACKUP LUN to each HOSTS? so SQL server backup
job can backup database on that LUN
Question2:
Once we expose that LUN to SQL CLuster Nodes the HOST will have full control
and how the backup wil work on second virtual host?
May be you can tell me what is the BEST practices of SAN/SQL Server backup
solutions. Just so you know we are trying to AVOID using the LAN (performance
etc.) and thereofore will also have dedicated backup server for TAPE backup...
Thanks.
Kamal Hassan
"Geoff N. Hiten" wrote:

> Your only problem is that you might get torn pages and/or log file
> fragments, especially if you are storing data and logs on different LUNS
> mapped to different physical disks. I suggest testing the backups heavily,
> especially where you take backups under heavy load. I suspect that there
> will be a fairly high failure rate. They may have this feature now, but
> when I was looking, they couldn't synchronize multiple LUNs for a snapshot,
> thus rendering the backups unreliable.
> I don't even bother with Snapview. I do SQL-level backups to a separate
> network file share (non-SAN based) and then spin those to tape to get an
> archive history. The extra storage is far cheaper than the Snapview license
> for a large SAN.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "wso" <wso@.discussions.microsoft.com> wrote in message
> news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
>
>

Backup SQL2000 on EMC

Hi all,
one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
They're using EMC SnapView for filesystem backup together with Veritas
NetBackup.
They won't use the SQL Integration Modules for SnapView so there is the
question of backing up the databases. Is there any other way to backup the
databases while using the SnapView functionality - and keeping SQL2000
services running?
Any help would be appreciated...
Wolfgang"wso" <wso@.discussions.microsoft.com> wrote in message
news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
> Hi all,
> one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
> They're using EMC SnapView for filesystem backup together with Veritas
> NetBackup.
> They won't use the SQL Integration Modules for SnapView so there is the
> question of backing up the databases. Is there any other way to backup the
> databases while using the SnapView functionality - and keeping SQL2000
> services running?
> Any help would be appreciated...
> Wolfgang
The data and log files are kept open and active while SQL Server is running.
I am assuming that Snap View won't touch them in this state.
The quick and easy solution is to schedule a regular SQL Server job to
perform SQL based backups to disk. Once the backup is complete, the backup
files are closed and Snap View can collect them (or Veritas for that
matter).
It's sort of doing double duty, but it does give you some flexibility.
Rick Sawtell
MCT, MCSD, MCDBA|||Your only problem is that you might get torn pages and/or log file
fragments, especially if you are storing data and logs on different LUNS
mapped to different physical disks. I suggest testing the backups heavily,
especially where you take backups under heavy load. I suspect that there
will be a fairly high failure rate. They may have this feature now, but
when I was looking, they couldn't synchronize multiple LUNs for a snapshot,
thus rendering the backups unreliable.
I don't even bother with Snapview. I do SQL-level backups to a separate
network file share (non-SAN based) and then spin those to tape to get an
archive history. The extra storage is far cheaper than the Snapview license
for a large SAN.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"wso" <wso@.discussions.microsoft.com> wrote in message
news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
> Hi all,
> one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
> They're using EMC SnapView for filesystem backup together with Veritas
> NetBackup.
> They won't use the SQL Integration Modules for SnapView so there is the
> question of backing up the databases. Is there any other way to backup the
> databases while using the SnapView functionality - and keeping SQL2000
> services running?
> Any help would be appreciated...
> Wolfgang|||Hello Geoff,
I was wondering if you can help me with the SAN/SQL Cluster backup question?
We have 3-Node (2 Virtual Instances with Standby) Cluster and one dedicated
LUN (Raid 5) for online backup.
Question1:
I think we need to expose the BACKUP LUN to each HOSTS? so SQL server backup
job can backup database on that LUN
Question2:
Once we expose that LUN to SQL CLuster Nodes the HOST will have full control
and how the backup wil work on second virtual host?
May be you can tell me what is the BEST practices of SAN/SQL Server backup
solutions. Just so you know we are trying to AVOID using the LAN (performance
etc.) and thereofore will also have dedicated backup server for TAPE backup...
Thanks.
Kamal Hassan
"Geoff N. Hiten" wrote:
> Your only problem is that you might get torn pages and/or log file
> fragments, especially if you are storing data and logs on different LUNS
> mapped to different physical disks. I suggest testing the backups heavily,
> especially where you take backups under heavy load. I suspect that there
> will be a fairly high failure rate. They may have this feature now, but
> when I was looking, they couldn't synchronize multiple LUNs for a snapshot,
> thus rendering the backups unreliable.
> I don't even bother with Snapview. I do SQL-level backups to a separate
> network file share (non-SAN based) and then spin those to tape to get an
> archive history. The extra storage is far cheaper than the Snapview license
> for a large SAN.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "wso" <wso@.discussions.microsoft.com> wrote in message
> news:748B28E5-9172-4AAB-877B-195AB1EFC035@.microsoft.com...
> > Hi all,
> >
> > one of my customers is going to use SQL2000 on existing EMC Clariion 4700.
> > They're using EMC SnapView for filesystem backup together with Veritas
> > NetBackup.
> > They won't use the SQL Integration Modules for SnapView so there is the
> > question of backing up the databases. Is there any other way to backup the
> > databases while using the SnapView functionality - and keeping SQL2000
> > services running?
> >
> > Any help would be appreciated...
> > Wolfgang
>
>

Backup SQL 2005 to SQL 2000

Hi,
Is there anyway to restore a database that was created on SQL 2005 into SQL
2000. We have a database that I have been doing some testing on, on SQL 2005
and need to restore it back to SQL 2000 but keep getting various messages.
I have read that there is no way to do this but thought I would drop a
message in here first to confirm this or to find a way to do it?
Thanks
Mike
Your question is already answered in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:6C40D5E5-325E-429B-AF38-ED1DCA162AFA@.microsoft.com...
> Hi,
> Is there anyway to restore a database that was created on SQL 2005 into SQL
> 2000. We have a database that I have been doing some testing on, on SQL 2005
> and need to restore it back to SQL 2000 but keep getting various messages.
> I have read that there is no way to do this but thought I would drop a
> message in here first to confirm this or to find a way to do it?
> Thanks
> Mike

Backup SQL 2005 to SQL 2000

Hi,
Is there anyway to restore a database that was created on SQL 2005 into SQL
2000. We have a database that I have been doing some testing on, on SQL 2005
and need to restore it back to SQL 2000 but keep getting various messages.
I have read that there is no way to do this but thought I would drop a
message in here first to confirm this or to find a way to do it?
Thanks
MikeYour question is already answered in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:6C40D5E5-325E-429B-AF38-ED1DCA162AFA@.microsoft.com...
> Hi,
> Is there anyway to restore a database that was created on SQL 2005 into SQ
L
> 2000. We have a database that I have been doing some testing on, on SQL 20
05
> and need to restore it back to SQL 2000 but keep getting various messages.
> I have read that there is no way to do this but thought I would drop a
> message in here first to confirm this or to find a way to do it?
> Thanks
> Mike

Thursday, March 22, 2012

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
Jamil
I would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign i
t
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than on
e
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along wit
h
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
This allows you to specify how long you want the backup files to be retained
.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_CopyLatestBackup.html
http://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.html
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup routines

Hi there,

I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,

job: daily backup

step1. truncate log
step2. shrink log
step3. backup
step. updateusage

job weekly admin

step1. defrag indexes (sometimes re-index + update stats)
step2. truncate log
step3. shrink db
step4. checkdb

Does this look good enough?
is there anything else I should add?
What do you have in place currently?

Thanks for your help in advanceYou should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.|||and you ideally want to move a set of backups offsite and preferably not in the same town\city\suburban sprawl office park just in case of things like hurricanes\flooding\tornados.

another hint. you do not want to truncate your logs. you want to back them up.|||I am lazy and manage a number of different servers. My priorities are to have a consistent backup strategy from server to server and to ensure that the backup will always be there when I need it.

For SQL 2000:
I create two maintenance plans (skipping optimizations, shrinkage and integrity checks).
One maintenance plan is for all the system databases. I do a full backup on these nightly.
One maintenance plan is for all user databases. I do a full backup on these nightly and a transaction log backup hourly (in test, only every three hours).

Depending on the size of the backup, I will backup over the network (we are running 1 GB switched) or local to a dedicated dump disk. Generally, if the aggregate of user databases to be backed up is over 25 GB, then I will back it up locally (we use a SAN, I have a separate job that makes an image copy of the backup disk and runs it to tape once per week).

I set the retention policy to a value based on business requirements and available storage.

For SQL 2005:
I create two maintenance plans.
One does a full backup on all databases (user and system). This runs nightly.
The other maintenance plan does a transaction log backup every hour (three hours in test).
I then edit the maintenance plan to add the retention policy and cleanup activity logs and reports.

All that being said, when you design your backup policy, you need to understand:

1. How much data is your business willing to lose (all, some or none)?
2. How much time is your business willing to spend recovering the database (days, weeks, hours, minutes or seconds)?
3. How much money is your business willing to spend on backup and recovery?

These are the principle drivers for your backup strategy. The technology just lets you achieve the requirements that your business sets for you.

Once you have developed your backup strategy be SURE that you test it. Practice recovery at LEAST every three months. Practice different types of recovery (new build, recover to point in time, recover a full backup, etc).

Remember too, that there are other things that you need to backup on your database server besides just the databases:
1. Linked Server settings
2. DTS Packages
3. Logins
4. Jobs

Regards,

hmscott|||You should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.

If the DB is going to keep growing to a certain size, I would not shrink it, just for it to get to that size again, it's a wasted effort, plus you have to allocate all those extents again, and that's overhead.

I usually do

Full (user and system) - nightly with dbcc checkdb catalog, logins, dts jobs, backup devices, etc.
Differential - 3X a day
TLog - Every 1/2 to 1 hr
nightly idx defrag dbcc indexdefrag.
weekly reindex. dbcc reindex.

Sunday, March 11, 2012

Backup Planning SQL2000

Hi i have implemented this backup strategy :
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!'!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
.
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> > Hi i have implemented this backup strategy :
> >
> > 1 - 00:00 AM Backup FULL "myDB"
> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
entries
> > from Transaction Log
> > Shrink T-LOG
> >
> >
> > My Rocovery model for "MyDB"is "Full"
> >
> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
00:00
> > time) and restore a backup of T-LOg (at 12:00) and recover all
> information
> > from 00:00 AM to 12:00 PM ?!'!
> >
> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> > entries from Transaction Log" enable and in the same job (after backup)
i
> > shrink t-log ro recover a space ?
> >
> > Thanks in advance.
> >
> >
>|||No. Make certain you do frequent enough transaction log backups. If the database crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Read about the NO_TRUNCATE
option to the BACKUP LOG command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
>> I would go for the following option
>> 00:00 Full backup
>> 06:00 Log backup - The log is backed up from the last successfully
>> executed log backup or full backup to the current end of the log. Once
> the
>> log is backed up the space may be truncated when no longer required by a
>> transaction or replication.
>> 12:00 Log backup
>>
>> If @. 14:00 the system crashes I will restore the full backup followed by
>> 6:00 and 12:00 logs. (This way the transaction log remais small and do
> not
>> have to shrink)
>> Other option could be
>> 00:00 Full backup
>> 06:00 Differential
>> 12:00 Differential
>> If @. 14:00 the system crashes, I would restore Full backup and 12:00
> copies.
>>
>> I am not sure as to why you would like to specifically want to keep
> inactive
>> transactions.
>> HTH.
>> Bhanu.
>>
>> .
>>
>> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
>> > Hi i have implemented this backup strategy :
>> >
>> > 1 - 00:00 AM Backup FULL "myDB"
>> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
> entries
>> > from Transaction Log
>> > Shrink T-LOG
>> >
>> >
>> > My Rocovery model for "MyDB"is "Full"
>> >
>> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
> 00:00
>> > time) and restore a backup of T-LOg (at 12:00) and recover all
>> information
>> > from 00:00 AM to 12:00 PM ?!'!
>> >
>> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
>> > entries from Transaction Log" enable and in the same job (after backup)
> i
>> > shrink t-log ro recover a space ?
>> >
>> > Thanks in advance.
>> >
>> >
>>
>|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>

Backup Planning SQL2000

Wednesday, March 7, 2012

Backup only one table

Hi
Does anyone know how to backup just one table (or selected tables) in
SQL2000?
Any help will be appreciated.
Thanks
CraigYou can't do that using BACKUP/RESTORE (well, you can put them on their own filegroups, but your
restore options are severely limited). How about using BCP/DTS etc ?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Craig Bryden" <craig@.ch.co.za> wrote in message news:Ozo3oFuaDHA.3444@.tk2msftngp13.phx.gbl...
> Hi
> Does anyone know how to backup just one table (or selected tables) in
> SQL2000?
> Any help will be appreciated.
> Thanks
> Craig
>

Saturday, February 25, 2012

backup of database problem

i'm trying to backup a database on sql2000.
i go right click on the database, all tasks, backup database, and when i
want to add a location, it only gives me the directories of the server(our
file server) where the databases reside on.
how can i change my location ?
looking in my computer it has a mapped drive that i want to use, but it
keeps showing me the file server, and i want to backup it on another server.
any help will be greatly appreciated
thanks in advance
yanchriType in the UNC path, and make sure that the SQL server service has permissions on the share.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yanchri Gerber" <yg@.greenafrica.co.za> wrote in message news:cmffp8$l3h$1@.ctb-nnrp2.saix.net...
> i'm trying to backup a database on sql2000.
> i go right click on the database, all tasks, backup database, and when i
> want to add a location, it only gives me the directories of the server(our
> file server) where the databases reside on.
> how can i change my location ?
> looking in my computer it has a mapped drive that i want to use, but it
> keeps showing me the file server, and i want to backup it on another server.
> any help will be greatly appreciated
> thanks in advance
> yanchri
>

backup of database problem

i'm trying to backup a database on sql2000.
i go right click on the database, all tasks, backup database, and when i
want to add a location, it only gives me the directories of the server(our
file server) where the databases reside on.
how can i change my location ?
looking in my computer it has a mapped drive that i want to use, but it
keeps showing me the file server, and i want to backup it on another server.
any help will be greatly appreciated
thanks in advance
yanchri
Type in the UNC path, and make sure that the SQL server service has permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yanchri Gerber" <yg@.greenafrica.co.za> wrote in message news:cmffp8$l3h$1@.ctb-nnrp2.saix.net...
> i'm trying to backup a database on sql2000.
> i go right click on the database, all tasks, backup database, and when i
> want to add a location, it only gives me the directories of the server(our
> file server) where the databases reside on.
> how can i change my location ?
> looking in my computer it has a mapped drive that i want to use, but it
> keeps showing me the file server, and i want to backup it on another server.
> any help will be greatly appreciated
> thanks in advance
> yanchri
>

backup of database problem

i'm trying to backup a database on sql2000.
i go right click on the database, all tasks, backup database, and when i
want to add a location, it only gives me the directories of the server(our
file server) where the databases reside on.
how can i change my location ?
looking in my computer it has a mapped drive that i want to use, but it
keeps showing me the file server, and i want to backup it on another server.
any help will be greatly appreciated
thanks in advance
yanchriType in the UNC path, and make sure that the SQL server service has permissi
ons on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yanchri Gerber" <yg@.greenafrica.co.za> wrote in message news:cmffp8$l3h$1@.ctb-nnrp2.saix.ne
t...
> i'm trying to backup a database on sql2000.
> i go right click on the database, all tasks, backup database, and when i
> want to add a location, it only gives me the directories of the server(our
> file server) where the databases reside on.
> how can i change my location ?
> looking in my computer it has a mapped drive that i want to use, but it
> keeps showing me the file server, and i want to backup it on another serve
r.
> any help will be greatly appreciated
> thanks in advance
> yanchri
>

Tuesday, March 27, 2012

Backup SQL2000 on EMC

Backup SQL2000 on EMC

Backup SQL 2005 to SQL 2000

Backup SQL 2005 to SQL 2000

Thursday, March 22, 2012

Backup script

Backup script

Backup script

Backup routines

Sunday, March 11, 2012

Backup Planning SQL2000

Backup Planning SQL2000

Backup Planning SQL2000

Wednesday, March 7, 2012

Backup only one table

Saturday, February 25, 2012

backup of database problem

backup of database problem

backup of database problem

sql backup files

Blog Archive

About Me