Database backups

HI All
can some one please shed some light on backups please?
im running sql 2000 and i want to know how often i should
backup and restore my MSDB and MASTER databases.
Thanks in advance
ToddI personally do them every night. You really need to back them up though
when something in them changes. Seeing as they are system databases this
should be through the SQL Server interface or stored procs i.e.
New Jobs
New Logins
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:030a01c3a70e$35404110$a101280a@.phx.gbl...
> HI All
> can some one please shed some light on backups please?
> im running sql 2000 and i want to know how often i should
> backup and restore my MSDB and MASTER databases.
> Thanks in advance
> Todd|||MSDB is important especially if you are doing things like
replication, etc. SQL Server uses it to store other
configuration/use. If you are not using any features that
utilize MSDB, then it is less important, but still
critical for SQL Server health. Generally it is fairly
small, so backing it up every night isn't a bad idea.
Master is the brains of the operation. It, too, is small,
but without a good and recent backup, you may be in
trouble if you need to restore it. I suggest nightly, but
at worst case, after updates such as a service pack since
you cannot, for example, restore a SP2 master to a SP3 SQL
Server.
You should never have to restore, but should always test
your backups elsewhere to ensure they are actually good.
However, in a D/R scenario, applying MSDB or MASTER to
another server could potentially have large negative
consequences since they contain configurations specific to
a particular instance of SQL Server.|||Hi,
I will do a daily backup of MSDB and Master database. As well as I have a
scheduled OS command which stops SQL Server and Agent during
sunday and copy the Master.mdf and mastlog.ldf file to a backup location.
This will help you a lot during crash situations.
Thanks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:030a01c3a70e$35404110$a101280a@.phx.gbl...
> HI All
> can some one please shed some light on backups please?
> im running sql 2000 and i want to know how often i should
> backup and restore my MSDB and MASTER databases.
> Thanks in advance
> Todd

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
Ivan
Hi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>
|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files[vbcol=seagreen]
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another
> and
>

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> > I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> > device.
> >
> > I have backup the database into a file, and try to restore it into
another
> > SQL Server 2000.
> > The new database has only 1 data device and 1 log.
> > However, when I try to restore it, in Option page, it has 2 data device
> and
> > 2 log device and I have no way to restore
> >
> > how can I restore that database?
> >
> > Ivan
> >
> >
>sql

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another[vbcol=seagreen]
> and
>

Database backup using c#

Hi can anybody know how to get the functionality of database backup and restore of a sql server2005 using asp.net 2.0 and c#..

My problem is here I'm creating an application which uses Emp file. and I'm trying to get backup of that database and again I'm trying to restore with that backup file. But here I'm getting the error message like "Database is already in use"

Is there any solution for that?

Regards,

Nagu

Hi Nagu,

Please check to see if the database is being used by some other objects, such as replications.

If that doesn't work, please show some of your restored database code. Thanks!

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
Abbas
Most probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the tim
e. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when y
ou restored, you
probably didn't specify a FILE option, which meant you restore the first one
= the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2G
B.
> to investigate, When backup taken from enterprise manager it is 60 MB. Wha
t
> could be the rerasons?. The clients are doing hundered of inserts daily. b
ut
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

Database backup script

In SQL 7, we backup and restore an OLTP database to
another server to off-load some processing and to have a
static copy of the database at a point in time.
Here's the procedure:
--restore from full backup
RESTORE DATABASE <database name> from
DISK =
N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
WITH MOVE '<file name 1>' to 'location\file1.mdf',
MOVE '<file name 2' to 'location\file2.mdf',
MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
NORECOVERY, REPLACE
--restore differential backup
RESTORE DATABASE <database name> FROM DISK
= '<server>\<share>\<folder>\<subfolder>\filename.BA K'
with recovery
Periodically, this script completes, but leaves the
restored database in 'loading' status. I know how to
correct this, by using 'RESTORE DATABASE with RECOVERY',
but I'm wondering why this happens sometimes and not
others. Any suggestions?
Are you sure that your script gets all the way through the differential
restore? If for some reason the differential restore didn't happen or
failed, then you database would be in "loading" status.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Thayer" <dthayer@.deltanet.net> wrote in message
news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
> In SQL 7, we backup and restore an OLTP database to
> another server to off-load some processing and to have a
> static copy of the database at a point in time.
> Here's the procedure:
> --restore from full backup
> RESTORE DATABASE <database name> from
> DISK =
> N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
> WITH MOVE '<file name 1>' to 'location\file1.mdf',
> MOVE '<file name 2' to 'location\file2.mdf',
> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
> NORECOVERY, REPLACE
> --restore differential backup
> RESTORE DATABASE <database name> FROM DISK
> = '<server>\<share>\<folder>\<subfolder>\filename.BA K'
> with recovery
>
> Periodically, this script completes, but leaves the
> restored database in 'loading' status. I know how to
> correct this, by using 'RESTORE DATABASE with RECOVERY',
> but I'm wondering why this happens sometimes and not
> others. Any suggestions?
|||Initially, this was my suspicion, but upon review of the
data, the diff restore did complete, but just left the
database in 'load'.

>--Original Message--
>Are you sure that your script gets all the way through
the differential
>restore? If for some reason the differential restore
didn't happen or
>failed, then you database would be in "loading" status.
>--
>----
--
>----
--[vbcol=seagreen]
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"David Thayer" <dthayer@.deltanet.net> wrote in message
>news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
>
>.
>

Database backup script

In SQL 7, we backup and restore an OLTP database to
another server to off-load some processing and to have a
static copy of the database at a point in time.
Here's the procedure:
--restore from full backup
RESTORE DATABASE <database name> from
DISK =
N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
WITH MOVE '<file name 1>' to 'location\file1.mdf',
MOVE '<file name 2' to 'location\file2.mdf',
MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
NORECOVERY, REPLACE
--restore differential backup
RESTORE DATABASE <database name> FROM DISK
= '<server>\<share>\<folder>\<subfolder>\filename.BA K'
with recovery
Periodically, this script completes, but leaves the
restored database in 'loading' status. I know how to
correct this, by using 'RESTORE DATABASE with RECOVERY',
but I'm wondering why this happens sometimes and not
others. Any suggestions?
Are you sure that your script gets all the way through the differential
restore? If for some reason the differential restore didn't happen or
failed, then you database would be in "loading" status.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Thayer" <dthayer@.deltanet.net> wrote in message
news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
> In SQL 7, we backup and restore an OLTP database to
> another server to off-load some processing and to have a
> static copy of the database at a point in time.
> Here's the procedure:
> --restore from full backup
> RESTORE DATABASE <database name> from
> DISK =
> N'\\<server>\<share>\<folder>\<subfolder>\file_1.b ak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
> WITH MOVE '<file name 1>' to 'location\file1.mdf',
> MOVE '<file name 2' to 'location\file2.mdf',
> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
> NORECOVERY, REPLACE
> --restore differential backup
> RESTORE DATABASE <database name> FROM DISK
> = '<server>\<share>\<folder>\<subfolder>\filename.BA K'
> with recovery
>
> Periodically, this script completes, but leaves the
> restored database in 'loading' status. I know how to
> correct this, by using 'RESTORE DATABASE with RECOVERY',
> but I'm wondering why this happens sometimes and not
> others. Any suggestions?
|||Initially, this was my suspicion, but upon review of the
data, the diff restore did complete, but just left the
database in 'load'.

>--Original Message--
>Are you sure that your script gets all the way through
the differential
>restore? If for some reason the differential restore
didn't happen or
>failed, then you database would be in "loading" status.
>--
>----
--
>----
--[vbcol=seagreen]
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"David Thayer" <dthayer@.deltanet.net> wrote in message
>news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
N'<server>\<share>\<folder>\<subfolder>\file_2.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_3.bak ',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_4.bak '
>
>.
>

Database backup script

In SQL 7, we backup and restore an OLTP database to
another server to off-load some processing and to have a
static copy of the database at a point in time.
Here's the procedure:
--restore from full backup
RESTORE DATABASE <database name> from
DISK =
N'\\<server>\<share>\<folder>\<subfolder>\file_1.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak'
WITH MOVE '<file name 1>' to 'location\file1.mdf',
MOVE '<file name 2' to 'location\file2.mdf',
MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
NORECOVERY, REPLACE
--restore differential backup
RESTORE DATABASE <database name> FROM DISK
= '<server>\<share>\<folder>\<subfolder>\filename.BAK'
with recovery
Periodically, this script completes, but leaves the
restored database in 'loading' status. I know how to
correct this, by using 'RESTORE DATABASE with RECOVERY',
but I'm wondering why this happens sometimes and not
others. Any suggestions?Are you sure that your script gets all the way through the differential
restore? If for some reason the differential restore didn't happen or
failed, then you database would be in "loading" status.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Thayer" <dthayer@.deltanet.net> wrote in message
news:227bb01c45df3$a0fe1a20$a601280a@.phx
.gbl...
> In SQL 7, we backup and restore an OLTP database to
> another server to off-load some processing and to have a
> static copy of the database at a point in time.
> Here's the procedure:
> --restore from full backup
> RESTORE DATABASE <database name> from
> DISK =
> N'\\<server>\<share>\<folder>\<subfolder>\file_1.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak'
> WITH MOVE '<file name 1>' to 'location\file1.mdf',
> MOVE '<file name 2' to 'location\file2.mdf',
> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
> NORECOVERY, REPLACE
> --restore differential backup
> RESTORE DATABASE <database name> FROM DISK
> = '<server>\<share>\<folder>\<subfolder>\filename.BAK'
> with recovery
>
> Periodically, this script completes, but leaves the
> restored database in 'loading' status. I know how to
> correct this, by using 'RESTORE DATABASE with RECOVERY',
> but I'm wondering why this happens sometimes and not
> others. Any suggestions?|||Initially, this was my suspicion, but upon review of the
data, the diff restore did complete, but just left the
database in 'load'.

>--Original Message--
>Are you sure that your script gets all the way through
the differential
>restore? If for some reason the differential restore
didn't happen or
>failed, then you database would be in "loading" status.
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"David Thayer" <dthayer@.deltanet.net> wrote in message
> news:227bb01c45df3$a0fe1a20$a601280a@.phx
.gbl...
N'<server>\<share>\<folder>\<subfolder>\file_2.bak',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_3.bak',[vbcol=seagreen]
N'<server>\<share>\<folder>\<subfolder>\file_4.bak'[vbcol=seagreen]
>
>.
>

Database backup script

In SQL 7, we backup and restore an OLTP database to
another server to off-load some processing and to have a
static copy of the database at a point in time.
Here's the procedure:
--restore from full backup
RESTORE DATABASE <database name> from
DISK = N'\\<server>\<share>\<folder>\<subfolder>\file_1.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak',
DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak'
WITH MOVE '<file name 1>' to 'location\file1.mdf',
MOVE '<file name 2' to 'location\file2.mdf',
MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
NORECOVERY, REPLACE
--restore differential backup
RESTORE DATABASE <database name> FROM DISK
= '<server>\<share>\<folder>\<subfolder>\filename.BAK'
with recovery
Periodically, this script completes, but leaves the
restored database in 'loading' status. I know how to
correct this, by using 'RESTORE DATABASE with RECOVERY',
but I'm wondering why this happens sometimes and not
others. Any suggestions?Are you sure that your script gets all the way through the differential
restore? If for some reason the differential restore didn't happen or
failed, then you database would be in "loading" status.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Thayer" <dthayer@.deltanet.net> wrote in message
news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
> In SQL 7, we backup and restore an OLTP database to
> another server to off-load some processing and to have a
> static copy of the database at a point in time.
> Here's the procedure:
> --restore from full backup
> RESTORE DATABASE <database name> from
> DISK => N'\\<server>\<share>\<folder>\<subfolder>\file_1.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_2.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_3.bak',
> DISK = N'<server>\<share>\<folder>\<subfolder>\file_4.bak'
> WITH MOVE '<file name 1>' to 'location\file1.mdf',
> MOVE '<file name 2' to 'location\file2.mdf',
> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
> NORECOVERY, REPLACE
> --restore differential backup
> RESTORE DATABASE <database name> FROM DISK
> = '<server>\<share>\<folder>\<subfolder>\filename.BAK'
> with recovery
>
> Periodically, this script completes, but leaves the
> restored database in 'loading' status. I know how to
> correct this, by using 'RESTORE DATABASE with RECOVERY',
> but I'm wondering why this happens sometimes and not
> others. Any suggestions?|||Initially, this was my suspicion, but upon review of the
data, the diff restore did complete, but just left the
database in 'load'.
>--Original Message--
>Are you sure that your script gets all the way through
the differential
>restore? If for some reason the differential restore
didn't happen or
>failed, then you database would be in "loading" status.
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"David Thayer" <dthayer@.deltanet.net> wrote in message
>news:227bb01c45df3$a0fe1a20$a601280a@.phx.gbl...
>> In SQL 7, we backup and restore an OLTP database to
>> another server to off-load some processing and to have a
>> static copy of the database at a point in time.
>> Here's the procedure:
>> --restore from full backup
>> RESTORE DATABASE <database name> from
>> DISK =>> N'\\<server>\<share>\<folder>\<subfolder>\file_1.bak',
>> DISK =N'<server>\<share>\<folder>\<subfolder>\file_2.bak',
>> DISK =N'<server>\<share>\<folder>\<subfolder>\file_3.bak',
>> DISK =N'<server>\<share>\<folder>\<subfolder>\file_4.bak'
>> WITH MOVE '<file name 1>' to 'location\file1.mdf',
>> MOVE '<file name 2' to 'location\file2.mdf',
>> MOVE '<log file name 1>' to 'location\LOGfile1.ldf',
>> MOVE '<log file name 2>' to 'location\LOGfile2.ldf',
>> NORECOVERY, REPLACE
>> --restore differential backup
>> RESTORE DATABASE <database name> FROM DISK
>> = '<server>\<share>\<folder>\<subfolder>\filename.BAK'
>> with recovery
>>
>> Periodically, this script completes, but leaves the
>> restored database in 'loading' status. I know how to
>> correct this, by using 'RESTORE DATABASE with RECOVERY',
>> but I'm wondering why this happens sometimes and not
>> others. Any suggestions?
>
>.
>

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the following error: " The media set for database "isis" has 2 family members but only 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a system problem now the sql 2000 server us up and when i try to restore the database it gives me the above error. I hope some one out there can help.
Ray
You striped the backup over two backup files, and now you try to restore from only one of those
backups files. You need to find that other backup file before you can restore.
(This is similar to having two disks in a raid 0, take one of those to another machine and expect to
get to the data on that machine... :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following error: " The
media set for database "isis" has 2 family members but only 1 are provided. All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a
system problem now the sql 2000 server us up and when i try to restore the database it gives me the
above error. I hope some one out there can help.
> Ray
>

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the following error: " The media set for database "isis" has 2 family members but only 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a system problem now the sql 2000 server us up and when i try to restore the database it gives me the above error. I hope some one out there can help.
RayYou striped the backup over two backup files, and now you try to restore from only one of those
backups files. You need to find that other backup file before you can restore.
(This is similar to having two disks in a raid 0, take one of those to another machine and expect to
get to the data on that machine... :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following error: " The
media set for database "isis" has 2 family members but only 1 are provided. All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 server due to a
system problem now the sql 2000 server us up and when i try to restore the database it gives me the
above error. I hope some one out there can help.
> Ray
>

database backup restore problem!

Attempting to do a data base recovery from backup and keep getting the follo
wing error: " The media set for database "isis" has 2 family members but on
ly 1 are provided. All members must be provided."
I backed up the database in sql 2000 server and had to reinstall the sql 200
0 server due to a system problem now the sql 2000 server us up and when i tr
y to restore the database it gives me the above error. I hope some one out
there can help.
RayYou striped the backup over two backup files, and now you try to restore fro
m only one of those
backups files. You need to find that other backup file before you can restor
e.
(This is similar to having two disks in a raid 0, take one of those to anoth
er machine and expect to
get to the data on that machine... :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond C Prys" <Raymond C Prys@.discussions.microsoft.com> wrote in message
news:E9DA9319-A918-4DF5-94BB-C1E0F9B7B2B9@.microsoft.com...
> Attempting to do a data base recovery from backup and keep getting the following e
rror: " The
media set for database "isis" has 2 family members but only 1 are provided.
All members must be
provided."
> I backed up the database in sql 2000 server and had to reinstall the sql 2000 serv
er due to a
system problem now the sql 2000 server us up and when i try to restore the d
atabase it gives me the
above error. I hope some one out there can help.
> Ray
>

Database Backup for Log Shipping

Hi,
Does the Log Shipping Setup wizard provide any options to do the database
backup and restore after the initialization? what if the table structures
are changed and stored procedures are added? If not, does that mean I have
to start over the setup process again?
THanks
EdI am a little as to what you are asking as Log shipping is simply
Backup and Restore that is somewhat automated. If you add a sp then when the
backup gets applied to the other server it will create that sp there as
well. You might want to read the sections in BooksOnLIne under Log Shipping.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
> Hi,
> Does the Log Shipping Setup wizard provide any options to do the database
> backup and restore after the initialization? what if the table structures
> are changed and stored procedures are added? If not, does that mean I
> have
> to start over the setup process again?
> THanks
> Ed|||Sorry for the question.
I have the log shipping setup and everything works fine. However, the
current database structure needs to be changed, do I need to start over to
setup the log shipping again or I can just easily copy and restored the
altered database to the standby server?
"Andrew J. Kelly" wrote:

> I am a little as to what you are asking as Log shipping is simply
> Backup and Restore that is somewhat automated. If you add a sp then when t
he
> backup gets applied to the other server it will create that sp there as
> well. You might want to read the sections in BooksOnLIne under Log Shippin
g.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
>
>|||Hi
The log contains both DML and DDL events, so if you make a change to a SP,
or a table, or a row of data, on the source server, the change will be in th
e
log backup, and since you are using log shipping, the change will be applied
to the destination database automatically.
Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
how SQL Server works internally.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed" wrote:
> Sorry for the question.
> I have the log shipping setup and everything works fine. However, the
> current database structure needs to be changed, do I need to start over to
> setup the log shipping again or I can just easily copy and restored the
> altered database to the standby server?
> "Andrew J. Kelly" wrote:
>|||Yes, this is the case. It is something that I don't know...
How about Trasactional Replication? I don't think the transfered Reader Log
contains all the DML and DDL, am I right?
Ed
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The log contains both DML and DDL events, so if you make a change to a SP,
> or a table, or a row of data, on the source server, the change will be in
the
> log backup, and since you are using log shipping, the change will be appli
ed
> to the destination database automatically.
> Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
> how SQL Server works internally.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Ed" wrote:
>|||Replication and Log shipping are two different things. Replication may or
may not pass certain commands off to the subscriber but Log shipping will
make sure the other db is an exact duplicate of the first.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BC2E60AA-5D35-4A7A-B992-50E7B782A267@.microsoft.com...
> Yes, this is the case. It is something that I don't know...
> How about Trasactional Replication? I don't think the transfered Reader
> Log
> contains all the DML and DDL, am I right?
> Ed
> "Mike Epprecht (SQL MVP)" wrote:
>

Database Backup & Restore

Most of the rows in one of the tables in our database were deleted by a DTS
package. (The DTS package does a complete replacement of the table.
Unfortunately the source is a "work" file which had been cleared.) I ended
up restoring the last backup of the database with a different name and then
copied the data from the alternate DB. But I was wondering, is it possible
to restore a single table in a database from a full database backup or can
you only restore the entire database?
Not with the native Sql Server backup and restore commands. There are some
third party products who claim to have object level restore capabilities but
I have not tried any of them. It can be a very dangerous thing and must be
done considering all the elements so you maintain your data integrity.
Andrew J. Kelly SQL MVP
"Evan Nelson" <EvanNelson@.discussions.microsoft.com> wrote in message
news:BBEB73EC-AB2B-48F5-92D8-AAAF056EE80C@.microsoft.com...
> Most of the rows in one of the tables in our database were deleted by a
> DTS
> package. (The DTS package does a complete replacement of the table.
> Unfortunately the source is a "work" file which had been cleared.) I
> ended
> up restoring the last backup of the database with a different name and
> then
> copied the data from the alternate DB. But I was wondering, is it
> possible
> to restore a single table in a database from a full database backup or can
> you only restore the entire database?

database backup

I want to backup selected tables from a database and restore it . How
can i do it

--
Posted via http://dbforums.comThanks for the reply.

How can i use DTS to transefer selected database tables to same database
in another server?

--
Posted via http://dbforums.com|||darshini

Easiest way is to use the export wizard. Either start it from the
wizard tab in EM or right click database and start it from all tasks
option.

First screen is source server and database, second screen is target
server and database. Next screen choose copy tables. Next screen
select the tables you want. Last page decide when to run it. You can
run it immediately or schedule it later. It's usually a good idea to
also save it as a package in case you need to run it again.

Hope that helps

John

Database attach using .mdf and .ldf files

Hi all,
I have a problem with my sharepoint database. I lost access after i
installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
backup sets i've seen that backup files corrupted. I just have mdf and ldf
files of the database. I've searched on this page and also internet but
suggested solutions did not work for me. I tried sp_attach_db and
sp_attach_single_file_db queries but they did not work. I got the same errors
that others who having the same problem. Also i tried to create a database in
the same name and changed the database files with the old ones. Then set the
database in emergency mode and tried to set online again using alter database
command. It is still being executed for 48 hours but havent been
completed.This database is very important for me and i need to get my
database back. So, do you know any alternative methods to get my database
back, just using mdf and ldf files on the same server or on the different
server?Have you tried to use (DBCC) REBUILD_LOG ?
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"ALKIM" <ALKIM@.discussions.microsoft.com> wrote in message
news:9A7F69FE-C2CA-40EC-9E37-E8D2178AB3FF@.microsoft.com...
> Hi all,
> I have a problem with my sharepoint database. I lost access after i
> installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
> backup sets i've seen that backup files corrupted. I just have mdf and ldf
> files of the database. I've searched on this page and also internet but
> suggested solutions did not work for me. I tried sp_attach_db and
> sp_attach_single_file_db queries but they did not work. I got the same
> errors
> that others who having the same problem. Also i tried to create a database
> in
> the same name and changed the database files with the old ones. Then set
> the
> database in emergency mode and tried to set online again using alter
> database
> command. It is still being executed for 48 hours but havent been
> completed.This database is very important for me and i need to get my
> database back. So, do you know any alternative methods to get my database
> back, just using mdf and ldf files on the same server or on the different
> server?

Database attach using .mdf and .ldf files

Hi all,
I have a problem with my sharepoint database. I lost access after i
installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
backup sets i've seen that backup files corrupted. I just have mdf and ldf
files of the database. I've searched on this page and also internet but
suggested solutions did not work for me. I tried sp_attach_db and
sp_attach_single_file_db queries but they did not work. I got the same errors
that others who having the same problem. Also i tried to create a database in
the same name and changed the database files with the old ones. Then set the
database in emergency mode and tried to set online again using alter database
command. It is still being executed for 48 hours but havent been
completed.This database is very important for me and i need to get my
database back. So, do you know any alternative methods to get my database
back, just using mdf and ldf files on the same server or on the different
server?
Have you tried to use (DBCC) REBUILD_LOG ?
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"ALKIM" <ALKIM@.discussions.microsoft.com> wrote in message
news:9A7F69FE-C2CA-40EC-9E37-E8D2178AB3FF@.microsoft.com...
> Hi all,
> I have a problem with my sharepoint database. I lost access after i
> installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
> backup sets i've seen that backup files corrupted. I just have mdf and ldf
> files of the database. I've searched on this page and also internet but
> suggested solutions did not work for me. I tried sp_attach_db and
> sp_attach_single_file_db queries but they did not work. I got the same
> errors
> that others who having the same problem. Also i tried to create a database
> in
> the same name and changed the database files with the old ones. Then set
> the
> database in emergency mode and tried to set online again using alter
> database
> command. It is still being executed for 48 hours but havent been
> completed.This database is very important for me and i need to get my
> database back. So, do you know any alternative methods to get my database
> back, just using mdf and ldf files on the same server or on the different
> server?