restoring sql 2000 backup on sql 7

Hi,

I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?

The database only contains tables and views...

Thanks
Koen

You may have to move the tables and Views and you cannot enable relationship there is none in 7.0 and in Belgium if the 7.0 is not using unicode you cannot use Unicode because to Unicode enable you must reinstall. There are many differences with those versions it is hard to tell you in one post. The best thing to do ask the customer for the personal edition which is free to them so you can develop directly to 7.0 Hope this helps.

Restoring Replicated DB to NonReplicated Server

Hi all. I am working on a SQL 7.0-2000 upgrade testing/planning
scenario. My question is: Is there any way to restore a backup of a
published DB to a non-replicated server?
When we go live, I plan to drop all of the subscriptions and
publications before restoring the databases onto the new SQL 2000 SP4
servers, but for testing, I want to take a backup of the live
replicated database and restore it onto a test server without any trace
of replication. Alternatively, is there an easy way to remove all
traces of replication (i.e. replication tables/triggers/etc.) once I
have restored the backup of the replicated DB onto the test server?
Any help/advice is greatly appreciated.
Thanks,
Peter
Peter,
most remaining replication objects can be removed using
sp_removedbreplication. The conflict tables will be left hanging and a few
views, but it should be 99% clean after this.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this get rid of the replication triggers as well? That is what I
am most concerned about. Also, not sure if it makes any difference, but
I am using merge replication.
Thanks,
Peter
Paul Ibison wrote:
> Peter,
> most remaining replication objects can be removed using
> sp_removedbreplication. The conflict tables will be left hanging and a few
> views, but it should be 99% clean after this.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||I tried to restore the SQL 7.0 replicated database onto SQL 2000 SP4,
but the restore fails with a "Invalid column name 'excluded_cols'."
error.

>From what I found in a quick Google search, it sounds like this is
probably due to replication. My question is: Is the database fully
restored at this point and I can just run sp_removedbreplication? Or
will I have to find space on a SQL 7.0 server to restore the replicated
database, then run sp_removedbreplication, then back up that database,
then restore it to SQL 2000?
Thanks,
Peter
|||Peter - yes it'll remove the replication triggers.
Cheers,
Paul Ibison
|||Is the database visible in the sql server 2000 instance? If it is,
sp_removedbreplication should do the trick. If not, restore the backup on
the SQL Server 7.0 instance as a different name, remove replication from
this database, back it up then restore on the sql 2000 instance.
HTH,
Paul Ibison

Restoring Multiple Logs In One Operation

I am working with a client on backup strategy. Their database undergoes a
full backup 2-3 times a week with hourly log backups during business hours
(8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
extension that I need to restore one by one. Is there a way to restore the
logs as a group?
--
Larry Menzin
American Techsystems Corp.Not without executing several RESTORE LOG commands. You can automate this by writing some code for
your client. Hard to give further recommendations as we don't know anything about how the backups
are taken, named etc. But using the backup history tables in msdb is what EM does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:CDDCDA6F-255D-4069-99C5-C10637DDD4EE@.microsoft.com...
>I am working with a client on backup strategy. Their database undergoes a
> full backup 2-3 times a week with hourly log backups during business hours
> (8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
> extension that I need to restore one by one. Is there a way to restore the
> logs as a group?
> --
> Larry Menzin
> American Techsystems Corp.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.
> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
>> You can view the physical files from the backup using
>> restore filelistonly.
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>> If you need to have the files in
>> another location, use the move option in the restore script
>> to specify the physical file locations.
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
>> Yes you can use with move. You can follow the example in the
>> following knowledge base article:
>> http://support.microsoft.com/?id=304692
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
Christoffer
You can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.
|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com.. .
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>No, You cannot use the move option with system databases.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.
|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com.. .
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.
|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.
4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring just the Data?

Hi All,

We have a database on our server in the office, which I have installed to test an application in delevelopment. I've got it all working and tested OK.

I've continued working on it at home. I've added some fields to one of the tables. The database in office has a data in which I whish to transfer to my test database - with the new fields added.

I have tried the backup and restore procedure, which works great, but it overwrites the entire database, I just want the data from all tables in one procedure each way, one to backup and one to restore.

I hope that makes sense.

Thanks

Wayne

You will need to employ some form of scripting, perhaps these resources will help point you in the right direction.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

Restoring encrypted databases between different servers

Hello:

I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.

If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.

Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?

Thanks,

Cyndi

After the initial RESTORE and ALTER MASTER KEY … ADD ENCRYPTION BY SERVER MASTER KEY on ServerB, a redundant copy of the DBMK (protected by ServerB SMK) will be stored in the mater DB; and as long as the DBMK is the same, the copies should be synchronized. This redundant copy is there to minimize the DBMK management after the initial setup (via RESTORE).

I hope this information helps. Please let us know if you have any additional questions and/or if you have any additional feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine