Hi,
I am very new to Microsoft Technologies, infact new to database world
:)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
restore this backup by using SQL-Server 2000Enterprise edition, how
should i go about it?
Any help or comments will be highly appreciated.
-ErlandEasiest in the login run is to read about the RESTORE command in Books Onlin
e. After you understand
how that RESTORE command work, you will find it natural how to work the GUI
dialog in Enterprise
Manager (if you chose to use it instead of executing the RESTORE command in
Query Analyzer).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132234090.437642.38470@.f14g2000cwb.googlegroups.com...
> Hi,
> I am very new to Microsoft Technologies, infact new to database world
> :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
> restore this backup by using SQL-Server 2000Enterprise edition, how
> should i go about it?
> Any help or comments will be highly appreciated.
> -Erland
>|||Tibor Karaszi wrote:
> Easiest in the login run is to read about the RESTORE command in Books Onl
ine. After you understand
> how that RESTORE command work, you will find it natural how to work the GU
I dialog in Enterprise
> Manager (if you chose to use it instead of executing the RESTORE command i
n Query Analyzer).
>
osql
restore database blah from disk = 'c:\blah.dat'
something like that
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Wednesday, March 28, 2012
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
Restoring using smo
I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):
db1 = New Management.Smo.Database
db1.Name = Me.lstDatabase.SelectedItem
restore = New Management.Smo.Restore
restore.Action = Management.Smo.RestoreActionType.Database
restore.Database = db1.Name
backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)
restore.Devices.Add(backDeviceItem)
restore.ReplaceDatabase = True
restore.SqlRestore(SqlServerSelection)
In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?
Thanks...
Scott
What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restrictedsql
Restoring using smo
I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):
db1 = New Management.Smo.Database
db1.Name = Me.lstDatabase.SelectedItem
restore = New Management.Smo.Restore
restore.Action = Management.Smo.RestoreActionType.Database
restore.Database = db1.Name
backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)
restore.Devices.Add(backDeviceItem)
restore.ReplaceDatabase = True
restore.SqlRestore(SqlServerSelection)
In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?
Thanks...
Scott
What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restricted
Restoring Transaction Logs
I have a customer that needs to restore a database to
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack
Restoring Tran. Log files
I need to restore a database on my test server from that on my production
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:
> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,
> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:
> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,
> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>
Restoring to remove standy/read-only mode.
Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>
Restoring to New server
I have created a new server running sql server 6.5 and I am trying to restore a databasefrm an existing 6.5 server to this new server . I got the master on but I cannot get any of the databases on. First the database was going in suspend mode, then after I resolved that I am getting size errors that I cannot seem to resolve even with expanding the devices.
What I'm I doing wrong? Did I miss a step? or is there a problem with the server setup?
Ans..Q1 What I'm I doing wrong?
A1 Maybe nothing (insufficient information).
Was the master backup used in the restore to the second server in fact current (assuming the restores (DB Loads) were performed to similar devices on the new server - using similar device orders, sizes and types, as those of the DB devices as they existed on the original server)? If not, some device data may be incorrect (most likely, incomplete). In that case it may be possible to use Disk ReInit for each device on the new server as necessary to address the issue (using the same logical and physical device names is easier if that is the case; however the 2k block size value entered for the size parameter must be the same as the production user devices at the times their backup dumps were taken.). Once complete, run Disk ReFit.
Q2 Did I miss a step?
A2 Maybe, maybe not (insufficient information). However, do make sure both servers have:
similar character set installs (collation),
similar service pack levels applied,
similar database device geometries.
Q3 or is there a problem with the server setup?
A3 To help rule out some potential issues with the server setup, do a dump and load of Pubs on the new server; if Pubs restores without issues, the basic server configuration is probably not the main issue.
What I'm I doing wrong? Did I miss a step? or is there a problem with the server setup?
Ans..Q1 What I'm I doing wrong?
A1 Maybe nothing (insufficient information).
Was the master backup used in the restore to the second server in fact current (assuming the restores (DB Loads) were performed to similar devices on the new server - using similar device orders, sizes and types, as those of the DB devices as they existed on the original server)? If not, some device data may be incorrect (most likely, incomplete). In that case it may be possible to use Disk ReInit for each device on the new server as necessary to address the issue (using the same logical and physical device names is easier if that is the case; however the 2k block size value entered for the size parameter must be the same as the production user devices at the times their backup dumps were taken.). Once complete, run Disk ReFit.
Q2 Did I miss a step?
A2 Maybe, maybe not (insufficient information). However, do make sure both servers have:
similar character set installs (collation),
similar service pack levels applied,
similar database device geometries.
Q3 or is there a problem with the server setup?
A3 To help rule out some potential issues with the server setup, do a dump and load of Pubs on the new server; if Pubs restores without issues, the basic server configuration is probably not the main issue.
Restoring to new database doesn't default DB file names
In SQL 2000 EM, when you restore a database from an existing database backup but specify a new database name to restore to, the filenames are automatically updated to match the new name of the database that will be restored.
I tried to do this with SQL 2005 Management Studio but it appears that the filenames aren't updated i.e. they are the same as the original database filenames. Do i have to manually go and change the filenames to reflect the fact that I'm creating a new database via a restore? If so, why doesn't the behaiour follow that of SQL 2000?
Thanks
Hello thereThis is by design in SQL Server 2005.
There are two ways to restore to a new database:
1- By launching the restore database dialog from the context of an existing database. This would pre-load the existing backup set. You could change the database name & the dialog would automatically change the DB filenames for you.
2- By launching the resotore database from the 'Databases' node & later specifying the backup set by selecting a backup file thru 'device' option.
#1 above should work fine when you change the database name & hit 'Ok'
#2 above doesn't automatically change the DB file names for you. This is by design in SQL Server 2005. In SQL Server 2000 too, you had to go to the 'options' page for the dialog to automatically change file names.
The grid on the options page in SQL Server 2005's restore dialog is editable. You could directly edit the file names in the grid to accomplish the action.
Thanks,
Restoring to new database doesn't change logical DB name
I created a new database from a restore backup method. The database file names did get updated with the new database name, but the logical file name still remains the old file name.
Do we have to manually change the logical file name in the new database?
what is the use of logical name?
Please help!
Do we have to manually change the logical file name in the new database?
what is the use of logical name?
Please help!
The logical file will not change while u restore a db. you have to change it explicitly
alter database gpx mODIFY FILE (NAME=oldLogicalFilename,NEWNAME=NewLogicalFilename)
Madhu
|||
what is the use of logical name?
The name to represent the database data file. The logical name for each file is contained in the name column
The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can hinder the performance of the whole system.Restoring to another server issue with data file sizes
I'm using SQL Server 2000 and need to restore a large database onto a different node. The problem is the original database has a 74 gb first datafile and the node where I need to restore it doesn't have a single drive that big. I'm trying to use a backup of the original database and restore it into an existing database on another node and am using the move options to put the files in the right places.
Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
Thanks for any help.Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
I don't think so; you would have to do this first on the source database and then edit the file paths when restoring to the backup database.
Regards,
hmscott
Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
Thanks for any help.Is there a way I run the restore to split the 74 gb datafile across drives on my target node?
I don't think so; you would have to do this first on the source database and then edit the file paths when restoring to the backup database.
Regards,
hmscott
Monday, March 26, 2012
Restoring to a different server
I have a back up of one of our database, which i want to restore onto our
Devolopment server. I have tied to do a 'restore' through Enterprise manager,
but it fails with a SQL 42000 error. It also says something about ' use WITH
MOVE'
You have different disk layout on your two servers. The EM GUI allows you
to MOVE a data or log file to a different drive. You also have the ability
to specify WITH MOVE when restoring a database via T-SQL within Query
Analyzer.
Bottom line: you will need to tell SQL Server to move the data and log
file(s) to a drive that exists on the machine that you are restoring to.
Lots of information is available within Books Online (within the SQL Server
program group) or Transact-SQL Help (available from Query Analyzer).
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:DC547DF1-D385-463E-96FF-16C7C9C1D6CE@.microsoft.com...
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise
manager,
> but it fails with a SQL 42000 error. It also says something about ' use
WITH
> MOVE'
>
|||Hi,
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'
In the physical path give the available drive letters and folder names
Thanks
Hari
MCDBA
"Peter Newman" wrote:
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise manager,
> but it fails with a SQL 42000 error. It also says something about ' use WITH
> MOVE'
>
Devolopment server. I have tied to do a 'restore' through Enterprise manager,
but it fails with a SQL 42000 error. It also says something about ' use WITH
MOVE'
You have different disk layout on your two servers. The EM GUI allows you
to MOVE a data or log file to a different drive. You also have the ability
to specify WITH MOVE when restoring a database via T-SQL within Query
Analyzer.
Bottom line: you will need to tell SQL Server to move the data and log
file(s) to a drive that exists on the machine that you are restoring to.
Lots of information is available within Books Online (within the SQL Server
program group) or Transact-SQL Help (available from Query Analyzer).
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:DC547DF1-D385-463E-96FF-16C7C9C1D6CE@.microsoft.com...
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise
manager,
> but it fails with a SQL 42000 error. It also says something about ' use
WITH
> MOVE'
>
|||Hi,
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'
In the physical path give the available drive letters and folder names
Thanks
Hari
MCDBA
"Peter Newman" wrote:
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise manager,
> but it fails with a SQL 42000 error. It also says something about ' use WITH
> MOVE'
>
restoring to 2005 doesnt work
Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc mak
e
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc mak
e
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>
restoring to 2005 doesnt work
Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
Dan
Hi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan
|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
Dan
Hi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan
|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>
restoring to 2005 doesnt work
Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
--
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> > Hi peeps
> >
> > Bit of a problem... I did a backup of a database on SQL Server 2000 which
> > I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> > Developer Ed), but I'm getting the following error...
> >
> > Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> >
> > System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> > Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> > operating system system error 21 (the device is not ready).
> >
> > --
> >
> > I can't think of any reason why this would be happening, apart from the
> > fact
> > that I've changed the DOMAIN of the network from DSERV to something else,
> > so
> > is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> > missing?
> >
> > Cheers
> >
> >
> >
> > Dan
>
>
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
--
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> > Hi peeps
> >
> > Bit of a problem... I did a backup of a database on SQL Server 2000 which
> > I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> > Developer Ed), but I'm getting the following error...
> >
> > Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> >
> > System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> > Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> > operating system system error 21 (the device is not ready).
> >
> > --
> >
> > I can't think of any reason why this would be happening, apart from the
> > fact
> > that I've changed the DOMAIN of the network from DSERV to something else,
> > so
> > is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> > missing?
> >
> > Cheers
> >
> >
> >
> > Dan
>
>
Restoring the source db with log shipping.
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
Restoring the source db with log shipping.
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.sql
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.sql
Restoring the source db with log shipping.
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
Subscribe to:
Posts (Atom)