MS SQl server2000 Restoration

when I restored backup file from system to another system
gave me the message "An internal consistency error
occurred.Contact Technical Support for assistance.Restore
Database is Terminating abnormally"
Abdelzahir abdelmaboud
Sudatel Co.
DBA
abdzahir@.hotmail.comAbdelzahir,
Perhaps the database was corrupt before you backed it up? Can you run
DBCC CHECKDB on the source database, and back it up again? Are you using
SQL Server native backup commands to do this or an external component
such as Litespeed? Very occasionally, external components can corrupt a
backup file.
It sounds like the database was corrupt before you backed it up, and
then it can't restore it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Abdelzahir Abdelmaboud wrote:
> when I restored backup file from system to another system
> gave me the message "An internal consistency error
> occurred.Contact Technical Support for assistance.Restore
> Database is Terminating abnormally"
> Abdelzahir abdelmaboud
> Sudatel Co.
> DBA
> abdzahir@.hotmail.comsql

MS SQl server2000 Restoration

when I restored backup file from system to another system
gave me the message "An internal consistency error
occurred.Contact Technical Support for assistance.Restore
Database is Terminating abnormally"
Abdelzahir abdelmaboud
Sudatel Co.
DBA
abdzahir@.hotmail.com
Abdelzahir,
Perhaps the database was corrupt before you backed it up? Can you run
DBCC CHECKDB on the source database, and back it up again? Are you using
SQL Server native backup commands to do this or an external component
such as Litespeed? Very occasionally, external components can corrupt a
backup file.
It sounds like the database was corrupt before you backed it up, and
then it can't restore it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Abdelzahir Abdelmaboud wrote:
> when I restored backup file from system to another system
> gave me the message "An internal consistency error
> occurred.Contact Technical Support for assistance.Restore
> Database is Terminating abnormally"
> Abdelzahir abdelmaboud
> Sudatel Co.
> DBA
> abdzahir@.hotmail.com

MS SQL restore

I have a MS SQL database backup from a client and would like to use this to create the database on my local machine.

I have been advised to do this by using restore database in enterprise manager.

I am lost though as I really dont know much about MS SQL - sorry people but can anyone tell me how to do this?Sure (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_20h1.asp).

-PatP|||I think they had the database on their D:/ drive and I only have a C: drive

That is whats causing the problem I think.

Any suggestions on how to get round this?

Thanks

Paul|||When restoring in EM, click on Options tab and change the path for every device there.|||and buy a copy of this book...
Inside SQL Server 2000 By Kalen Delaney (http://www.bestwebbuys.com/Inside_Microsoft_SQL_Server_2000-ISBN_0735609985.html?isrc=b-search)

MS SQL Backup Schedule

When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
and schedule. Then I select DAILY and a Time, click apply, and then OK. Whe
n
I go back in to review the setting it is back the way it was with no
schedule.
What am I doing wrong ?I'm unable to reproduce this on my sql2k+sp3a. Perhaps, you want to update
to the latest service pack.
http://microsoft.com/sql
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite
box
> and schedule. Then I select DAILY and a Time, click apply, and then OK.
When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||That dialog only creates an SQL Server agent job. Check in SQL Server Agent,
Jobs and you will see
you job(s) there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite b
ox
> and schedule. Then I select DAILY and a Time, click apply, and then OK. W
hen
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||Thank you Tibor, where can I see the scheduled jobs ?
gerrym
"Tibor Karaszi" wrote:

> That dialog only creates an SQL Server agent job. Check in SQL Server Agen
t, Jobs and you will see
> you job(s) there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
> news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
>
>|||Enterprise Manager, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:7DC355DB-A511-4B91-A46D-17B309D4879C@.microsoft.com...[vbcol=seagreen]
> Thank you Tibor, where can I see the scheduled jobs ?
> gerrym
> "Tibor Karaszi" wrote:
>
see[vbcol=seagreen]

MS SQL Backup Schedule

When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
and schedule. Then I select DAILY and a Time, click apply, and then OK. When
I go back in to review the setting it is back the way it was with no
schedule.
What am I doing wrong ?
I'm unable to reproduce this on my sql2k+sp3a. Perhaps, you want to update
to the latest service pack.
http://microsoft.com/sql
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite
box
> and schedule. Then I select DAILY and a Time, click apply, and then OK.
When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?
|||That dialog only creates an SQL Server agent job. Check in SQL Server Agent, Jobs and you will see
you job(s) there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
> and schedule. Then I select DAILY and a Time, click apply, and then OK. When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?
|||Thank you Tibor, where can I see the scheduled jobs ?
gerrym
"Tibor Karaszi" wrote:

> That dialog only creates an SQL Server agent job. Check in SQL Server Agent, Jobs and you will see
> you job(s) there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
> news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
>
>
|||Enterprise Manager, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:7DC355DB-A511-4B91-A46D-17B309D4879C@.microsoft.com...[vbcol=seagreen]
> Thank you Tibor, where can I see the scheduled jobs ?
> gerrym
> "Tibor Karaszi" wrote:
see[vbcol=seagreen]

MS SQL Backup issues

Hi,

I am running on MS SQL 2000 of this version:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I have created a backup procedure to make automated db backup. However it is working only from the query analyzer but not from dts package. Does it have any particular reason to behave this way in your opinion ? Is it some limitation of backup statement ?

Here is the code i am using:

DECLARE @.dirname VARCHAR(255)
DECLARE @.filename VARCHAR(255)
DECLARE @.statement VARCHAR(255)
DECLARE @.date_value VARCHAR(255)

SET @.dir_value = (SELECT dirname FROM Extend_ObalServis.dbo.backup_dir_name WHERE id_dirname = (SELECT MAX(id_dirname) FROM Extend_ObalServis.dbo.backup_dir_name))

SET @.dirname = 'c:\db\archive\source\' + @.dir_value

SET @.filename = 'obal_source.dat'

SET @.filename = @.dirname + '\' + @.filename

SET @.statement = 'mkdir ' + '"' + @.dirname + '"'

select @.statement, @.filename

EXECUTE master.dbo.xp_cmdshell @.statement

-- Create a logical backup device for the full obal_target database backup.
USE master
EXECUTE sp_addumpdevice 'disk', 'SOURCE_BCKP', @.filename

-- Back up the full source database.
BACKUP DATABASE obal_source TO SOURCE_BCKP

EXECUTE sp_dropdevice 'SOURCE_BCKP'

I created a simple dts package with an execute sql task and saved it as a structured storage file to the files system and run it by dtsrun. It creates the needed directory. It starts to create a backup file (I see that the space on the disk is decreasing). A placeholder of a file with 0B size appears. But right after the package finishes its work the file disappears. Any suggestion would be very appreciated.

Thanks

You say that you are running the package via dtsrun....which indicates that you are likely using a sql job to invoke it. SQL jobs run under a different security level than you. Check to see what user the sql job is running under. That is likely your problem. If you can change that user so that it has rights to do what is in the script, it should fix it for you.
Tim|||

Make sure the job owner is a sysadmin.

One other option - you still need to fix the job owner and security context of the job - but you could also run the script directly in a job. Cleaner and fewer points of failure if there is no real driving reason to use a DTS package for this. If there is something you need to run after the backup or before, you can still use jobs steps to control the flow of tasks. You may have some other reason for doing this through DTS but just a thought.

-Sue

|||

Thanks for your answers,

however there is a strange thing that if I run that package from SQL Server the result is the same. Coul you please let know how to change the owner of the package in SQl Server ? How to set what user it would run under ?

Many thanks

|||

The owner of the package doesn't really cause problems for DTS on SQL 2000.

For the job owner, you view the properties for the job and on the General tab you can set the job owner.

You aren't getting any error messages anywhere? Did you view the job history? Do you get an error when you run the package manually on the server? Does the directory exist the you are referencing in the script?

If the package runs fine somewhere, you can most likely find the answer in the following:

INF: How to Run a DTS Package as a Scheduled Job

http://support.microsoft.com/kb/269074

-Sue

|||

HI,

so I have tried many scenarios with various settings of ownership and run options. Unfortunately there is no progress on the issue so it still does not want to leave the created file in the directory. I created a brand new package just with one sql execute task to make the job done. Basicly I did not use any jobs. But when I tried it it finished without the file created. When I created a package with the mentioned task and I run it just by clicking right mouse button and choose execute step from the context menu it finished with the very same result. I am really lost at the moment what is going on.

Service of ms sql server is running under loacl administrator account. I am logged in as the same local administrator. I am running the package from the enterprise manager as local administrator. Still the same result. I cannot understand what other permissions should I give it to make the statement successsfully run.

BTW No errors appeared anywhere.

Akos|||

One more thing. How is it possible that beside these settings the Query Analyzer runs the statement sucessfully everytime regardless of what user I am logged in under. Don't you have any experience with this kind of strange issues ?

|||

Is it possible to not be allowed to create backup for some reasons ? I mean that I run the services (mssqlserver and sqlserveragent) under the same account I am using to develop the package to do the backup of the db. So I assume it should be some reasonable cause there to forbid me to create that backup file. That account is in administrators group locally. It is included into the db users and sysadmins as builtin/administrators on the db server ...

Please let me know what other restriction affects this or give me any idea of any sources I can use to get rid of this issue as this can seriously harm the whole project I am working on :-(

thanks in advance

|||

First thing is to enable logging for this DTS package, that gives better understanding on the errors or warning to complete that particular task.

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Also I'm not able to understand why you are using a DTS package to perform a backup for a database?

MS SQL Backup

I'm trying to backup a database in MS SQL 7. I want to backup to a network directory. I'm running into problems when I give the path to a network directory. MS SQL tells me it is not a valid path. Then if I try to browes to the directory i want it does not see any of the networked drives.

Why does MS SQL only see local drives?

Has anyone delt with this problem before? Any suggestions would be greatly appriciated.

Thanks,

AnimaulYeah, its pretty annoying isn't it. Fortunately there is a solution. In Enterprise Manager under the Management folder there is a icon call backup. This is really a backup device (like a hard drive or network share). Just right click on the backup icon and choose new backup device. Give the device a name and for the file name type in the network path something like:
\\ServerName\foldername\filename.bck, you will probably get a warning that sql can't verify the path & filename but thats ok.

Then when you go to create the backup choose add and then pick the new backup device you just created from the dropdown list.

later,
mkal|||Thanks! That was a huge help.

Animaul

MS Sql 2000 Standard Backup

Hello all quick question. We are looking at purchasing MS Sql 2000 standard
and we also are looking at no longer using a tape backup solution but either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_03C5_01C3A9EC.DD50C560
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Yes, you can backup to local tape, or local or remote disk on Standard
Edition.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brian" <nixtootec@.hotmail.com> wrote in message
news:OHGZrYhqDHA.2820@.TK2MSFTNGP10.phx.gbl...
Hello all quick question. We are looking at purchasing MS Sql 2000 standard
and we also are looking at no longer using a tape backup solution but either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much appreciated.
--=_NextPart_000_03C5_01C3A9EC.DD50C560
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes, you can backup to local tape, or =local or remote disk on Standard Edition.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brian" =wrote in message news:OHGZrYhqDHA.2820=@.TK2MSFTNGP10.phx.gbl...Hello all quick question. We are looking at purchasing MS Sql 2000 standardand we also are looking at no longer using a tape backup =solution but eitherthat of another drive offsite or some type of removeable =hot swap typedrive. I seen a demo of MS Sql 2000 enterprise edition =that showedbasically where you could backup to either a tape or other =location ieanother network server but I don't know if this is the case with thestandard edition. Any help / website direction would be =much appreciated.

---

--=_NextPart_000_03C5_01C3A9EC.DD50C560--|||This is a multi-part message in MIME format.
--=_NextPart_000_0043_01C3A9E5.FD321A70
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok thanx much for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:Oos%23IbhqDHA.2632@.TK2MSFTNGP09.phx.gbl...
Yes, you can backup to local tape, or local or remote disk on Standard =Edition.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brian" <nixtootec@.hotmail.com> wrote in message =news:OHGZrYhqDHA.2820@.TK2MSFTNGP10.phx.gbl...
Hello all quick question. We are looking at purchasing MS Sql 2000 =standard
and we also are looking at no longer using a tape backup solution but =either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much =appreciated.
--=_NextPart_000_0043_01C3A9E5.FD321A70
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Ok thanx much for the =help.
"Tom Moreau" = wrote in message news:Oos%23IbhqDHA.=2632@.TK2MSFTNGP09.phx.gbl...
Yes, you can backup to local tape, =or local or remote disk on Standard Edition.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Brian" =wrote in message news:OHGZrYhqDHA.2820=@.TK2MSFTNGP10.phx.gbl...Hello all quick question. We are looking at purchasing MS Sql 2000 standardand we also are looking at no longer using a tape backup =solution but eitherthat of another drive offsite or some type of removeable =hot swap typedrive. I seen a demo of MS Sql 2000 enterprise =edition that showedbasically where you could backup to either a tape or other =location ieanother network server but I don't know if this is the case with = thestandard edition. Any help / website direction would be =much appreciated.

---

--=_NextPart_000_0043_01C3A9E5.FD321A70--

MS SQL 2000 Server 'point in time' restore

Hello,
We encountered problem concerning MS SQL 2000 point in time restore. Our
backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
VDI. We performed the following combined restore session:
[SCENARIO]
a) Database restore from full backup session, by executing the following
Transact-SQL:
RESTORE DATABASE [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH NORECOVERY;
b) Transaction log restoration which backup was finished on 7/28/2004
2:00:30 PM.
RESTORE LOG [Northwind] FROM
VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
[PROBLEM]
MS SQL backup/restore agent successfully restores full backup version and
leave Nortwind database non-recovered.
When transaction log restore process starts,
IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
fails with VD_E_TIMEOUT return value and the following message is reported by
MS SQL 2000 Server:
<Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
for STOPAT parameter.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
We started several restore sessions and always got the same error when
session was started with 'STOPAT' parameter. But at some point restore
session happened to work and we were unable to reproduce problem anymore. We
have also seen customers facing the same problem.
Sessions started with no 'STOPAT' parameter specified always went OK.
[QUESTIONS]
We would appreciate if you can help us with the following answers:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified?
2. Is there any workaround available?
[ENVIRONMENT]
Environment:
- MS SQL 2000 Server with SP3a installed
- Windows 2000 SP4
- Regional Settings set to English
Thank YouRobert,
Seems you are using a 3:rd party backup product (judging by the backup device type: VIRTUAL_DEVICE =). The
vendor of this is probably where you have to look for support.
To determine whether that is the case, I suggest that you try the RESTORE using disk (or possibly tape) and
see whether it work fine...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"robert.cokan" <robert.cokan@.discussions.microsoft.com> wrote in message
news:E4B763F8-09FD-4241-9FDF-331E7DE07819@.microsoft.com...
> Hello,
> We encountered problem concerning MS SQL 2000 point in time restore. Our
> backup/restore agent integrates to MS SQL 2000 Server through MS SQL 2000
> VDI. We performed the following combined restore session:
> [SCENARIO]
> a) Database restore from full backup session, by executing the following
> Transact-SQL:
> RESTORE DATABASE [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH NORECOVERY;
>
> b) Transaction log restoration which backup was finished on 7/28/2004
> 2:00:30 PM.
> RESTORE LOG [Northwind] FROM
> VIRTUAL_DEVICE = 'Data Protector_(DEFAULT)_Northwind_11_20_49'
> WITH STOPAT = '7/28/2004 2:00:20 PM', RECOVERY;
>
> [PROBLEM]
> MS SQL backup/restore agent successfully restores full backup version and
> leave Nortwind database non-recovered.
> When transaction log restore process starts,
> IClientVirtualDeviceSet2::GetConfiguration (60000, &vdiConfig) function call
> fails with VD_E_TIMEOUT return value and the following message is reported by
> MS SQL 2000 Server:
> <Microsoft SQL-DMO (ODBC SQLState: 42000):c91>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid value specified
> for STOPAT parameter.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
>
> We started several restore sessions and always got the same error when
> session was started with 'STOPAT' parameter. But at some point restore
> session happened to work and we were unable to reproduce problem anymore. We
> have also seen customers facing the same problem.
> Sessions started with no 'STOPAT' parameter specified always went OK.
>
> [QUESTIONS]
> We would appreciate if you can help us with the following answers:
> 1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
> failure when 'STOPAT' parameter is specified?
> 2. Is there any workaround available?
>
> [ENVIRONMENT]
> Environment:
> - MS SQL 2000 Server with SP3a installed
> - Windows 2000 SP4
> - Regional Settings set to English
> Thank You
>|||Hello Tibor,
thank you for your help. However, we need to say that we are backup vendor.
We have addressed this question to MS Support due to we were unable to find
explanation for 'Invalid value specified for STOPAT parameter' message
reported by MS SQL Server. Please help us by providing answers to the
following questions:
1. what can be a reason for IClientVirtualDeviceSet2::GetConfiguration
failure when 'STOPAT' parameter is specified? Please also note that restore
session does not always fail (see [PROBLEM] section below).
2. Is there any workaround available?
3. Under What circumstances MS SQL 2000 Server reports 'Invalid value
specified for STOPAT parameter' message?
Thank you,
Robert

MS SQL 2000 full export (not backup)

Hi Everybody,

I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.

Many thanks.You should be able to use DTS to export the entire database at one time. Just right click on the database (in EM), click on all tasks and select export data.

Specify your source server/database, your target server/database and then select either copy tables and views or copy objects. Copy objects is more useful when you are trying to recreate the database structure. Copy data just moves over any tables.

Regards,

hmscott|||Thanks for your reply scott. my export is just a part of the work, because, i have to drop the entire instance and recreate it including 'master' database due to collation name change. So, i cannot export it to another database under this instance or to a different instance (due to space restriction). Hence my initial interest is to go with a flat (text) file. Is it still possible ?

Thanks|||Or...

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF
GO|||Yurk. That's the second post I've seen today from someone needing to change the collation on their server.

I think for this you will sincerely want a second database server available as the target for your export. A developer license is cheap ($50?). Put it on a handy box and then away you go.

Short of that, you might be able to work out some combination of backup, re-install, restore (to different DB name), script off db, change collation specified in script, run script to create new DB and then use DTS to copy the data from the restored DB to the new DB.

Am I making sense? It IS getting close to 5:00 here. At 5:00, the value of my responses declines by at least 75%.

Regards,

hmscott|||Or just do what Brett (BCP King) Kaiser suggests.

Brett, is it any coincidence that your initials BK might stand for 'BCP King'?

regards,

hmscott|||Got a chuckle out of that one...

You can point that code at ANY database...

I think you'll need a DELETE in there for table a...I originally had it as a temp table (still want to delete a temp, so that's no excuse...just poor coding habit)

Oh and the table is to log the events for each bcp...

Let us know if it works out...just make sure you got plent of drive space...

I wouldn't do this over a network either...

just copy the files when they're done...|||Many Thanks for both of you (Scott and Brett).
I am browsing Brett's script. I am sure that, it will be a big help to get the export work done successfully. Hope I will get my next 15 instances converted from Latin1_General_CI_AS to SQL_Latin1_General_CI_AS ASAP. Thank you very much.|||15 Instances! On 1 box?

Wow

:eek:

MS SQL 2000 Backup. where r my Indexes?

I used Enterpise manager to make a backup of a SQL Database. When I
restored it on another machine, I did not have any of my indexes. I
checked the backup wizard and I could not find any reference to
including or not indexes.
Are my indexes there, but not built? Maybe a stored procedure I need
to run to rebuild them?
TIA
Rob"R Camarda" <rcamarda@.cablespeed.com> wrote in message
news:d7938492.0407150406.3b114fb0@.posting.google.c om...
> I used Enterpise manager to make a backup of a SQL Database. When I
> restored it on another machine, I did not have any of my indexes. I
> checked the backup wizard and I could not find any reference to
> including or not indexes.
> Are my indexes there, but not built? Maybe a stored procedure I need
> to run to rebuild them?

This is very weird.

I'd try to do a backup and restore using query analyzer just to confirm
everything.

A restore should be an intact copy of the database.

> TIA
> Rob|||A restored database is exactly like the original, although file locations
may be changed during the restore. You might check your backup file to see
if it contains multiple backup sets. By default, the first (oldest) backup
is restored and perhaps that version didn't have your indexes.

You can list the contents of your backup with RESTORE HEADERONLY. For
example:

RESTORE HEADERONLY FROM
DISK='C:\Backups\MyDatabase.bak'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"R Camarda" <rcamarda@.cablespeed.com> wrote in message
news:d7938492.0407150406.3b114fb0@.posting.google.c om...
> I used Enterpise manager to make a backup of a SQL Database. When I
> restored it on another machine, I did not have any of my indexes. I
> checked the backup wizard and I could not find any reference to
> including or not indexes.
> Are my indexes there, but not built? Maybe a stored procedure I need
> to run to rebuild them?
> TIA
> Rob