Restore A Set Of Databases From A SQL2000 Server To A SQL2005 Server
Nov 3, 2006
I can restore databases one by one, setting .mdf and .ldf destination paths.
How can I restore all my Databases at the same time?
Thanks
G. Zanghi
ADVERTISEMENT
Restore Sql2005 Db In Sql2000
Oct 23, 2007
Hello,
Restoring a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:
TITLE: Microsoft SQL Server Enterprise Edition
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
Linked Server From SQL2005 To SQL2000
Jan 7, 2007Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
----
USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerNameInstanceName', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc',@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerNameInstanceName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteUser', @rmtpassword = N'remotePassword'
Can A SQL2005 DB Be Hosted On A SQL2000 Server ?
Jan 2, 2007Hi,
I'd like to host a small website created using VS2005 EE with a company that offers just SQL2000 DB support.
My question, can i use the DB created by VS site manager as a DB to the site, if not, is there any alternatives.
Thanks.
Link Server From SQL2005 To SQL2000
Jul 2, 2007I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.
SQL2000 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
8.00.2175 SP4
SQL2005 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
9.00.3054.00
Notice the removal of the two RTRIMs.
Any help welcome
Gertus
Current Connection was on the SQL2005 machine:
Remote Link Server SQL2000 SP4
Working:
SELECT * FROM Entity -- on SQL2005
WHERE
(EntityRef) + Source NOT IN
(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000
AND Source = 'EU'
Not-working:
SELECT * FROM Entity-- on SQL2005
WHERE
RTRIM(EntityRef) + Source NOT IN
(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)
AND Source = 'EU'
Error :: >>
OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".
Can SQL2000 && SQL2005 Co-exist On The One Server??
Sep 29, 2006i realise you can have both SQL2000 & SQL2005 Client software co-existing on one (developement) machine.
but i cant seem to find a definitive answer on whether i can install both complete databases on a server??
we are currently building a new development server and would like to install both version on it if possible??
Cheers,
Craig
Commit Transaction Between Sql2000 And Sql2005 Server
Oct 20, 2006Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000
View 1 Replies View RelatedRestoring A SQL2000 Database To SQL2005 On A New Server
Apr 23, 2008If I restore a SQL 2000 DB into 2005 it upgrades it for me.
When restoring a DB to a new server/instance of SQL, in SQL 2000 I had to carry out the sp_changedbowner procedure after the restore was complete.
Is the equivalent procedure when restoring a SQL 2000 DB to 2005 to go to the schema for the restored database, find the schema with the name of the SQL 2000 db owner and change the schema owner?
OR alternatively run the following query where mark is the db owner in 2000 and lladmin in the new user in 2005 ;
ALTER AUTHORIZATION ON SCHEMA::mark TO lladmin;
GO
Access Linked Server: Works In SQL2000 But Not SQL2005
Aug 14, 2007I am runing Windows 2003 which has both SQL2000 and SQL2005.
The following works in 2000 but not 20005
Code Snippet
DECLARE @sql nvarchar(4000)
set @sql = 'sp_addlinkedserver @server = N''dbAccessPO'',
@provider = N''Microsoft.Jet.OLEDB.4.0'',
@srvproduct = N''OLE DB Provider for Jet'',
@datasrc = N''C:Temppopts.mdb'''
exec sp_executesql @sql
set @sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'
exec sp_executesql @sql
Running this:
Code Snippetselect * from dbAccessPO...MyTable
Produces this error:
Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".
I login using sa for both instances.
I could really use your help.
Thanks.
Problem Connecting SQL2005 Client To SQL2000 Server
Jan 22, 2008OK, I'm stumped and could do with some help. I have a laptop running Vista (64bit) with the SQL 2005 client tools. At home, I have SQL Server 2000 running on Windows Server 2003. I cannot get the SQL 2005 to talk to the SQL Server 2000. At work, we have SQL 2000 running on NT 5.0 and it connects fine. I also have a second laptop with SQL 2000 client tools running on XP SP2 which conects fine to the SQL 2000 instance on the 2003 box.
The vista laptop talks to the 2003 server, so it's not simpy that there is no connection. Any ideas why I keep getting an error stating that the Server might not be accepting remote connections every time I try to connect the Vista machine to the SQL 2000 server? I can't connect either from the SQL management tool, nor via ODBC.
Any ideas would be much appreciated.
What Are The Differences Between CD1 And CD2 Of The SQL Server 2005 Enterprise? And Can I Just Attach The Sql2000 MDF File Into Sql2005?
Feb 4, 2006Hi
I have new bought the SQL server 2005 enterprise, but it have 2 CDs, so what are the differences between CD1 and CD2? and so which one should i install first? or is it necessary to install both two or just need to install one of them?
And about my original sql 2000 database, can i just attach it's MDF file into the sql 2005 engine, or which import wizard can load the sql 2000 MDF into sql 2005? or do i need to keep the sql 2000 engine before do this?
thx
Restore A SQL2000 Db To A SQL7.0 Server
Jul 20, 2005How to restore a database dump file (.BAK) that backed up from SQL2000 server to a SQL 7.0 server?Please help.Saiyou
View 1 Replies View RelatedSQL2005--How To Prohibit BackupOperator To Restore The Database To Local Workstation Server
Sep 25, 2007
We currently built a new SQL2005 server and have serveral sentitive Payroll database hosted in it. I managed to seperate some roles to various users to prohibit them to direct access the data but can access it via front-end application.
The problem is I setup a backup operator with the following rights
Server Roles -- Public
User Mapping
User Mapped to this login / Database membership for
PayrollDataBase_1 BackupOperatorName
db_backupoperator / db_denydatareader / db_denydatawriter / public
However, I try to login using the 'BackupOperatorName' in my workstation and Backup the PayllrollDataBase_1 to a server shared path says E:SqlDataBacupPayrollDataBase_1.BAK. Afterward, I copy it over the network back to my workstation and RESTORE in back to my local SQL2005 instant using the local SQL2005 SA user. All data CAN BE browse
Anyone please help to post how to restrict the Backup operator can only Backup the database but cannot restore to its local SQL2005 instant
Many Thanks in advance
Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow
Dec 27, 2005I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies View RelatedRestore Databases Into A New Installation Of SQL Server
Jul 20, 2005Hi,I want to restore my databases from the old SQL Server installationinto a new installation of SQL Server. My new installation of SQLServer has different data path from the old installation. In addition,the data owners of some databases in the old SQL Server installationare not dbo. I have backups for all the databases in the oldinstallation SQL Server.Can anyone tell me how to restore my databases from the old SQL Serverinstallation into a new installation of SQL Server for my case? (Iwant to use new path for data and log files as well as keep the olddata owners in the restored databases.) Do I need to perform restoreoperation against my master database in the new installation of SQLServer? (The data path for a newly created database has been changedin the new installation of SQL Server.)My idea is:1. Create new databases whose names are the same with the databases ofthe old SQL installation. Set the new databases with the new data pathI want.2. Create new login accounts whose user names and passwords are thesame with the old login accounts. Assign appropriate permissions tothe new login accounts (e.g, the data owner for some database).3. Use Enterprise Manager to perform restore operation against thenewly created databases using the backups from the old SQL Serverinstallation.Please advice.Hai-Chu
View 1 Replies View RelatedSql2000 && Sql2005, Want Localhost To Use Sql2000
Sep 17, 2006i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies View RelatedAttach/restore Databases In SQL Server 2000
Jun 19, 2007Hi,
I've got a question regarding attaching/restoring of DBs and wonder if anyone could help me out.
Does SQL Server 2000 provide a functionality to attach/restore DB automatically? ie, some kind of polling service to attach/restore DB that were detached previously?
Thanks
Danny.
SQL Server 2008 :: Restore Number Of Databases Using Powershell
Aug 15, 2012I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Restore SQL Server Express Databases After A Disaster Recovery
Sep 15, 2006Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
Cant Restore SQL Server Databases: Exclusive Access Could Not Be Obtained
Jul 7, 2007
Hi everyone,
Hope somebody can help me on this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Thank you for your help on this.
Oscar E.
Sql2005 Database Restore From Another Sql2005 Backup File Error.
Dec 15, 2005hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
SQL Server 2008 :: Test Backup Restores (full And Log) - Restore Databases Automatically?
Apr 4, 2015I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.
here are some ideas I have but have not yet tested:
Create a maintenance plan with each 3 jobs:
--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3
I would like to use seperate jobs as to get a report on the duration and status of each job
Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.
Sql2000 & Sql2005
Jul 22, 2007Hi all,
I just wonder can we install both sql2000 & sql2005
into one server, any problems?
thanks for advice.
Possible Between SQL2005 And SQL2000?
Oct 17, 2007I have two SQL servers setup; A - SQL 2005, and B - SQL 2000.
I would like to create an account on Server A which has access to the results from one view via a linked server on Server B. I don't want the user on Server A to be able to access any databases, tables or even columns on Server B with the exception of those contained within this view.
Is this possible, and how would I go about doing it? (Permission-wise)
SQL2005 SP2 And SQL2000
Nov 14, 2007I have just loaded SQL2005(server only)on my production box.
This box also is running SQL2000.
When I install the SQL2005 SP2 it says that if services are locked they will cause a reboot. I stop all the SQL 2005 services but it also want to stop SQL2000 Server for "backward compatiblilty".
I thought installing a separate instance of SQL2005 would not have any effect on my SQL2000 instance.
Thanks for your help
R/P
Possible In SQL2000 - Not In SQL2005 :(
Jan 4, 2008
Hello all
I have an odd problem with sql 2005. I'm a long time sql 2000 user trying to migrate to 2005. My company uses CSVs to import data from our clients db into our SQL db. In 2000 I just create a txt source and pick whichever CSV and transform to a temp sql table. This has always worked just fine with no problems.
So we have a new server with sql 2005 installed. I go through the "migrate dts 2000" wizard and pull in all my DTSs from sql 2000. The first thing I see that needs to be fixed is "connection 1" which is my CSV connection manager along with the flat file source.
I went through both the connection and the flat file source and tried to copy exactly what I do in sql 2000. When I preview the data or even execute this package I get bad data.
Here is what my CSV looks like:
"ACCT ","ACCOUNT DESCRIPTION ","ACCT TYPE","SCH NO","SC"
"9999Z","Balance By Source Offset",9
"87B","UTILITIES(NOT PHONE)U-C",7
72,"ADM-LEGAL/AUDIT/COLL EXP",7
315,"SALES TAX - VEHICLES LOCAL 1",3
"90A","INTEREST(NON FPLAN/MORT)/N-C",7
73,"MEALS & ENTERTAINMENT-ALL",7
"210A","REBATES RECEIVABLE",2,17,4
"5A@","# OF EXT WARR SALES-UC",10
"51N","N/C-VEHICLE INV MAINTENANCE",7
"87D","UTILITIES(NOT PHONE)SVC",7
"90B","INTEREST(NON FPLAN/MORT)U-C",7
317,"ACCRUED OTHER",3,75,4
This is what it looks like when I preview it inside the flat file connection manager:
ACCT ACCT DESC ACCT TYPE SCH NO SCTYPE
9999z blance by source... 9– – "87B" UTILITIES(NO... 7
So it is basically putting two rows of csv data into one row of SQL data. Now I realize my CSV isn't in the correct format because it doesn't have the extra commas at the end of a row IF the columns are null. It seems that 2005 doesn't recognize the carriage return/line feed. It puts the actual ascii characters in the preview. Once I execute the package it changes the ascii characters into blank spaces. My big issue is, why did this work just fine in 2000 but I can't get clean data in 2005? I'm completely clueless now, I have no idea where to go with this. Its impossible to change the output of the CSVs I get. There is literally no other options. I kinda get what I get with them. Why is this happening?
Migrating SQL2000 DB To SQL2005
Nov 25, 2005Any one knows if I can restore a SQL2000 database backup file on SQL2005? and the DB structure will be upgraded?
Many thanks.
Sever With Sql2000 && Sql2005
Jul 23, 2007Hi,
Can a sever with OS win2003 install both sql2000 & sql2005? is there any problems with 2 differenct versions in one sever.
Thank you. :)
Sql2000 And Sql2005 On The Same Machine
Apr 16, 2008I have installed MSDE2000 (name: mypcsql2000, port 1433) and sql2005 Express (mypcsql2005, port 1434) on my WinXP.
I'm developing some application and I need to test both server.
It has worked fine about 1 year, but last month i got error message.
When i'm trying to connect to SQL2005 from my application or import data from Excel, i get error message:
[DBNETLIB][ConnectionOpen (Invalid Connection()).]Invalid connection.
I can access SQL2005 with Management Studio or cmd line (sqlcmd). But not with Excel (or with my application).
MSDE2000 works fine, i haven't any problems.
Last weekend I have reinstalled my WinXP and also MSDE2000, SQL2005 Express.
Worked fine, but when turn on automatic updates ON and afterwards installed latest updates, the problem is here again...
Any idea to solve this problem?
(sorry for my english...)
Upgrading From Sql2000 32 Bit To Sql2005 64 Bit
Mar 26, 2007Hi,
Is there any difficulties I should be aware of? I'm not running DTS and I think my DB is relatively simple. I've googled and not found anything that sticks out as a problem.
We are getting a new server because we want more RAM, and better upgrade options. The hosting company recommended 64bit SQL, so I am thinking of following their recommendations.
I am upgrading from a 2 x p4 2.8ghz xeons to 1 x Intel 5130 dual core 64 bit chip. I also will be going from 2 gigs to 4 gigs of RAM.
Any thoughts I would love to hear.
Thanks!
Why SQL2005 Is NOT Faster Than SQL2000?
Apr 19, 2006Hello!!
we use SQL2000(64bit) and we are considering our system to apply SQL2005
so i tested two SQL-Server on One Machine by installing each instance of them.
The RESULT..
i can't find difference of performance between two instance.
is there no impoving in Database engine?
* i'm sorry, for my poor sentence...
SQL2000 TO AND FROM SQL2005 TRANSFERING
Jan 11, 2007I am a IT Manager.I have question:
There is a database was created on SQL Server.
I want to learn if I restore the database to SQL 2005 then I want to get backup then I want to restore to SQL 2000.
shortly: SQL 2000 sever-> SQL 2005->SQL 2000 SERVER
Can I do that?
If I do that is there someting missed?
http://www.narcv.com/
Replication Between SQL2000 And SQL2005
Jul 26, 2007I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.
Has anyone else seen an error similiar to this? Any insight would be appreciated.
Thanks in advance,
Max