Backup design

Hello, everyone:
I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?
Thanks
ZYTOriginally posted by yitongzhang
Hello, everyone:

I have a 1GB table that has to run in 24X7 environment. That means the users maybe use this table during backup. How should I design the backup strategy? Can users access the table when the backup is running?

Thanks

ZYT

yes

Backup and restore just part of the backup,

Hi All,
I got a question from my users. When I backup a Database from MS SQL I back
up the whole database. My question is. In case I want to restore just one or
two tables from the backup, it is possible.
Thanks in advance
RobertoNo, you can not natively restore tables from a backup file, your users might
be asking this to find out if you had to do a restore if you would have to
bring the entire database down. You typically would restore the backup to a
second test database and then transfer over the tables that you need from
the test to the production database.
If you use a third part tool like Lumigent Log Explorer I believe you could
selectively recover the tables but I've always had a question on how it
recovers the data held within those tables if time has elapsed to a point
where the previously referenced pages have been overwritten.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Julio Gonzalez" <jcquijada@.hotmail.com> wrote in message
news:e0sGl0$aDHA.2572@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I got a question from my users. When I backup a Database from MS SQL I
back
> up the whole database. My question is. In case I want to restore just one
or
> two tables from the backup, it is possible.
> Thanks in advance
> Roberto
>

Backup and availability

Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanks
Hi!
You will find a lot of info at
http://www.microsoft.com/technet/pro...ackuprest.mspx
and
http://www.microsoft.com/mspress/boo...hap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks

Backup and availability

Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
and
http://www.microsoft.com/mspress/books/sampchap/4297b.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks

Backup and availability

Technically what exactly does the backup do ?, and what can users/sysadmins
do in a database when a backup is in progress ?
Does it put table/page locks on data when it is backing up that specific
table/page or does it back up the actual mdf/ndf file ?.
How does this change with Full, differencial, filegroup and transaction
backups ?
When backing up a database I have read that it is not possible to run some
ALTER DATABASE commands, inserts and deletes etc, but i have also read that
backups maximize their rate of data transfer with minimal effect on
transaction throughput.
I am starting suspect that you are able to use DML statements and not DDLs
during a backup.
As long as Ive been a DBA i have always done Backups at times when there are
the least amount of users and when the server is not being utilised so much,
this make perfect sense as I'm sure theres an overhead.
Ive looked on the web and in BOL but cant find anything specific.
any knowledge or help on this would be appreciated
thanksHi!
You will find a lot of info at
http://www.microsoft.com/technet/pr...chap/4297b.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"piripi" <piripi@.discussions.microsoft.com> wrote in message
news:EA51C857-126E-4835-AF3F-C46DA996C439@.microsoft.com...
> Technically what exactly does the backup do ?, and what can
> users/sysadmins
> do in a database when a backup is in progress ?
> Does it put table/page locks on data when it is backing up that specific
> table/page or does it back up the actual mdf/ndf file ?.
> How does this change with Full, differencial, filegroup and transaction
> backups ?
> When backing up a database I have read that it is not possible to run some
> ALTER DATABASE commands, inserts and deletes etc, but i have also read
> that
> backups maximize their rate of data transfer with minimal effect on
> transaction throughput.
> I am starting suspect that you are able to use DML statements and not DDLs
> during a backup.
> As long as Ive been a DBA i have always done Backups at times when there
> are
> the least amount of users and when the server is not being utilised so
> much,
> this make perfect sense as I'm sure theres an overhead.
> Ive looked on the web and in BOL but cant find anything specific.
> any knowledge or help on this would be appreciated
> thanks

backing up/restoring a db

Hello,
I have a set of scripts, which create a database Test1,
create accounts/roles/users, and grant permissions for
those users to access the newly created database.
When I ran the scripts for the 1st time, everything worked
just fine. Test1 db was created and I could successfully
connect to it from my client application, add/remove
records, etc.
Then, I needed to move my db to a different server, so I
decided to backup the database, and then to restore on the
new server.
Here is the backup script I ran:
USE master
EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
BACKUP DATABASE Test1 TO Test1DB
The database backup is created properly (when I restored,
I saw all the original records).
The problem I am experiencing, though, is the messed up
accounts/logins, once the database is restored.
If I just run a restore script, my database is restored,
but unders 'security' for the SQL registration (in
Enterprise Manager) the login associated with my DB is
missing.
If I first run my scripts to create a brand new database,
and then restore the database from a backup, the logins
are created, but after the restore script is done, the
association with DB seems to be broken: if I double-click
on my login under 'security' and look at the list of dbs
in 'database access', my Test1 db doesn't have a check
mark next to it (even though it was there before I ran
restore script), if I try to set the check, I get an error
21002, 'User already 'so-and-so' already exist'.
What I am doing wrong? Is there any way to backup a db in
such way so that logins could be also restored? Or is
there a way to restore a db, so that existing
accounts/permissions would not be affected, but only the
data be merged in?
Thanks a lot for any help/suggestions.
Sincerely,
VRHi
backing or restoring a user database does not create
logins ,the logins hv to pre-exist or u hv to create
logins before creating/restoring the user db. bcos logins
exists inside master db and not user db
but when u exec scripts which include the logins surely
they get created.
thanks
rahul
>--Original Message--
>Hello,
>I have a set of scripts, which create a database Test1,
>create accounts/roles/users, and grant permissions for
>those users to access the newly created database.
>When I ran the scripts for the 1st time, everything
worked
>just fine. Test1 db was created and I could successfully
>connect to it from my client application, add/remove
>records, etc.
>Then, I needed to move my db to a different server, so I
>decided to backup the database, and then to restore on
the
>new server.
>Here is the backup script I ran:
>USE master
>EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
>BACKUP DATABASE Test1 TO Test1DB
>The database backup is created properly (when I
restored,
>I saw all the original records).
>The problem I am experiencing, though, is the messed up
>accounts/logins, once the database is restored.
>If I just run a restore script, my database is restored,
>but unders 'security' for the SQL registration (in
>Enterprise Manager) the login associated with my DB is
>missing.
>If I first run my scripts to create a brand new
database,
>and then restore the database from a backup, the logins
>are created, but after the restore script is done, the
>association with DB seems to be broken: if I double-
click
>on my login under 'security' and look at the list of dbs
>in 'database access', my Test1 db doesn't have a check
>mark next to it (even though it was there before I ran
>restore script), if I try to set the check, I get an
error
>21002, 'User already 'so-and-so' already exist'.
>What I am doing wrong? Is there any way to backup a db
in
>such way so that logins could be also restored? Or is
>there a way to restore a db, so that existing
>accounts/permissions would not be affected, but only the
>data be merged in?
>Thanks a lot for any help/suggestions.
>Sincerely,
>VR
>
>.
>|||Check out sp_change_users_login in the Books Online
<tsqlref.chm::/ts_sp_ca-cz_8qzy.htm>. This will report and/or correct
mismatches between users and logins due to a restore/attach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VR" <anonymous@.discussions.microsoft.com> wrote in message
news:033601c3cb79$a2806270$a101280a@.phx.gbl...
> Hello,
> I have a set of scripts, which create a database Test1,
> create accounts/roles/users, and grant permissions for
> those users to access the newly created database.
> When I ran the scripts for the 1st time, everything worked
> just fine. Test1 db was created and I could successfully
> connect to it from my client application, add/remove
> records, etc.
> Then, I needed to move my db to a different server, so I
> decided to backup the database, and then to restore on the
> new server.
> Here is the backup script I ran:
> USE master
> EXEC sp_adddumpdevice 'disk', 'Test1DB', 'Test1db.dat'
> BACKUP DATABASE Test1 TO Test1DB
> The database backup is created properly (when I restored,
> I saw all the original records).
> The problem I am experiencing, though, is the messed up
> accounts/logins, once the database is restored.
> If I just run a restore script, my database is restored,
> but unders 'security' for the SQL registration (in
> Enterprise Manager) the login associated with my DB is
> missing.
> If I first run my scripts to create a brand new database,
> and then restore the database from a backup, the logins
> are created, but after the restore script is done, the
> association with DB seems to be broken: if I double-click
> on my login under 'security' and look at the list of dbs
> in 'database access', my Test1 db doesn't have a check
> mark next to it (even though it was there before I ran
> restore script), if I try to set the check, I get an error
> 21002, 'User already 'so-and-so' already exist'.
> What I am doing wrong? Is there any way to backup a db in
> such way so that logins could be also restored? Or is
> there a way to restore a db, so that existing
> accounts/permissions would not be affected, but only the
> data be merged in?
> Thanks a lot for any help/suggestions.
> Sincerely,
> VR
>
>