Backup Question

I have some SQL 2000 and SQL 2005 servers as part of my network and I am
including the SQL databases are part of my nightly backup routines. How I do
this is set up SQL jobs to create .bak files early in the evening so that the
nightly backup "sweep" then includes them in the backup since the .mdf and
.ldf are open files. This work great but is it the best way? Does not
Volume Shadow Copy help me work around this issue so that I can backup open
.mdf and .ldf files? If so then how does that work since I'm not familiar
with it.
Basically, what does everyone else do as part of the backup rountines to
make sure SQL Server data gets backed up and what databases (outside of your
application databases) do you backup (master etc.)?
Thanks!
-Richard KHello,
There are a few of ways you can tackle this...
1. Use a third party tool that can back up the database directly to tape
like Backup Exec or Lite Speed etc.
2. Create a maintenance plan to back up the database at a schedule time to
your directory.
3. Online Back ups
4. Etc.
There are lots of good information on how to set up a maintenance plan in
the SQL Books online or check out
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1076630,00.html
I Hope this helps.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Sorry I left out a few things..
If you are doing replication you will want to back up the Back up the
Publisher, Distributor, Subscriber(s) and Master DBs and all your SQL Users.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ESSAG
Also, depending how important your data is you may want to do Trans Logs in
between full backups.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Richard K wrote:
> I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I do
> this is set up SQL jobs to create .bak files early in the evening so that the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of your
> application databases) do you backup (master etc.)?
I saw Christina's answers but decided to describe what I normally do.
1. The \Data directory is excluded from the file system (FS) backup
2. A management plan (or several plans if you need to do the transaction
log backups for some databases and not for others) puts all the backup
files into \Backup directory and deletes them after N days (depending on
the space capacities).
3. A file system backup writes to tapes those *.bak and *.trn files
created by the maintenance routine.
The timing and specifics depend on the business needs and probability of
disasters.
If the most probable disaster is a data loss caused by the
application/operator (i.e. the SQL Server is up and running but the data
was lost), you better have SQL backups ready and copy them to the tape
even 20+ hours after they are created.
If the most probable disaster is a hardware failure and the backups need
to be restored on a separate SQL Server, then it is better to schedule
the SQL backups prior to the FS or trigger the FS backup at the end of
the SQL backup.|||"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
>
Don't back up the .MDF and .LDF files.
Even with volume shadow copy you won't get a consistent backup.
Imagine this scenario. You backup MyDB.MDF at 11:00 PM.
By the time you get around to backing up MyDB.LDF at 11:15 PM, new
transactions have been completed in MyDB and some new ones have been
started, but not completed.
Now things crash.
You restore MyDB.MDF. Ok, all's good and fine. You restore MyDB.LDF.
Now you try to start up the DB. It can't. Why? Because MyDB.LDF has open
transactions that don't even exist in MyDB.MDF yet and MyDBF has open
transactions that MyDB.LDF things are completed.
So, yes, simply backup to disk as you seem to want to do. Simply figure out
how long it takes to do a full backup.
Generally I did full backups of my user databases 3 nights a week. And then
transaction log backups every 15 minutes.
WORST case scenario for recovery was to restore the most recent full backup
and then 288 or so transaction log backups.
Now, if I'm then backing them up to tape, well I simply make sure I keep at
least 3 days worth of tapes around.
Generally though I like to backup to a snap server or other NAS device and
keep them there for a week (with tape backups as desired). This makes
recovery even faster.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
I backed up the system databases daily since they're small and generally can
only have simple recovery mode, so to capture changes, I wanted to make sure
we had a fairly recent copy.
> Thanks!
> -Richard K
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup of a WSS site with STSADM.EXE

I want to backup an entire site in WSS 2.0 (including subsites and published
web parts and documents).
I haven't founded the documentation related that explains the use of the
various options of STSADM command.
Is it correct this statement?
"C:\Programmi\File comuni\Microsoft Shared\web server
extensions\60\BIN\STSADM" -o backup -url http://localhost -filename
"D:\BACKUP PORTALE\PortaleRia.BAK" -overwrite
Does exist the documentation that illustrates the use of the parameters of
STSADM command?
Many thanks for your helps.I think you sent this to the wrong newsgroup. This is for Reporting
Services. You might want to post to
microsoft.public.sharepoint.windowsservices instead.
But anyway, to answer your question, you can find more information about
backup and restore of WSS 2.0 at
"Backing Up and Restoring Web Sites"
http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsf20.mspx?mfr=true
This article gives you some examples for using stsadm for backup and
restore, describing the parameters and what to do.
If you need more info, you'll probably get more responses in that other
newsgroup. :)
Kaisa M. Lindahl Lervik
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:CF13615B-6C76-4554-BC16-8658A60CB471@.microsoft.com...
>I want to backup an entire site in WSS 2.0 (including subsites and
>published
> web parts and documents).
> I haven't founded the documentation related that explains the use of the
> various options of STSADM command.
> Is it correct this statement?
> "C:\Programmi\File comuni\Microsoft Shared\web server
> extensions\60\BIN\STSADM" -o backup -url http://localhost -filename
> "D:\BACKUP PORTALE\PortaleRia.BAK" -overwrite
> Does exist the documentation that illustrates the use of the parameters of
> STSADM command?
> Many thanks for your helps.
>

Backup Maintence Plan Fails

I get the error every day. i've checked everything i know
of to make sure that nothing including the backup job is
accessing the database. This job has run for years and is
suddenly failing for the last few weeks. Any help would be
appreciated. Win2k Server SQL2000 with SP3.
Lamar
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
3041 : BACKUP failed to complete the command BACKUP
DATABASE [DMD1] TO DISK = N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
18210 : BackupMedium::ReportIoError: write failure on
backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
Operating system error 33(The process cannot access the
file because another process has locked a portion of the
file.). "Seems like some other program is using and locking the backup file. You have to hunt that down.
Could be some anti-virus program, for instance. I thinks that www.sysinternals has tools for this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lamar Mrris" <LamarMorris@.ti.com> wrote in message news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> I get the error every day. i've checked everything i know
> of to make sure that nothing including the backup job is
> accessing the database. This job has run for years and is
> suddenly failing for the last few weeks. Any help would be
> appreciated. Win2k Server SQL2000 with SP3.
> Lamar
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 3041 : BACKUP failed to complete the command BACKUP
> DATABASE [DMD1] TO DISK => N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 18210 : BackupMedium::ReportIoError: write failure on
> backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> Operating system error 33(The process cannot access the
> file because another process has locked a portion of the
> file.). "
>
>|||... or maybe the backup file is being backed up -- to tape, for example.
Whatever the case, checking with the system admin should provide some help,
at least eliminate some candidate problem sources.
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u3gxJDWnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Seems like some other program is using and locking the backup file. You
have to hunt that down.
> Could be some anti-virus program, for instance. I thinks that
www.sysinternals has tools for this.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> > I get the error every day. i've checked everything i know
> > of to make sure that nothing including the backup job is
> > accessing the database. This job has run for years and is
> > suddenly failing for the last few weeks. Any help would be
> > appreciated. Win2k Server SQL2000 with SP3.
> >
> > Lamar
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 3041 : BACKUP failed to complete the command BACKUP
> > DATABASE [DMD1] TO DISK => > N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> > NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 18210 : BackupMedium::ReportIoError: write failure on
> > backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> > Operating system error 33(The process cannot access the
> > file because another process has locked a portion of the
> > file.). "
> >
> >
> >
>|||Thanks for the input, i've already excluded the whole directory from
Virus scanning or realtime protection.
Lamar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||To add to what Tibor said, you can get a utility called
nthandle.exe from www.sysinternals.com.
To help you determine what process is holding that backup
file, you can modify your backup job to add a step before
the backup step. In this step, run:
path>nthandle.exe DMD1_db
and make sure that you specify the output file for this
step.
When the backup job fails, you can then review the output
file for the first step to identify the offending process
that was holding your backup file.
My guess would be: it's your tape backup process if you
are running a tape backup process to backup the files on
the disk.
Linchi
>--Original Message--
>Seems like some other program is using and locking the
backup file. You have to hunt that down.
>Could be some anti-virus program, for instance. I thinks
that www.sysinternals has tools for this.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
>> I get the error every day. i've checked everything i
know
>> of to make sure that nothing including the backup job is
>> accessing the database. This job has run for years and
is
>> suddenly failing for the last few weeks. Any help would
be
>> appreciated. Win2k Server SQL2000 with SP3.
>> Lamar
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 3041 : BACKUP failed to complete the command BACKUP
>> DATABASE [DMD1] TO DISK =>> N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
>> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 18210 : BackupMedium::ReportIoError: write failure on
>> backup
device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
>> Operating system error 33(The process cannot access the
>> file because another process has locked a portion of the
>> file.). "
>>
>
>.
>

Backup hangs in wait-state MSSEARCH

I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.

The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.

When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.

I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.

The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.

Kim,

This is very likely a bug. I suggest you contact Microsoft support.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.

There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.

The quick solution is to cycle the MSSEARCH service, which should get backups moving.

If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.

|||

Hi Kevin,

We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.

The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.

|||

I'm assuming that you have fulltext catalogs in your database?

So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.

If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com

|||

Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}

Thanks for the all the good will shown in these forums.

Kim Brandt Jensen

Backup hangs in wait-state MSSEARCH

I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.

The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.

When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.

I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.

The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.

Kim,

This is very likely a bug. I suggest you contact Microsoft support.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.

There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.

The quick solution is to cycle the MSSEARCH service, which should get backups moving.

If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.

|||

Hi Kevin,

We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.

The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.

|||

I'm assuming that you have fulltext catalogs in your database?

So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.

If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com

|||

Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}

Thanks for the all the good will shown in these forums.

Kim Brandt Jensen