Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?
Look at http://www.winnetmag.com/Article/Art...09/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.co m...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?
|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Look at http://www.winnetmag.com/Article/Art...09/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.co m...

Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?Look at http://www.winnetmag.com/Article/ArticleID/42009/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.com...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...
> Look at http://www.winnetmag.com/Article/ArticleID/42009/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.com...
> > Any one have implemented backup, recovery stored procedures which
> > takes the uments for dbname, date, time ime and restores till that
> > time?

Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?Look at http://www.winnetmag.com/Article/Ar...009/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.com...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...[
vbcol=seagreen]
> Look at http://www.winnetmag.com/Article/Ar...009/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.com...

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> T-SQL scheduled using SQL Server Agent Service
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.
> >
> > Plus please post some links to backup command scripts pages.
> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> >
> > Thanks
> > BVR
> >
> >
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> >
> > Plus please post some links to backup command scripts pages.
> >
> > Thanks
> > BVR
> >
> >
>

Backup / restore publisher db

Hello all -
We have a published db which we backup daily. In order to restore the
database, we run a scripts which removes all subscribers, restore the db,
and then run another script to add all subscribers. We then have to re-init
all. ( The remove / create scripts are generated by EM ).
Is there a more efficent way to do this? Can we take the db offline and
then do the restore & reinit?
Note that we aren't so concerned with preserving the state of the clients
and accept having to do the reinit.
Your thoughts & comments are appreciated.
Regards,
bruce
Bruce,
you could investigate the use of the KEEP_REPLICATION flag in RESTORE.
In my experience it usually it won't fully work and especially not when the
computers name is different therefore I script back the replication settings
and reinitialize.
Regards,
Paul Ibison

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
>
>

Backing up tables

I use SQL Server 2000 and I create scripts on a daily basis to do the following

1. Rename a table
2. Create table with original name and add new field
3. Copy data from renamed table to new table
4. Build indexes
-------------
execute sp_rename ORIGINAL, OLD
GO

CREATE TABLE [dbo].[ORIGINAL] (
[A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C] [int] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO ORIGINAL (A, B, NOTES)
SELECT A, B, NOTES
FROM OLD

CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-------------

ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART

I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS
DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?

IF SO,
DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?

Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.

Thanks,
MitchWow sounds like he has a DB2 OS/390 v2.3 background

Look up the datatype in BOL...

They store pointer values, not the data...it's stored separatley...

If you don't mind being fired, call him a scrub...

The order of columns in a table should have very little impact...

But being anal goes with the job...

Try a bcp out and in with a format file...

The reason it's slow is because it's all being logged...the bcp will log only the pages...not every row....|||EDIT:

Oh, and btw, I use ALTER