Database Unit Testing: restores

Showing posts with label restores. Show all posts

Sunday, March 11, 2012

Database Restores; Terminates Abnormally

Hi to All!
i have data base restore problem. i have backed up data base with verify
the backup option last w

now i have server crash and i have two
recent backups with me. When i restore the latest one it terminates
abnormally. when i restore the other backup it restores okay. what may
be the problem in restoring the latest backup or which optin i shuould
select in the options to restore the data sucessfully.
Thanx
*** Sent via Developersdex http://www.examnotes.net ***Hi Ghulam,
What error do you get when the restore fails ?
"Ghulam Farid" wrote:

> Hi to All!
> i have data base restore problem. i have backed up data base with verify
> the backup option last w

now i have server crash and i have two
> recent backups with me. When i restore the latest one it terminates
> abnormally. when i restore the other backup it restores okay. what may
> be the problem in restoring the latest backup or which optin i shuould
> select in the options to restore the data sucessfully.
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Database restores using Enterprise Manager

Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.

Chris

That the way, restoring the database files one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply, Jens.

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
John
Hi
Does this help?
http://msdn.microsoft.com/library/de...ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql serve
r
fill one datafile first before starting on the second, or do both at the sam
e
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/d...br />
49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

Database Restores

I took some database backups from a client site to do some development.
There were 20G, 16G and 200MB

i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.

went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?I have to bring 2 or 3 client databases in house a week. Our largest client db is only about 70 gb but here is my restore template. Never fails.

--STEP 1. Create the Database
CREATE DATABASE MyDatabase

--STEP 2. Get the physical file names
sp_helpdb MyDatabase

--STEP 3. Get the logical file names
RESTORE FILELISTONLY
FROM DISK = 'X:\MyBackup.BAK'

--STEP.4 RUN THE RECOVERY
ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDatabase
FROM DISK = 'X:\MyBackup.BAK'
WITH REPLACE,RECOVERY,STATS=10,
MOVE 'Logical_Data' TO 'D:\physical.mdf' ,
MOVE 'Logical_Log' TO 'E:\physiical.LDF'
GO
ALTER DATABASE MyDatabase SET MULTI_USER
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
USE MyDatabase
GO
DBCC SHRINKFILE(Logical_Log,1)
GO
USE MASTER
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE|||That's the sort of thing you should post for reference on sqlkit, Thrasy.|||I know. I am getting some stuff together. Sample code... I have an article started. I will probably start posting this weekend. I am looking forward to some biting criticism. Why don't you post your CSVtoINT thing and don't you have a first name \ last name from one field thing too. I just have a full version release coming plus customer projects by the end of the month and I am woefully behind.|||I have noticed a remarkable delay from issuing the restore command to the first status tick being reported. Esecially on large databases. It could be that SQL Server is verifying the backup before beginning the restore, but that would be a question for Microsoft.

Thursday, March 8, 2012

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JB
If the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DA
TABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took
backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part.
It will just add back
the pages to the same location as they had when you performed the backup. (I
t is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an er
ror from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same,
then SQL Server will
not be able to put back all pages in the same file and page address as they
had when the backup was
performed. In this case, you can use the REPLACE option, which essentially m
eans that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

Database Restore on a Linked Server

I have a log shipping mechanism which backs up database on the primary server
and then restores the database on a standby server, the database is restored
with the help of a stored procedure on the standby server executed from the
primary server, After this the tran log is restored on the standby server, I
get the following error when i execute the stored procedure for restoring the
tran log
File 'YRS_TMP_Data' was only partially restored by a database or file
restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup, Line 5
RESTORE LOG is terminating abnormally.
Thanks
RodgerAre you sure your full and/or differential backups where successfully
restored on your standby server?
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> I have a log shipping mechanism which backs up database on the primary
server
> and then restores the database on a standby server, the database is
restored
> with the help of a stored procedure on the standby server executed from
the
> primary server, After this the tran log is restored on the standby server,
I
> get the following error when i execute the stored procedure for restoring
the
> tran log
> File 'YRS_TMP_Data' was only partially restored by a database or file
> restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup,
Line 5
> RESTORE LOG is terminating abnormally.
> Thanks
> Rodger|||Thanks greg
here is the command i execute to restore my initial database on the standby
server
and the error which i get, it is related to the linked server entry, after
which i try the second query for restoring my log.
exec yrswebnyc.master.dbo.restore_YRS_backup
go
[OLE/DB provider returned message: Timeout expired]
"Gregory A. Larsen" wrote:
> Are you sure your full and/or differential backups where successfully
> restored on your standby server?
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > I have a log shipping mechanism which backs up database on the primary
> server
> > and then restores the database on a standby server, the database is
> restored
> > with the help of a stored procedure on the standby server executed from
> the
> > primary server, After this the tran log is restored on the standby server,
> I
> > get the following error when i execute the stored procedure for restoring
> the
> > tran log
> >
> > File 'YRS_TMP_Data' was only partially restored by a database or file
> > restore.
> > The entire file must be successfully restored before applying the log.
> > Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup,
> Line 5
> > RESTORE LOG is terminating abnormally.
> >
> > Thanks
> >
> > Rodger
>
>|||I'm guessing that your initial restore never happened, since you gota
timeout. Did you review the errorlog file on the yrswebnyc server to verify
that your linked server request did/didn't run your SP.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> Thanks greg
> here is the command i execute to restore my initial database on the
standby
> server
> and the error which i get, it is related to the linked server entry, after
> which i try the second query for restoring my log.
> exec yrswebnyc.master.dbo.restore_YRS_backup
> go
> [OLE/DB provider returned message: Timeout expired]
>
> "Gregory A. Larsen" wrote:
> > Are you sure your full and/or differential backups where successfully
> > restored on your standby server?
> >
> > --
> >
> ----
--
> ----
--
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > > I have a log shipping mechanism which backs up database on the primary
> > server
> > > and then restores the database on a standby server, the database is
> > restored
> > > with the help of a stored procedure on the standby server executed
from
> > the
> > > primary server, After this the tran log is restored on the standby
server,
> > I
> > > get the following error when i execute the stored procedure for
restoring
> > the
> > > tran log
> > >
> > > File 'YRS_TMP_Data' was only partially restored by a database or file
> > > restore.
> > > The entire file must be successfully restored before applying the log.
> > > Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup,
> > Line 5
> > > RESTORE LOG is terminating abnormally.
> > >
> > > Thanks
> > >
> > > Rodger
> >
> >
> >|||Yes my original restore never happened it gives me the error after 10 min
[OLE/DB provider returned message: Timeout expired]
I execute the stored proc from primary server which restores database on
standby server, but i receive the error above, i am not sure if i have to
make changes in my linked server settings, maybe connection timeout or query
timeout. any sugesstion
"Gregory A. Larsen" wrote:
> I'm guessing that your initial restore never happened, since you gota
> timeout. Did you review the errorlog file on the yrswebnyc server to verify
> that your linked server request did/didn't run your SP.
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> >
> > Thanks greg
> >
> > here is the command i execute to restore my initial database on the
> standby
> > server
> > and the error which i get, it is related to the linked server entry, after
> > which i try the second query for restoring my log.
> >
> > exec yrswebnyc.master.dbo.restore_YRS_backup
> > go
> >
> > [OLE/DB provider returned message: Timeout expired]
> >
> >
> > "Gregory A. Larsen" wrote:
> >
> > > Are you sure your full and/or differential backups where successfully
> > > restored on your standby server?
> > >
> > > --
> > >
> >
> > ----
> --
> >
> > ----
> --
> > > -
> > >
> > > Need SQL Server Examples check out my website
> > > http://www.geocities.com/sqlserverexamples
> > >
> > >
> > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > > > I have a log shipping mechanism which backs up database on the primary
> > > server
> > > > and then restores the database on a standby server, the database is
> > > restored
> > > > with the help of a stored procedure on the standby server executed
> from
> > > the
> > > > primary server, After this the tran log is restored on the standby
> server,
> > > I
> > > > get the following error when i execute the stored procedure for
> restoring
> > > the
> > > > tran log
> > > >
> > > > File 'YRS_TMP_Data' was only partially restored by a database or file
> > > > restore.
> > > > The entire file must be successfully restored before applying the log.
> > > > Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup,
> > > Line 5
> > > > RESTORE LOG is terminating abnormally.
> > > >
> > > > Thanks
> > > >
> > > > Rodger
> > >
> > >
> > >
>
>|||Are you able to do simple "SELECT" statements via your linked server? I
would verify that your linked server definition is in fact working.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:BC1B0F2A-21C9-46C1-A22B-B067030BADB7@.microsoft.com...
> Yes my original restore never happened it gives me the error after 10 min
> [OLE/DB provider returned message: Timeout expired]
> I execute the stored proc from primary server which restores database on
> standby server, but i receive the error above, i am not sure if i have to
> make changes in my linked server settings, maybe connection timeout or
query
> timeout. any sugesstion
> "Gregory A. Larsen" wrote:
> > I'm guessing that your initial restore never happened, since you gota
> > timeout. Did you review the errorlog file on the yrswebnyc server to
verify
> > that your linked server request did/didn't run your SP.
> >
> > --
> >
> ----
--
> ----
--
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> > >
> > > Thanks greg
> > >
> > > here is the command i execute to restore my initial database on the
> > standby
> > > server
> > > and the error which i get, it is related to the linked server entry,
after
> > > which i try the second query for restoring my log.
> > >
> > > exec yrswebnyc.master.dbo.restore_YRS_backup
> > > go
> > >
> > > [OLE/DB provider returned message: Timeout expired]
> > >
> > >
> > > "Gregory A. Larsen" wrote:
> > >
> > > > Are you sure your full and/or differential backups where
successfully
> > > > restored on your standby server?
> > > >
> > > > --
> > > >
> > >
> >
> ----
> > --
> > >
> >
> ----
> > --
> > > > -
> > > >
> > > > Need SQL Server Examples check out my website
> > > > http://www.geocities.com/sqlserverexamples
> > > >
> > > >
> > > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > > news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > > > > I have a log shipping mechanism which backs up database on the
primary
> > > > server
> > > > > and then restores the database on a standby server, the database
is
> > > > restored
> > > > > with the help of a stored procedure on the standby server executed
> > from
> > > > the
> > > > > primary server, After this the tran log is restored on the standby
> > server,
> > > > I
> > > > > get the following error when i execute the stored procedure for
> > restoring
> > > > the
> > > > > tran log
> > > > >
> > > > > File 'YRS_TMP_Data' was only partially restored by a database or
file
> > > > > restore.
> > > > > The entire file must be successfully restored before applying the
log.
> > > > > Server: Msg 3013, Level 16, State 1, Procedure
restore_YRS_Log_backup,
> > > > Line 5
> > > > > RESTORE LOG is terminating abnormally.
> > > > >
> > > > > Thanks
> > > > >
> > > > > Rodger
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Gregory
The select works but the stored proc fails after running for 8 min, here is
the stored proc script and the command which i execute from standby is
exec yrswebnyc.master.dbo.restore_YRS_backup
and the error message i get when i execute the command from primary server
query analyzer is
[OLE/DB provider returned message: Timeout expired]
CREATE PROCEDURE restore_YRS_backup
AS
RESTORE DATABASE YRS
FROM yrs_prod2
WITH
DBO_ONLY,
REPLACE,
--STANDBY = 'F:\undo_yrs.ldf',
MOVE 'yrs_tmp_data' TO 'Y:\yrs.mdf',
MOVE 'yrs_tmp_log' TO 'E:\yrs_log.ldf'
WAITFOR DELAY '00:00:05'
GO
"Gregory A. Larsen" wrote:
> Are you able to do simple "SELECT" statements via your linked server? I
> would verify that your linked server definition is in fact working.
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:BC1B0F2A-21C9-46C1-A22B-B067030BADB7@.microsoft.com...
> > Yes my original restore never happened it gives me the error after 10 min
> > [OLE/DB provider returned message: Timeout expired]
> >
> > I execute the stored proc from primary server which restores database on
> > standby server, but i receive the error above, i am not sure if i have to
> > make changes in my linked server settings, maybe connection timeout or
> query
> > timeout. any sugesstion
> >
> > "Gregory A. Larsen" wrote:
> >
> > > I'm guessing that your initial restore never happened, since you gota
> > > timeout. Did you review the errorlog file on the yrswebnyc server to
> verify
> > > that your linked server request did/didn't run your SP.
> > >
> > > --
> > >
> >
> > ----
> --
> >
> > ----
> --
> > > -
> > >
> > > Need SQL Server Examples check out my website
> > > http://www.geocities.com/sqlserverexamples
> > >
> > >
> > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> > > >
> > > > Thanks greg
> > > >
> > > > here is the command i execute to restore my initial database on the
> > > standby
> > > > server
> > > > and the error which i get, it is related to the linked server entry,
> after
> > > > which i try the second query for restoring my log.
> > > >
> > > > exec yrswebnyc.master.dbo.restore_YRS_backup
> > > > go
> > > >
> > > > [OLE/DB provider returned message: Timeout expired]
> > > >
> > > >
> > > > "Gregory A. Larsen" wrote:
> > > >
> > > > > Are you sure your full and/or differential backups where
> successfully
> > > > > restored on your standby server?
> > > > >
> > > > > --
> > > > >
> > > >
> > >
> > ----
> > > --
> > > >
> > >
> > ----
> > > --
> > > > > -
> > > > >
> > > > > Need SQL Server Examples check out my website
> > > > > http://www.geocities.com/sqlserverexamples
> > > > >
> > > > >
> > > > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > > > news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > > > > > I have a log shipping mechanism which backs up database on the
> primary
> > > > > server
> > > > > > and then restores the database on a standby server, the database
> is
> > > > > restored
> > > > > > with the help of a stored procedure on the standby server executed
> > > from
> > > > > the
> > > > > > primary server, After this the tran log is restored on the standby
> > > server,
> > > > > I
> > > > > > get the following error when i execute the stored procedure for
> > > restoring
> > > > > the
> > > > > > tran log
> > > > > >
> > > > > > File 'YRS_TMP_Data' was only partially restored by a database or
> file
> > > > > > restore.
> > > > > > The entire file must be successfully restored before applying the
> log.
> > > > > > Server: Msg 3013, Level 16, State 1, Procedure
> restore_YRS_Log_backup,
> > > > > Line 5
> > > > > > RESTORE LOG is terminating abnormally.
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Rodger
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Perhaps you are just experiencing a timeout, i.e. the restore takes longer time than the time out
period for a query on a remote server? I believe that there are some timeout settings for linked
servers in sp_configure and possibly also for the linked server setup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:C81B7DFA-5B19-416A-806F-64729C030387@.microsoft.com...
> Hi Gregory
> The select works but the stored proc fails after running for 8 min, here is
> the stored proc script and the command which i execute from standby is
> exec yrswebnyc.master.dbo.restore_YRS_backup
> and the error message i get when i execute the command from primary server
> query analyzer is
> [OLE/DB provider returned message: Timeout expired]
>
>
> CREATE PROCEDURE restore_YRS_backup
> AS
> RESTORE DATABASE YRS
> FROM yrs_prod2
> WITH
> DBO_ONLY,
> REPLACE,
> --STANDBY = 'F:\undo_yrs.ldf',
> MOVE 'yrs_tmp_data' TO 'Y:\yrs.mdf',
> MOVE 'yrs_tmp_log' TO 'E:\yrs_log.ldf'
> WAITFOR DELAY '00:00:05'
> GO
> "Gregory A. Larsen" wrote:
> > Are you able to do simple "SELECT" statements via your linked server? I
> > would verify that your linked server definition is in fact working.
> >
> > --
> >
> > ----
> > ----
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > news:BC1B0F2A-21C9-46C1-A22B-B067030BADB7@.microsoft.com...
> > > Yes my original restore never happened it gives me the error after 10 min
> > > [OLE/DB provider returned message: Timeout expired]
> > >
> > > I execute the stored proc from primary server which restores database on
> > > standby server, but i receive the error above, i am not sure if i have to
> > > make changes in my linked server settings, maybe connection timeout or
> > query
> > > timeout. any sugesstion
> > >
> > > "Gregory A. Larsen" wrote:
> > >
> > > > I'm guessing that your initial restore never happened, since you gota
> > > > timeout. Did you review the errorlog file on the yrswebnyc server to
> > verify
> > > > that your linked server request did/didn't run your SP.
> > > >
> > > > --
> > > >
> > >
> > > ----
> > --
> > >
> > > ----
> > --
> > > > -
> > > >
> > > > Need SQL Server Examples check out my website
> > > > http://www.geocities.com/sqlserverexamples
> > > >
> > > >
> > > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > > news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> > > > >
> > > > > Thanks greg
> > > > >
> > > > > here is the command i execute to restore my initial database on the
> > > > standby
> > > > > server
> > > > > and the error which i get, it is related to the linked server entry,
> > after
> > > > > which i try the second query for restoring my log.
> > > > >
> > > > > exec yrswebnyc.master.dbo.restore_YRS_backup
> > > > > go
> > > > >
> > > > > [OLE/DB provider returned message: Timeout expired]
> > > > >
> > > > >
> > > > > "Gregory A. Larsen" wrote:
> > > > >
> > > > > > Are you sure your full and/or differential backups where
> > successfully
> > > > > > restored on your standby server?
> > > > > >
> > > > > > --
> > > > > >
> > > > >
> > > >
> > > ----
> > > > --
> > > > >
> > > >
> > > ----
> > > > --
> > > > > > -
> > > > > >
> > > > > > Need SQL Server Examples check out my website
> > > > > > http://www.geocities.com/sqlserverexamples
> > > > > >
> > > > > >
> > > > > > "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> > > > > > news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> > > > > > > I have a log shipping mechanism which backs up database on the
> > primary
> > > > > > server
> > > > > > > and then restores the database on a standby server, the database
> > is
> > > > > > restored
> > > > > > > with the help of a stored procedure on the standby server executed
> > > > from
> > > > > > the
> > > > > > > primary server, After this the tran log is restored on the standby
> > > > server,
> > > > > > I
> > > > > > > get the following error when i execute the stored procedure for
> > > > restoring
> > > > > > the
> > > > > > > tran log
> > > > > > >
> > > > > > > File 'YRS_TMP_Data' was only partially restored by a database or
> > file
> > > > > > > restore.
> > > > > > > The entire file must be successfully restored before applying the
> > log.
> > > > > > > Server: Msg 3013, Level 16, State 1, Procedure
> > restore_YRS_Log_backup,
> > > > > > Line 5
> > > > > > > RESTORE LOG is terminating abnormally.
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > Rodger
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Sunday, March 11, 2012

Database Restores; Terminates Abnormally

Database restores using Enterprise Manager

database restores - what actually happens?

database restores - what actually happens?

database restores - what actually happens?

Database Restores

Thursday, March 8, 2012

database restore takes ages....

database restore takes ages....

database restore takes ages....

Database Restore on a Linked Server

Database Restore on a Linked Server

Database Unit Testing

Blog Archive

About Me