Hi All,
Does anyone know how to write an xp_ Stored Procedure or
dll to make to normal sql backup run faster.
Thanks and please point me to the right direction or
source for this.
tony
trying to issue a SQL Server backup thru a self created DLL will most likely
take longer and have more overhead. I would suggest you look at SQL
LiteSpeed from http://www.imceda.com/ to speed up your backups. They will
also be much smaller as well.
Andrew J. Kelly SQL MVP
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony
|||You can also use more than one backup device - this may help your
situation. You can find some other suggestions and things to monitor
for bottlenecks in the books online topic:
Optimizing Backup and Restore Performance
-Sue
On Mon, 13 Sep 2004 10:53:00 -0700, "tony"
<anonymous@.discussions.microsoft.com> wrote:
>Hi All,
>Does anyone know how to write an xp_ Stored Procedure or
>dll to make to normal sql backup run faster.
>Thanks and please point me to the right direction or
>source for this.
>tony
|||Have a look at our article at http://www.yohz.com/articles_01.html. Amongst
other things, it describes how Microsoft provides a standard interface for
third party software vendors to implement their own backup/restore solution.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Sunday, March 25, 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?
>
>.
>
Monday, March 19, 2012
Database Audit
Hi,
We are currently porting our POINT OF SALE system to SQL Server 2005. In our existing software we log database changes (inserts, amendments and deletions) to a flat acsii files so that these files can be FTP'd to remote sites (largest number of sites 100+) and processed on to replicate the data.
I have looked at REPLICATION in MSSQL 2005 but we need two way replication and additional processing. Therefore I dont think we will be able to use this feature.
Therefore what I wanted to do was to try and setup an automated way of capturing database changes to all tables within the database and log these changes to XML to be shipped out to remote sites. Unfortunatly I can only find TABLE TRIGGERS which would require creating 100's of triggers as we have 100's of tables.
Is there anyway of setting up MSSQL server to automatically do something like this... I was looking to see if there was a DATABASE TRIGGER which could perform this action but I cant see anything...
Can anyone advise or is there a simpler way of doing this ?
Ray
I think that Log Shipping may be a useful solution in your situation.
Refer to Books Online, and the topic: Log Shipping.
Saturday, February 25, 2012
Data Viewers not viewable
I came back from the weekend and noticed that none of my data viewers pop-up any longer, neither existing nor new ones. The job pauses at the point where the data would be following through the viewer but I cannot make it continue (since I don't have a "play" or Detach button.) The only way to terminate the job is with the "Stop" button on the Toolbar. Anyone know how I can get these back?
Thanks.Never mind, it was pulled down to the very bottom of my second screen, almost out of sight. I swear...
Subscribe to:
Comments (Atom)