I need to detect whether or not a database is being replicated. I need to do
this in a script and use the results in an IF statement. Is the best way to
do this by checking the value of category in the sysdatabases table?
Something like this …
IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
category = 0)
BEGIN
PRINT 'Not replicating'
END
ELSE
BEGIN
PRINT 'Replicating'
END
Thanks,
Mark
You could do this using databasepropertyex:
select name, databasepropertyex (name,'IsMergePublished') from
master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from
master..sysdatabases
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You could also try
sp_helpreplicationdboption and observe the values of transpublish and
mergepublish
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:36A25480-B64D-4D67-914C-F85A6D0EFA8A@.microsoft.com...
>I need to detect whether or not a database is being replicated. I need to
>do
> this in a script and use the results in an IF statement. Is the best way
> to
> do this by checking the value of category in the sysdatabases table?
> Something like this .
> IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
> category = 0)
> BEGIN
> PRINT 'Not replicating'
> END
> ELSE
> BEGIN
> PRINT 'Replicating'
> END
> Thanks,
> Mark
|||Select name,category from sysdatabases (For SQL Server 2000)
See if it is replicate by
looking at these values
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Select * from sys.datbases
and look for is_published,is_subscribed etc
thankx
"mrprice" wrote:
> I need to detect whether or not a database is being replicated. I need to do
> this in a script and use the results in an IF statement. Is the best way to
> do this by checking the value of category in the sysdatabases table?
> Something like this …
> IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
> category = 0)
> BEGIN
> PRINT 'Not replicating'
> END
> ELSE
> BEGIN
> PRINT 'Replicating'
> END
> Thanks,
> Mark
sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
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 '
>
>.
>
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 '
>
>.
>
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]
>
>.
>
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?
>
>.
>
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?
>
>.
>
Wednesday, March 7, 2012
database (read-only)
I attached a database with sp_attach_db but it ended being read-only. Is
there a script that can change my database from being read-only?ALTER DATABASE pubs SET READ_WRITE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?|||ALTER DATABASE dbname SET READ_WRITE
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
there a script that can change my database from being read-only?ALTER DATABASE pubs SET READ_WRITE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?|||ALTER DATABASE dbname SET READ_WRITE
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
database (read-only)
I attached a database with sp_attach_db but it ended being read-only. Is
there a script that can change my database from being read-only?
ALTER DATABASE pubs SET READ_WRITE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
|||ALTER DATABASE dbname SET READ_WRITE
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
there a script that can change my database from being read-only?
ALTER DATABASE pubs SET READ_WRITE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
|||ALTER DATABASE dbname SET READ_WRITE
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
database (read-only)
I attached a database with sp_attach_db but it ended being read-only. Is
there a script that can change my database from being read-only?ALTER DATABASE pubs SET READ_WRITE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?|||ALTER DATABASE dbname SET READ_WRITE
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
there a script that can change my database from being read-only?ALTER DATABASE pubs SET READ_WRITE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?|||ALTER DATABASE dbname SET READ_WRITE
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:7841BD45-E305-49A7-8037-C821B891C799@.microsoft.com...
>I attached a database with sp_attach_db but it ended being read-only. Is
> there a script that can change my database from being read-only?
databas size
I am looking for script to give the Data and log files of each Database on
the server including the size of each file.
can you help<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
SQL Server 2005:
SELECT * FROM sys.master_files;
SQL Server 2000:
SELECT * FROM master.dbo.sysaltfiles;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>|||Hi
http://dimantdatabasesolutions.blog...
se.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
the server including the size of each file.
can you help<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
SQL Server 2005:
SELECT * FROM sys.master_files;
SQL Server 2000:
SELECT * FROM master.dbo.sysaltfiles;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>|||Hi
http://dimantdatabasesolutions.blog...
se.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
databas size
I am looking for script to give the Data and log files of each Database on
the server including the size of each file.
can you help
You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
the server including the size of each file.
can you help
You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
databas size
I am looking for script to give the Data and log files of each Database on
the server including the size of each file.
can you help<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
SQL Server 2005:
SELECT * FROM sys.master_files;
SQL Server 2000:
SELECT * FROM master.dbo.sysaltfiles;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
the server including the size of each file.
can you help<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
SQL Server 2005:
SELECT * FROM sys.master_files;
SQL Server 2000:
SELECT * FROM master.dbo.sysaltfiles;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can also use the below system proc.
sp_MSforeachdb 'sp_helpdb ?'
Thanks
Hari
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
<msnews.microsoft.com> wrote in message
news:e$$FnV8dHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I am looking for script to give the Data and log files of each Database on
>the server including the size of each file.
> can you help
>
Tuesday, February 14, 2012
Data Transformation with ActiveX
Hello,
I am importing data from a textfile of 1,5 GB to my Sql-server and I use activeX for the transformation. I need ActiveX script because I have to change some formats etc.
But, I think it is very slow and I want it to speed up.
Do you have some hints to speed up the transformation?
Thx for the advice!
TomInstead of having multiple Activex scripts , make it single script
I am importing data from a textfile of 1,5 GB to my Sql-server and I use activeX for the transformation. I need ActiveX script because I have to change some formats etc.
But, I think it is very slow and I want it to speed up.
Do you have some hints to speed up the transformation?
Thx for the advice!
TomInstead of having multiple Activex scripts , make it single script
Subscribe to:
Posts (Atom)