xp cmdshell: restoring

Showing posts with label restoring. Show all posts

Wednesday, March 28, 2012

Problem restoring transaction log

Dear all
I have a problem restoring a transaction log backup.
Platform SQL Server 2000 Standard Edition SP 2
Recovery mode = FULL
Transaction log backed up when empty to give us a nice
point to return to after some tests.
Backup was performed with Enterprise Manager.
Tlog has been grown from 23Gb to 46Gb after backup to
accommodate large load of transactions.
No dump device was created when backing up Tlog. Tlog
backed up to a file.
Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
When restoring in Enterprise Manager (no matter what
options I choose) I get the infamous message:
The preceding restore operation did not specify WITH
NORECOVERY or WITH STANDBY.
Restart the restore sequence, specifying WITH NORECOVERY
or WITH STANDBY for all but the final step.
I've tried from filegroups and from devices. Specified
backups and specified "Leave DB non-operational
for more Tlog restores"
No joy.
I've tried to use a standalone restore script:
RESTORE LOG SD70DbR7
FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
WITH RECOVERY
I get the same message.
I have to specify a file as the TLog is not backed up to a
backup device.
This could be a big mistake on my part
I've specified WITH NORECOVERY and still the same message.
I've said FILE=1 but sill the same message.
When I leave out the very necessary FROM statement I get
teh nice message:
RESTORE LOG successfully processed 0 pages in 0.000
seconds (0.000 MB/sec).
I have some theories as to why I can't restore my TLog but
it seems obscene:
Could the fact that this database has not been backed up
be the issue?
Not on a suitable service pack.
Any suggestions?
Thanks
/SteveYou have to restore the database before you can restore the log. The error
message is saying that you did not do a restore leaving the server in
"norecovery" or "standby" mode so you cannot restore the log. Restore the
database backup and all log backups before that t-log backup using
norecovery and then apply that restore with recovery.
Christian Smith
"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in message
news:1281a01c3f646$a327e430$a401280a@.phx
.gbl...
> Dear all
> I have a problem restoring a transaction log backup.
> Platform SQL Server 2000 Standard Edition SP 2
> Recovery mode = FULL
> Transaction log backed up when empty to give us a nice
> point to return to after some tests.
> Backup was performed with Enterprise Manager.
> Tlog has been grown from 23Gb to 46Gb after backup to
> accommodate large load of transactions.
> No dump device was created when backing up Tlog. Tlog
> backed up to a file.
> Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
> When restoring in Enterprise Manager (no matter what
> options I choose) I get the infamous message:
> The preceding restore operation did not specify WITH
> NORECOVERY or WITH STANDBY.
> Restart the restore sequence, specifying WITH NORECOVERY
> or WITH STANDBY for all but the final step.
> I've tried from filegroups and from devices. Specified
> backups and specified "Leave DB non-operational
> for more Tlog restores"
> No joy.
> I've tried to use a standalone restore script:
> RESTORE LOG SD70DbR7
> FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
> WITH RECOVERY
> I get the same message.
> I have to specify a file as the TLog is not backed up to a
> backup device.
> This could be a big mistake on my part
> I've specified WITH NORECOVERY and still the same message.
> I've said FILE=1 but sill the same message.
> When I leave out the very necessary FROM statement I get
> teh nice message:
> RESTORE LOG successfully processed 0 pages in 0.000
> seconds (0.000 MB/sec).
> I have some theories as to why I can't restore my TLog but
> it seems obscene:
> Could the fact that this database has not been backed up
> be the issue?
> Not on a suitable service pack.
> Any suggestions?
>
> Thanks
> /Steve
>|||Thank you Christian.
As there is no space to do a backup to, I will have to go
for a plan B.
Thanks for the advice - at least it stops me searching for
something that can't be done.
>--Original Message--
>You have to restore the database before you can restore
the log. The error
>message is saying that you did not do a restore leaving
the server in
>"norecovery" or "standby" mode so you cannot restore the
log. Restore the
>database backup and all log backups before that t-log
backup using
>norecovery and then apply that restore with recovery.
>Christian Smith
>"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in
message
> news:1281a01c3f646$a327e430$a401280a@.phx
.gbl...
to a
message.
but
>
>.
>sql

Problem restoring transaction log

Dear all
I have a problem restoring a transaction log backup.
Platform SQL Server 2000 Standard Edition SP 2
Recovery mode = FULL
Transaction log backed up when empty to give us a nice
point to return to after some tests.
Backup was performed with Enterprise Manager.
Tlog has been grown from 23Gb to 46Gb after backup to
accommodate large load of transactions.
No dump device was created when backing up Tlog. Tlog
backed up to a file.
Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
When restoring in Enterprise Manager (no matter what
options I choose) I get the infamous message:
The preceding restore operation did not specify WITH
NORECOVERY or WITH STANDBY.
Restart the restore sequence, specifying WITH NORECOVERY
or WITH STANDBY for all but the final step.
I've tried from filegroups and from devices. Specified
backups and specified "Leave DB non-operational
for more Tlog restores"
No joy.
I've tried to use a standalone restore script:
RESTORE LOG SD70DbR7
FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
WITH RECOVERY
I get the same message.
I have to specify a file as the TLog is not backed up to a
backup device.
This could be a big mistake on my part
I've specified WITH NORECOVERY and still the same message.
I've said FILE=1 but sill the same message.
When I leave out the very necessary FROM statement I get
teh nice message:
RESTORE LOG successfully processed 0 pages in 0.000
seconds (0.000 MB/sec).
I have some theories as to why I can't restore my TLog but
it seems obscene:
Could the fact that this database has not been backed up
be the issue?
Not on a suitable service pack.
Any suggestions?
Thanks
/SteveYou have to restore the database before you can restore the log. The error
message is saying that you did not do a restore leaving the server in
"norecovery" or "standby" mode so you cannot restore the log. Restore the
database backup and all log backups before that t-log backup using
norecovery and then apply that restore with recovery.
Christian Smith
"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in message
news:1281a01c3f646$a327e430$a401280a@.phx.gbl...
> Dear all
> I have a problem restoring a transaction log backup.
> Platform SQL Server 2000 Standard Edition SP 2
> Recovery mode = FULL
> Transaction log backed up when empty to give us a nice
> point to return to after some tests.
> Backup was performed with Enterprise Manager.
> Tlog has been grown from 23Gb to 46Gb after backup to
> accommodate large load of transactions.
> No dump device was created when backing up Tlog. Tlog
> backed up to a file.
> Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
> When restoring in Enterprise Manager (no matter what
> options I choose) I get the infamous message:
> The preceding restore operation did not specify WITH
> NORECOVERY or WITH STANDBY.
> Restart the restore sequence, specifying WITH NORECOVERY
> or WITH STANDBY for all but the final step.
> I've tried from filegroups and from devices. Specified
> backups and specified "Leave DB non-operational
> for more Tlog restores"
> No joy.
> I've tried to use a standalone restore script:
> RESTORE LOG SD70DbR7
> FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
> WITH RECOVERY
> I get the same message.
> I have to specify a file as the TLog is not backed up to a
> backup device.
> This could be a big mistake on my part
> I've specified WITH NORECOVERY and still the same message.
> I've said FILE=1 but sill the same message.
> When I leave out the very necessary FROM statement I get
> teh nice message:
> RESTORE LOG successfully processed 0 pages in 0.000
> seconds (0.000 MB/sec).
> I have some theories as to why I can't restore my TLog but
> it seems obscene:
> Could the fact that this database has not been backed up
> be the issue?
> Not on a suitable service pack.
> Any suggestions?
>
> Thanks
> /Steve
>|||Thank you Christian.
As there is no space to do a backup to, I will have to go
for a plan B.
Thanks for the advice - at least it stops me searching for
something that can't be done.
>--Original Message--
>You have to restore the database before you can restore
the log. The error
>message is saying that you did not do a restore leaving
the server in
>"norecovery" or "standby" mode so you cannot restore the
log. Restore the
>database backup and all log backups before that t-log
backup using
>norecovery and then apply that restore with recovery.
>Christian Smith
>"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in
message
>news:1281a01c3f646$a327e430$a401280a@.phx.gbl...
>> Dear all
>> I have a problem restoring a transaction log backup.
>> Platform SQL Server 2000 Standard Edition SP 2
>> Recovery mode = FULL
>> Transaction log backed up when empty to give us a nice
>> point to return to after some tests.
>> Backup was performed with Enterprise Manager.
>> Tlog has been grown from 23Gb to 46Gb after backup to
>> accommodate large load of transactions.
>> No dump device was created when backing up Tlog. Tlog
>> backed up to a file.
>> Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
>> When restoring in Enterprise Manager (no matter what
>> options I choose) I get the infamous message:
>> The preceding restore operation did not specify WITH
>> NORECOVERY or WITH STANDBY.
>> Restart the restore sequence, specifying WITH NORECOVERY
>> or WITH STANDBY for all but the final step.
>> I've tried from filegroups and from devices. Specified
>> backups and specified "Leave DB non-operational
>> for more Tlog restores"
>> No joy.
>> I've tried to use a standalone restore script:
>> RESTORE LOG SD70DbR7
>> FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
>> WITH RECOVERY
>> I get the same message.
>> I have to specify a file as the TLog is not backed up
to a
>> backup device.
>> This could be a big mistake on my part
>> I've specified WITH NORECOVERY and still the same
message.
>> I've said FILE=1 but sill the same message.
>> When I leave out the very necessary FROM statement I get
>> teh nice message:
>> RESTORE LOG successfully processed 0 pages in 0.000
>> seconds (0.000 MB/sec).
>> I have some theories as to why I can't restore my TLog
but
>> it seems obscene:
>> Could the fact that this database has not been backed up
>> be the issue?
>> Not on a suitable service pack.
>> Any suggestions?
>>
>> Thanks
>> /Steve
>
>.
>

Problem restoring SQL Svr 2000 Master db - wrong server version?

I'm trying to rebuild from a meltdown. I'm using disk backup files. I
could have sworn that I had SQL Server 2000 SP3 installed on the old
machine, but maybe not.

With SP3 installed, I get a message along the lines that the restore
can't be done because the backup was created using server version
134218262 and this server is version 134218488.

I've searched the documentation and can't find any reference to those
version numbers.

In any case, I thought SQL Server 2000 could restore backups created
on any SP level to any SP level.

Any ideas?

--
Regards.
Richard.On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
<richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:

>I'm trying to rebuild from a meltdown. I'm using disk backup files. I
>could have sworn that I had SQL Server 2000 SP3 installed on the old
>machine, but maybe not.

Ooop. Sorry. I had SP2 installed.

--
Regards.
Richard.|||"Richard Sherratt" <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote in
message news:dp7700dtggi3ev97mmgfgtacct926mjdb4@.4ax.com...
> On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
> <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:
> >I'm trying to rebuild from a meltdown. I'm using disk backup files. I
> >could have sworn that I had SQL Server 2000 SP3 installed on the old
> >machine, but maybe not.
> Ooop. Sorry. I had SP2 installed.

You mention the master DB in the subject.

I believe the system databases (Master, Model, MSDB) have to be restored to
the same level service pack they were backed up from.

Otherwise you're right. You can restore different SP levels of dbs to
different servers.

>
> --
> Regards.
> Richard.|||"Greg D. Moore $Strider$" <mooregr@.greenms.com> wrote in message news:<eRSMb.72883$Dq1.66308@.twister.nyroc.rr.com>...
> "Richard Sherratt" <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote in
> message news:dp7700dtggi3ev97mmgfgtacct926mjdb4@.4ax.com...
> > On Tue, 13 Jan 2004 06:30:12 GMT, Richard Sherratt
> > <richard.sherratt@.NOTHINGHEREbrunsley.com.au> wrote:
> > >I'm trying to rebuild from a meltdown. I'm using disk backup files. I
> > >could have sworn that I had SQL Server 2000 SP3 installed on the old
> > >machine, but maybe not.
> > Ooop. Sorry. I had SP2 installed.
> You mention the master DB in the subject.
> I believe the system databases (Master, Model, MSDB) have to be restored to
> the same level service pack they were backed up from.
> Otherwise you're right. You can restore different SP levels of dbs to
> different servers.

As part of a D&R test we restored databases to new servers, much the
same as you appear to be trying.

My best guess is that you have a different server name. We made sure
that the servers had the same name as the 'real' ones. If you are
dealing with a meltdown, this might actually help because your client
software won't have to be modified for the new server name.
>
> > --
> > Regards.
> > Richard.|||On Tue, 13 Jan 2004 13:59:38 GMT, "Greg D. Moore $Strider$"
<mooregr@.greenms.com> wrote:

<snip
>You mention the master DB in the subject.
>I believe the system databases (Master, Model, MSDB) have to be restored to
>the same level service pack they were backed up from.
>Otherwise you're right. You can restore different SP levels of dbs to
>different servers.

Thanks. That would explain it.

--
Regards.
Richard.

problem restoring master , model , msdb datbase

Hi ,
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_db
'model' statement does not work, and you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. You
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DBs
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error after
moving the "master" db
Step 1 : Moving ?master? database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and choosing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in the
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving ?tempdb? database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note :
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/features/mssql/article.php/3379901
Step 3 : Moving ?model? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving ?msdb? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for ?model? MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_db
>'model' statement does not work, and you receive the following error message:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. You
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For more
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System DBs
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1

problem restoring master , model , msdb datbase

Hi ,
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_d
b
'model' statement does not work, and you receive the following error message
:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. Yo
u
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup paramete
r,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DB
s
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error afte
r
moving the "master" db
Step 1 : Moving master database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure
A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and cho
osing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in th
e
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving tempdb database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note
:
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/feat...cle.php/3379901
Step 3 : Moving model database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving msdb database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the ne
w
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for model MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/defaul...kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_
db
>'model' statement does not work, and you receive the following error messag
e:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. Y
ou
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup paramet
er,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For mor
e
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System D
Bs
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1

Problem restoring database with encrypted columns to different database or server with encrypted

I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.

First, here's how i created the symmetric key and encrypted data in the original database:

create master key
encryption by password = 'testAppleA3';

create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

open symmetric key sk_Test decryption by certificate test;

insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));

select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees

close all symmetric keys

Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).

Then if i attempt to open the key in the new database and decrypt:

open symmetric key sk_Test decryption by certificate test;

I get the error: An error occurred during decryption.

Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then I try opening the key again and get the error again:

An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do

drop symmetric key sk_test

then

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

and then try to open it.

Same error!

So then i decide, let's drop everything, the master key, the certificate and then symmetric key:

drop symmetric key sk_test
drop certificate test
drop master key

Then recreate the master key:

create master key
encryption by password = 'testAppleA3';

Restore the certificate from a backup i had made to a file:

CREATE CERTIFICATE test
FROM FILE = 'c:\storedcerts\encryptiontestcert'

Recreate the symmetric key again:

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

And now open the key only to get the error:

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.

I also tried running the command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

again but this does not resolve the issue.

Thx.

I also saw a posting here where it referenced running the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

However when i then try to open the key it stil gives an error:

An error occurred during decryption.

|||

So now I'm confused.

Where this did not work is when i created the intial database using SQL Server Express edition on a Win XP workstation and then was restoring to a database located on a SQL Server Standard edition on a Windows 2003 Server.

I just tried creating everything from scratch on one WIndows 2003 Server with SQL Server Standard edition and then restoring that database to a different Windows 2003 Server also with SQL Server Standard and this time it worked as long as i did the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

My question (comes to reliabilty concerns) is why did it not work when i did:

Orig database SQLSvr Express on Win XP

Restore to SQL Svr Std on Win2003Svr

But did work when I did

Orig database SQL Svr Std on Win 2003Svr

Restore to different SQL Svr Std on different Win 2003 Svr

What would the reason for this be?

Thx.

|||

See this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1

There is an issue if you restore the database on a different time zone, but otherwise, the steps described in that thread are the ones that should be performed after restoring a database that has encrypted data. The time zone issue may also affect you if the times of the machines that you use are not in sync.

You should NOT drop and recreate the symmetric key, that will just create a different key and you'll have no way to decrypt the data in the database.

To figure out what's going on, I suggest the following:

1. Make sure this is not a time issue; check the times of the two machines. If there is a difference, wait for the amount of time before trying to decrypt.

2. If it's not a time issue, try protecting the symmetric key with a password as well as with a certificate. Try opening the key using both the password and the certificate. This should allow you to figure out if the problem is with the certificate or with the key. Also, please post the entire error message that you receive, including the error number and error state - the state may provide some additional information.

Thanks
Laurentiu

|||

Thanks. What you say makes sense and I'm going to try it again. Like I said, everything was fine when the both servers involved were Windows 2003 with SQL Svr Std.

But it didn't work when one was SQL Svr Express on WinXP. So that's the part I'm confused about.

But I will try it again in that scenario later today to see if it is reproducible.

The timezone is the same on all machines involved in these tests.

Thanks,
R-

|||

Have you been able to repro this issue. Have you tried using a password to isolate the problem?

Thanks
Laurentiu

sql

Problem restoring database to a compressed drive.

I am learning how to use SMO and I have a test application that allows me to restore a database backup to a new database. On my system I have one physical drive but two logical drives on separate partitions. Drive C: is on a partition that is a FAT32 partition and drive D: is on a partition that is a NTFS partition and is a compressed partition. The database backup file is located on drive D:. When I try to restore the backup to a new database and provide a path on the D: drive in the RelocateFile object I get the following error message.

"The file 'D:\NPS_Test.mdf' is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

When I do a restore and indicate that the new database file will be located on the C: drive the restore works. I assume the problem has to do with the fact that drive D: is set up as a compressed drive, but I am not sure.

Does this mean that I cannot store a database file on a compressed drive or is there something I need to do to make it work?

Hi,

http://blogs.msdn.com/sqlblog/archive/2006/10/02/SQL-Server-databases-are-not-supported-on-compressed-volumes.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem restoring database ...

Dear Newsgroup,

I am using sql server 2000 over win 2000 server with service pack 4.

I have been given a back up of a database (I have tried both from T-SQL and
Enterprise Manager)

T-SQL

RESTORE FILELISTONLY
FROM DISK = 'c:\A.bck'

RESTORE DATABASE B
FROM DISK = 'c:\A.bck'
WITH MOVE 'A_Data' TO 'c:\test\B.mdf',
MOVE 'A_Log' TO 'c:\test\B.ldf'

and as I try to restore I get the following error :

Server: Msg 3154, Level 16, State 2, Line 1
The backup set holds a backup of a database other than the existing 'B'
database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Would you kindly help me ?????

Thank you in advance,
YassYass (gol_e_yass@.yahoo.com) writes:
> RESTORE FILELISTONLY
> FROM DISK = 'c:\A.bck'
> RESTORE DATABASE B
> FROM DISK = 'c:\A.bck'
> WITH MOVE 'A_Data' TO 'c:\test\B.mdf',
> MOVE 'A_Log' TO 'c:\test\B.ldf'
> and as I try to restore I get the following error :
> Server: Msg 3154, Level 16, State 2, Line 1
> The backup set holds a backup of a database other than the existing 'B'
> database.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Would you kindly help me ?????

I'm out on a limb here, but my interpretation is that there is already
a database B on the machine, but the backup is taken from another
database (A?). Adding ", REPLACE" at the end will get rid of the
error message - and wipe out B, so be careful that this is what you
want to do.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem Restoring Database

I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST.BAK'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

When restoring other files, I would get this same error message, copy it to another folder and it would work. With this particular one, no matter what folder I copy it to I get the same error message. From what I can find online is that its a permission thing, but I cant find anywhere to change the permissions.

Any help is appreciated.

That can be based on serveral problems which either depends on the way you authenticate to the database server (you did not mention that) and the service account (running SQL Server authentication) or you own account (running Windows authenticaiton) does not have the appropiate rights to access the folder or you are running Vista, which needs elevated rights to access the special folder opn your system.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I connect using mixed authentication. I am running windows XP.|||

I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.

The 'copied-to' server in my case is running on Vista. I eventually bludgeoned it into working by opening up all the permissions on the containing folder and the .mdf file itself; so I guess this tends to confirm it's a permissions issue, but 'just give everyone access to everything' isn't really a satisfactory answer! Can anyone point us in the direction of more detail on how permissions and SQL Express interact - I gather it's particularly tricky with Vista and UAC etc. Jens - could you expand on the bit about Vista and the 'special folder' - I take it you mean the folder containing the .mdf?

TIA

|||What I ended up doing was setting the mssql folder proprty to full access and took read only off and it worked. Thanks.|||There's some more about this issue here: http://msdn2.microsoft.com/en-us/library/ms189128.aspx

Problem Restoring Database

I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:

Any help is appreciated.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I connect using mixed authentication. I am running windows XP.|||

I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.

TIA

problem restoring backup of DB

Hello,
I copied a backup file of a production DB on a production server to another
location on a Development server. When I try to restore this backup on the
Dev server - from Enterprise Manager, I am getting an error message that
states I need to use "With Move" to identify a valid location for the restor
e
file.
On the Restore Database Dialog I enter the name of the mdf file (which is
not in the dropdown list) in the "Restore as database" textbox. Then I
select the device - "Restore from" Disk, click on Add - select the bak file
,
click OK from that dialog, goto the Options tab and enter the physical path
in the "Move to physical file name" window. Then click OK on the Restore
Database dialog and get the error message above - "use With Move..." How do
I use "With Move"?
I also copied the actual mdf file from the production server and moved it to
the Dev server and tried to attach it to the Dev serve. But the mdf is
trying to reference the old Log file. I thought (hoped) it would create its
own log file. Is there a way to get around this?
Thanks,
Rich> I also copied the actual mdf file from the production server and moved it tod">
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
>
Did you detach the database first, before copying the mdf file?
If not, try detaching the DB, then copying the file(s), then re-attaching to
the other server.|||The file is a replication DB and I did not want to fiddle with
sp_removereplication. Otherwise I would have used the Copy Database wizard.
But since it is in Replication mode, I can't detach it or use the Copy
Database wizard.
What I did was to stop the production server for a few minutes, copied the
file to the dev server - restart the prod server. I did not copy the ldf
because it was kind of large. I was hoping the copy DB would create its own
log file on the Dev server. Is there a way to force that to happen?
Or am I stuck with using sp_removeReplication and then doing the copy?
"Mark Williams" wrote:

> Did you detach the database first, before copying the mdf file?
> If not, try detaching the DB, then copying the file(s), then re-attaching
to
> the other server.|||You will need the ldf file along with your mdf file. What you did is the
same as detach method except you shut MS SQL server down for a few minutes.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
> The file is a replication DB and I did not want to fiddle with
> sp_removereplication. Otherwise I would have used the Copy Database
> wizard.
> But since it is in Replication mode, I can't detach it or use the Copy
> Database wizard.
> What I did was to stop the production server for a few minutes, copied the
> file to the dev server - restart the prod server. I did not copy the ldf
> because it was kind of large. I was hoping the copy DB would create its
> own
> log file on the Dev server. Is there a way to force that to happen?
> Or am I stuck with using sp_removeReplication and then doing the copy?
> "Mark Williams" wrote:
>|||Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So once
I get the DB attached, what is the best way to shrink down the ldf? Can I
delete it once the mdf is attached and it will create a new one?
"Grant" wrote:

> You will need the ldf file along with your mdf file. What you did is the
> same as detach method except you shut MS SQL server down for a few minutes
.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
>
>|||> I get the DB attached, what is the best way to shrink down the ldf?
You shrink if *before* you detach. Or after you have attached. Use DBCC SHRI
NKFILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D1264111-609E-4190-922C-A88BB233D0CB@.microsoft.com...
> Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So on
ce
> I get the DB attached, what is the best way to shrink down the ldf? Can I
> delete it once the mdf is attached and it will create a new one?
> "Grant" wrote:
>|||Following or explaining GUI in text is incredibly hard. I suggest you invest
igate the file structure
of your backup using RESTORE FILELISTONLY. And based on that construct a RES
TORE command with the
proper commands. If restore from that doesn't work, you can post the output
from RESTORE
FILELISTONLY, your RESTORE command you tried and the error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
> Hello,
> I copied a backup file of a production DB on a production server to anothe
r
> location on a Development server. When I try to restore this backup on th
e
> Dev server - from Enterprise Manager, I am getting an error message that
> states I need to use "With Move" to identify a valid location for the rest
ore
> file.
> On the Restore Database Dialog I enter the name of the mdf file (which is
> not in the dropdown list) in the "Restore as database" textbox. Then I
> select the device - "Restore from" Disk, click on Add - select the bak fi
le,
> click OK from that dialog, goto the Options tab and enter the physical pa
th
> in the "Move to physical file name" window. Then click OK on the Restore
> Database dialog and get the error message above - "use With Move..." How
do
> I use "With Move"?
> I also copied the actual mdf file from the production server and moved it
to
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
> Thanks,
> Rich|||Thank you for your suggestion. Here is what Restore Filelist returned:
Subscriber_Data D:\MSSQLDATA\Subscriber_
Data.MDF D PRIMARY 654573568 3518437
2080640
Subscriber_Log E:\MSSQLLOGS\Subscriber_l
og.LDF L NULL 2818572288 35184372080
640
And here is what I tried with Restore Database - which is currently running
RESTORE DATABASE Subscriber
FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
WITH MOVE 'Subscriber' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
MOVE 'Subscriber_log' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
I will post the results of this restore effort - still running - probably
for an hour or so.
"Tibor Karaszi" wrote:

> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||Success!!! Thank you very much for your help. The restore DB works
perfectly!
"Tibor Karaszi" wrote:

> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||The restore command looks fine with one comment, and I also noticed the othe
r post where you said it
was successful.
The comment is that you specified the location for the database files to be
created by your restore
process to be in the Backup directory. Not that it is harmful, but perhaps a
little bit unusual.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C2E59AEB-3FF8-4930-B0E6-DDACEE2B9104@.microsoft.com...
> Thank you for your suggestion. Here is what Restore Filelist returned:
> Subscriber_Data D:\MSSQLDATA\Subscriber_Data.MDF D PRIMARY 654573568 35184
372080640
> Subscriber_Log E:\MSSQLLOGS\Subscriber_log.LDF L NULL 2818572288 351843720
80640
>
> And here is what I tried with Restore Database - which is currently runnin
g
> RESTORE DATABASE Subscriber
> FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
> WITH MOVE 'Subscriber' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
> MOVE 'Subscriber_log' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
> I will post the results of this restore effort - still running - probably
> for an hour or so.
>
> "Tibor Karaszi" wrote:
>sql

Problem restoring backup

I am running SQL 7...

I had a database on a Sql Server on a WinXP machine but I needed to reinstall that machine and I installed Win2k...

Before I format the machine I have made a backup from my database. Now, on the Win2k, I cant restore this backup. Sql Server shows the following error message:

The volume on device 'c:\backup.bak' is not a member of the media family.

Plz, help me on this... I dont know much about Sqlserver and this is the ONLY backup I have... The backup file seems fine... There was no errors during the backup proccess...

If u dont know whats happening, could u tell if exists some program that can read the backup file and extracts its data to another format?What I do is...
i create a backup device that is the same name as the old backup.
i then put the backup file where u set up the backup device to be. at this point the server should see it and use it as a lagit device
Jim

Originally posted by trojahn
I am running SQL 7...

I had a database on a Sql Server on a WinXP machine but I needed to reinstall that machine and I installed Win2k...

Before I format the machine I have made a backup from my database. Now, on the Win2k, I cant restore this backup. Sql Server shows the following error message:

The volume on device 'c:\backup.bak' is not a member of the media family.

Plz, help me on this... I dont know much about Sqlserver and this is the ONLY backup I have... The backup file seems fine... There was no errors during the backup proccess...

If u dont know whats happening, could u tell if exists some program that can read the backup file and extracts its data to another format?|||JDionne,

I need to ask u for a little more favor... Could u give me more details on how to do that? I really dont know much about Sql Server... I just follow the setup procedure of my so called technical support that now says that I did something wrong...

Edit -=-=-=-=-=-
I just figure out how to do what u said but theres a major problem... I dont know where the backup device was on the first machine... I there a way to figure that out?|||The location of the first back up dosent mater. I would create the backup device in eh mssql\backups directory just to stick to a standard.
Once u have created that device with the same name as the old backup file and move it to that dir, the server will use the file.

the following code will tell u whats in the backup when u go to restore.
run this in query analizer substituting your backup device name

RESTORE FILELISTONLY
FROM [Maersk Data Warehouse Full Backup]

after u know whats in the device u can use the following code to restore it. u will need to change the database name, backup device and mdf,ldf, (and if u have a ndf) file names
the To line indicates wehre u want it to restore to and again i would follow the standard just so u know where they are. If u have any questions just post it and ill see if i can help
Regards Jim

RESTORE DATABASE [Maersk Data Warehouse]
FROM [Maersk Data Warehouse Full Backup]
with recovery
, move 'Maersk Data Warehouse_Data'to 'E:\MSSQL\Data\Maersk Data Warehouse_Data.MDF'
, move 'Maersk Data Warehouse_Log' to 'F:\MSSQL\Data\Maersk Data Warehouse_Log.LDF'
, move 'Maersk Data Warehouse User Tables'to 'E:\MSSQL\Data\Maersk Data Warehouse_Data.NDF'

Originally posted by trojahn
JDionne,

I need to ask u for a little more favor... Could u give me more details on how to do that? I really dont know much about Sql Server... I just follow the setup procedure of my so called technical support that now says that I did something wrong...

Edit -=-=-=-=-=-
I just figure out how to do what u said but theres a major problem... I dont know where the backup device was on the first machine... I there a way to figure that out?

Problem restoring a SQL 2005 database from a backup device

I am trying to restore a database from a backup device and when I try
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
John
"wolf.emissar@.gmail.com" wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> John
> "wolf.emissar@.gmail.com" wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> >|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there are
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have any
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:
> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
> > Hi
> >
> > Other posts indicate this error message occurs if you try and restore from
> > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> > >
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
If you ran SELECT @.@.VERSION to get the version number in your other post,
then you are on SQL 2005 Service Pack 1. If you obtained it otherwise run the
command in Management Studio or SQLCMD.
I am not sure where you are getting the compatibility level from but if you
ran EXEC sp_helpdb 'MyDatabase' it will tell you at the end column of the
first resultset.
Try running:
RESTORE FILELISTONLY
FROM disk = 'C:\sqlbackups\sonar_wolf_new.bak'
John
"wolf.emissar@.gmail.com" wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> >
> > Could you try to post the sql statement you are running?
> >
> >
> > --
> > Regards
> > Steen SchlÃ¼ter Persson
> > Databaseadministrator / Systemadministrator
>|||wolf.emissar@.gmail.com wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
It sounds like you haven't got a SQL2005 instance on the PC.
Could you try to run SELECT @.@.VERSION on the laptop to see which version
it reports to be running.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Thats it, I was logging into the 2000 server. I didn't realize that
2005 SQL Express installed a 2000 version and a 2005 version of the
database server on my local machine, and for some reason it defaults to
the 2000 version of the server. When I select and connect to the 2005
version everything works as expected.
Thanks for the help, I really appreciate it.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > Steen, here is the SQL that I am running for the restore:
> >
> > restore database wolf_sonar
> > from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
> >
> > and I recieve the following error message:
> >
> > Msg 3205, Level 16, State 2, Line 1
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > One thing that I did notice that is I don't have a compatibility level
> > of 90 on the local server on the laptop that the wolf_sonar database
> > resides on. I only have 70 and 80 as available choices.
> >
> > John to answer your question, I do not have any 2000 instances on the
> > machine that I am trying to restore the database to. This is a fairly
> > new machine and I actually just installed 2005 Express on this box a
> > couple of days. As a further chack I only see sql server 2005 in add /
> > remove programs.
> >
> It sounds like you haven't got a SQL2005 instance on the PC.
> Could you try to run SELECT @.@.VERSION on the laptop to see which version
> it reports to be running.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||wolf.emissar@.gmail.com wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
>
Good to hear that you got the problem solved. A SQL2005 install will NOT
install a SQL2000 instance in anyway. It's more likely that you already
had a SQL2000 instance installed on the PC. When you then installed the
SQL2005 instance it would only be able to install as a named instance
because the SQL2000 was the default instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi
It sounds like SQL 2000 is your default instance, in which case if you don't
specify a specific instance to log into it will default to that!
John
"wolf.emissar@.gmail.com" wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > Steen, here is the SQL that I am running for the restore:
> > >
> > > restore database wolf_sonar
> > > from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> > >
> > > and I recieve the following error message:
> > >
> > > Msg 3205, Level 16, State 2, Line 1
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed.
> > > Msg 3013, Level 16, State 1, Line 1
> > > RESTORE DATABASE is terminating abnormally.
> > >
> > > One thing that I did notice that is I don't have a compatibility level
> > > of 90 on the local server on the laptop that the wolf_sonar database
> > > resides on. I only have 70 and 80 as available choices.
> > >
> > > John to answer your question, I do not have any 2000 instances on the
> > > machine that I am trying to restore the database to. This is a fairly
> > > new machine and I actually just installed 2005 Express on this box a
> > > couple of days. As a further chack I only see sql server 2005 in add /
> > > remove programs.
> > >
> >
> > It sounds like you haven't got a SQL2005 instance on the PC.
> > Could you try to run SELECT @.@.VERSION on the laptop to see which version
> > it reports to be running.
> >
> >
> > --
> > Regards
> > Steen SchlÃ¼ter Persson
> > Databaseadministrator / Systemadministrator
>|||Hi John -
Can you point me to a document confirming backups are compatible across SQL
Server 2005 versions? This has been a subject of debate here.
Thanks!
dj
"John Bell" wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
I don't think there is a single document! It is basically the same database
engine so why would you not think it is compatible?
Assuming that both instances are at the same service pack/hotfix level, then
if there are issues restoring from one version to another it should not be
because of the version which is running e.g. an unsupported collations on the
destination instance.
If you are planning a DR strategy, you should always test the recovery
scenarios and plans regardless!
John
"dj" wrote:
> Hi John -
> Can you point me to a document confirming backups are compatible across SQL
> Server 2005 versions? This has been a subject of debate here.
> Thanks!
> dj
> "John Bell" wrote:
> > Hi
> >
> > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > instance and they have the error message you get. Are you sure that there are
> > no SQL 2000 instances on the machine you have? Database restores are
> > compatible between all versions of SQL 2005 therefore you should not have any
> > problems. Is this a beta or CTP version?
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > John, thanks for the response. I am trying to restore to a 2005
> > > instance but it is an Express version, I don't know if this is the
> > > source of the problem or not.
> > >
> > > In the second link you attached Tibor mentions logging into a 2000
> > > instance. This sounds very much like the error I am receiving, do you
> > > know what he is referring to? Can express 2005 be configured to run as
> > > a 2000 instance?
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Other posts indicate this error message occurs if you try and restore from
> > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > I am trying to restore a database from a backup device and when I try
> > > > > to add the backup device in the restore database wizard I receive the
> > > > > following error message:
> > > > >
> > > > > An exception occurred while executing a transact-sql statement or
> > > > > batch.
> > > > >
> > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > >
> > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > restore the database to an Express version of 2005.
> > > > >
> > > > > I can restore the database successfully if I restore it to the same
> > > > > server that I created the backup device on.
> > > > >
> > > > > For a little background, I am trying to backup a database on a server
> > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > gig in total size.
> > > > >
> > > > > Any insight is greatly appreciated.
> > > > >
> > > > >
> > >
> > >|||Call me cautious by nature. :)
Are they compatible between 32-bit and 64-bit editions?
"John Bell" wrote:
> Hi
> I don't think there is a single document! It is basically the same database
> engine so why would you not think it is compatible?
> Assuming that both instances are at the same service pack/hotfix level, then
> if there are issues restoring from one version to another it should not be
> because of the version which is running e.g. an unsupported collations on the
> destination instance.
> If you are planning a DR strategy, you should always test the recovery
> scenarios and plans regardless!
> John
> "dj" wrote:
> > Hi John -
> >
> > Can you point me to a document confirming backups are compatible across SQL
> > Server 2005 versions? This has been a subject of debate here.
> >
> > Thanks!
> > dj
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > instance and they have the error message you get. Are you sure that there are
> > > no SQL 2000 instances on the machine you have? Database restores are
> > > compatible between all versions of SQL 2005 therefore you should not have any
> > > problems. Is this a beta or CTP version?
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > John, thanks for the response. I am trying to restore to a 2005
> > > > instance but it is an Express version, I don't know if this is the
> > > > source of the problem or not.
> > > >
> > > > In the second link you attached Tibor mentions logging into a 2000
> > > > instance. This sounds very much like the error I am receiving, do you
> > > > know what he is referring to? Can express 2005 be configured to run as
> > > > a 2000 instance?
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > >
> > > > > John
> > > > >
> > > > > "wolf.emissar@.gmail.com" wrote:
> > > > >
> > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > following error message:
> > > > > >
> > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > batch.
> > > > > >
> > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > >
> > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > restore the database to an Express version of 2005.
> > > > > >
> > > > > > I can restore the database successfully if I restore it to the same
> > > > > > server that I created the backup device on.
> > > > > >
> > > > > > For a little background, I am trying to backup a database on a server
> > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > gig in total size.
> > > > > >
> > > > > > Any insight is greatly appreciated.
> > > > > >
> > > > > >
> > > >
> > > >|||Hi
Yes, the format for a backed up database does not change.
John
"dj" wrote:
> Call me cautious by nature. :)
> Are they compatible between 32-bit and 64-bit editions?
> "John Bell" wrote:
> > Hi
> >
> > I don't think there is a single document! It is basically the same database
> > engine so why would you not think it is compatible?
> >
> > Assuming that both instances are at the same service pack/hotfix level, then
> > if there are issues restoring from one version to another it should not be
> > because of the version which is running e.g. an unsupported collations on the
> > destination instance.
> >
> > If you are planning a DR strategy, you should always test the recovery
> > scenarios and plans regardless!
> >
> > John
> >
> > "dj" wrote:
> >
> > > Hi John -
> > >
> > > Can you point me to a document confirming backups are compatible across SQL
> > > Server 2005 versions? This has been a subject of debate here.
> > >
> > > Thanks!
> > > dj
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > > instance and they have the error message you get. Are you sure that there are
> > > > no SQL 2000 instances on the machine you have? Database restores are
> > > > compatible between all versions of SQL 2005 therefore you should not have any
> > > > problems. Is this a beta or CTP version?
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > John, thanks for the response. I am trying to restore to a 2005
> > > > > instance but it is an Express version, I don't know if this is the
> > > > > source of the problem or not.
> > > > >
> > > > > In the second link you attached Tibor mentions logging into a 2000
> > > > > instance. This sounds very much like the error I am receiving, do you
> > > > > know what he is referring to? Can express 2005 be configured to run as
> > > > > a 2000 instance?
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "wolf.emissar@.gmail.com" wrote:
> > > > > >
> > > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > > following error message:
> > > > > > >
> > > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > > batch.
> > > > > > >
> > > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > > >
> > > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > > restore the database to an Express version of 2005.
> > > > > > >
> > > > > > > I can restore the database successfully if I restore it to the same
> > > > > > > server that I created the backup device on.
> > > > > > >
> > > > > > > For a little background, I am trying to backup a database on a server
> > > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > > gig in total size.
> > > > > > >
> > > > > > > Any insight is greatly appreciated.
> > > > > > >
> > > > > > >
> > > > >
> > > > >|||Hi Sheryl
If you issue a NET START command at a command prompt you can verify is
SQLExpress is running. The default name for a SQL Express instance is
SERVER\SQLEXPRESS (where SERVER is the hostname). If SQLExpress is running
you should be able to use SERVER\EXPRESS this in the connection details.
Have you downloaded Express Management Studio or got the Advanced Services
options?
If you are wanting to enable remote access to your SQL Express instance you
can enable this through the Surface Area Configuration tool sqlsac.exe
John
"Sheryl Briggs" wrote:
> you wrote:
> "It sounds like SQL 2000 is your default instance, in which case if you
> don't
> specify a specific instance to log into it will default to that"
> I installed SQL 2005 express on a machine that already had 2000 on it
> and am having the same problem, but there are only 2 instance names
> showing up in the connection mgr in Express 2005, and both say they are
> version 2000? How do i get to the 2005 connection? THe new instance
> name that appeared when I installed 2005 also says its version is
> 2000? SHould I reinstall it?
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***
>

Wednesday, March 28, 2012

Problem restoring transaction log

Problem restoring transaction log

Problem restoring SQL Svr 2000 Master db - wrong server version?

problem restoring master , model , msdb datbase

problem restoring master , model , msdb datbase

Problem restoring database with encrypted columns to different database or server with encrypted

Problem restoring database to a compressed drive.

Problem restoring database ...

Problem Restoring Database

Problem Restoring Database

problem restoring backup of DB

Problem restoring backup

Problem restoring a SQL 2005 database from a backup device

xp cmdshell

Blog Archive

About Me