Backup / Restore databse with filegroups

I hope this is the right forum, if not sorry about that. On Friday I will be doing support with another orgnaization on a SQL 2000 cluster system. The database will be backed up , another team will do the major application upgrade, and I will be restoring the database back. From the SCN, there will be no database changes. I have been searchng for information on databse backups / filegroups.

My questions are:

1. The database has 3 filegroups, besides the primary. If I do a complete backup, will it also do the filegroups?

2. If not, how do I backup the filegroups?

3. Whne I resote the databse, how do I bring in the filegroups from the backup?

Any info or pointers to links is greatly appreciated. Have a good day.

Carl

Regular database backup will backup all the filegroups including primary unless you are taking the exclusive filegroup backup...

Check the Books online for Backup command...

When you restore full backup it will restore all the filegroups... if you are restoring to different drive you may need to use WITH MOVE option...

Again check Books Online for the correct syntax...

Backup

I have backed up a sql server 2000 db to a BAK file called george.bak for
exapmle. I need to restore this databse into a another sqll server 2000.
How can i do this using enterprise manager or any method.I'd prefer doing such things by QA
exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
FROM DISK =''C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\testback.bak'''
Note : Take a look at RESTORE article in the BOL you 'll need probably use
WITH MOVE option
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>I have backed up a sql server 2000 db to a BAK file called george.bak for
> exapmle. I need to restore this databse into a another sqll server 2000.
> How can i do this using enterprise manager or any method.|||Hello George,
You may want to refer to the following articles which shall address this
issue:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Uri Dimant" <urid@.iscar.co.il>
>References: <D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com>
>Subject: Re: Backup
>Date: Sun, 22 Jan 2006 10:23:56 +0200
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uZ2Zx0yHGHA.2040@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: bzq-25-106-78.cust.bezeqint.net 212.25.106.78
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418437
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I'd prefer doing such things by QA
>
>exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
>FROM DISK =''C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\testback.bak'''
>
>Note : Take a look at RESTORE article in the BOL you 'll need probably
use
>WITH MOVE option
>
>
>"George Schneider" <georgedschneider@.news.postalias> wrote in message
>news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>
>

Backup

I have backed up a sql server 2000 db to a BAK file called george.bak for
exapmle. I need to restore this databse into a another sqll server 2000.
How can i do this using enterprise manager or any method.
I'd prefer doing such things by QA
exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
FROM DISK =''C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\testback.bak'''
Note : Take a look at RESTORE article in the BOL you 'll need probably use
WITH MOVE option
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>I have backed up a sql server 2000 db to a BAK file called george.bak for
> exapmle. I need to restore this databse into a another sqll server 2000.
> How can i do this using enterprise manager or any method.
|||Hello George,
You may want to refer to the following articles which shall address this
issue:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Uri Dimant" <urid@.iscar.co.il>
>References: <D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com>
>Subject: Re: Backup
>Date: Sun, 22 Jan 2006 10:23:56 +0200
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uZ2Zx0yHGHA.2040@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: bzq-25-106-78.cust.bezeqint.net 212.25.106.78
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418437
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I'd prefer doing such things by QA
>
>exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
>FROM DISK =''C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\testback.bak'''
>
>Note : Take a look at RESTORE article in the BOL you 'll need probably
use
>WITH MOVE option
>
>
>"George Schneider" <georgedschneider@.news.postalias> wrote in message
>news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>
>

Backup

I have backed up a sql server 2000 db to a BAK file called george.bak for
exapmle. I need to restore this databse into a another sqll server 2000.
How can i do this using enterprise manager or any method.I'd prefer doing such things by QA
exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
FROM DISK =''C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\testback.bak'''
Note : Take a look at RESTORE article in the BOL you 'll need probably use
WITH MOVE option
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>I have backed up a sql server 2000 db to a BAK file called george.bak for
> exapmle. I need to restore this databse into a another sqll server 2000.
> How can i do this using enterprise manager or any method.|||Hello George,
You may want to refer to the following articles which shall address this
issue:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Uri Dimant" <urid@.iscar.co.il>
>References: <D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com>
>Subject: Re: Backup
>Date: Sun, 22 Jan 2006 10:23:56 +0200
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uZ2Zx0yHGHA.2040@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: bzq-25-106-78.cust.bezeqint.net 212.25.106.78
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:418437
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I'd prefer doing such things by QA
>
>exec SRV.master.dbo.sp_executesql N'RESTORE DATABASE pubs
>FROM DISK =''C:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP\testback.bak'''
>
>Note : Take a look at RESTORE article in the BOL you 'll need probably
use
>WITH MOVE option
>
>
>"George Schneider" <georgedschneider@.news.postalias> wrote in message
>news:D2AE92DF-5D3F-40C5-ACFD-5E2F75AD9D11@.microsoft.com...
>>I have backed up a sql server 2000 db to a BAK file called george.bak for
>> exapmle. I need to restore this databse into a another sqll server 2000.
>> How can i do this using enterprise manager or any method.
>
>

Backup

I need to develop a backup plan for my SQL Server using the Databse
Maintenace plan. What is the recommended configuration to backup the server.
I will be backing it up to the file system first and tehn eventual backup it
up to tape via a nother server.Hi George
There is never one definite answer to this, you should read the topic
"Designing a Backup and Restore Strategy" in Books online. Your backup needs
will be governed by things like the risk system failure and loss of data, the
time required to restore the system and against the effect on hardware such
as space requirements, duration of backup process and resources required to
backup the system.
John
"George Schneider" wrote:
> I need to develop a backup plan for my SQL Server using the Databse
> Maintenace plan. What is the recommended configuration to backup the server.
> I will be backing it up to the file system first and tehn eventual backup it
> up to tape via a nother server.
>|||Hi George.
Thanks for John's suggestion.
You may also refer to
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_9zcj.asp>
If there is anything unclear, please let me know. I'll be glad to be of
assistance.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
>>Thread-Topic: Backup
>>thread-index: AcY4kkUVUbLW73gQTVe4GjsItVNNWg==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: "=?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?="
<georgedschneider@.news.postalias>
>>Subject: Backup
>>Date: Thu, 23 Feb 2006 08:00:30 -0800
>>Lines: 6
>>Message-ID: <B8F91A6C-312A-46A4-9120-5E06BF7BEC84@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:422127
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>I need to develop a backup plan for my SQL Server using the Databse
>>Maintenace plan. What is the recommended configuration to backup the
server.
>> I will be backing it up to the file system first and tehn eventual
backup it
>>up to tape via a nother server.
>>

Backp Solution

Hi ,
What is the best Backup plan that can be give for a sql databse that is on a very high usage.
Will a six hour backup will decrease the performance of the SQL server...
SipinIt depends on a lot of things:
Type of db: OLTP vs. OLAP
Size of db: 2GB vs. 1TB

"High usage databases" I am assuming means an OLTP type of database not an OLAP db. With an OLTP db your data will be changing rapidly and I would recommend doing hourly transaction log backups with a daily full backup (if possible, depending on the size of the db).

If the size of the database is relatively small, (like 2 - 10 GB) doing a full backup once every 6 hours is okay. But if the database is larger in size I do not recommend doing full backups during transaction hours.

Realize that if you decide to go with backup every 6 hours, you run the risk of losing up to 6 hrs worth of work if something bad happens, as to hourly tlog backups you will only lose 1hrs worth of work. There is a price to pay however for hourly tlog backups, and that is more maintenance, and it will take longer to do a full recovery and then apply several tlogs.

Consider both the business and technical side of backups

Good luck
I hope this helps