November 5, 2007 at 8:18 am
New to SQL and I am looking for the easiest way to copy a database that resided in an SQL 2000 server to an SQL 2005 express database. From the management console I right clicked on the database and selected tasks expecting to see the copy database wizard, but no luck. Does it exist on SQL 2005 Express? Can I find it and run it as a separate tool? Can I restore a backup from SQL 2000 to SQL 2005 Express?
Thanks in advance.
Larry
November 6, 2007 at 5:19 pm
I'm not sure in this, I think Copy database wizard is not in SQL Express.
You can detach the database in source server, copy it to destination server and attach it there.
Regards..Vidhya Sagar
SQL-Articles
November 6, 2007 at 6:38 pm
Yes u can copy a database from 2000 and restore the backup copy to 2005. when u successfully restore the copy there in 2005 SQL Server will automatically convert database to 2005. You can later check using 'select @@version'.
Copy Database wizard is not in Express edition.
November 7, 2007 at 9:18 am
I have tried the backup from the sql 200 server and restore to the 2005, but have had little success. I get errors when trying to restore. Do I need to create the entire DB structure in 2005 server first?
November 7, 2007 at 9:25 am
No u dont need to. What errors are u getting. R u using WITH MOVE option while resoring?
November 7, 2007 at 3:32 pm
For me, I make a backup of the database and copy that over to the 2005 Express server. I then just create the blank database and restore. I just did that for a two gig database (I'm running Express on a XP Pro Dell Optiplex 745 box) and it takes about ten minutes to back up, copy, and restore. Plus - this also verifies the backups.
November 7, 2007 at 3:34 pm
Also, make sure you have the logs and data files in the proper location - might be different between the old server and new.
November 7, 2007 at 5:29 pm
Just in case u plan on doing using scripts.
To Take Backup
BACKUP DATABASE TO DISK = 'C:\yourOldDatabase.bak'
To Restore to new location:
First Use this command to get the logical file name. Logical file name cant be changed and has to be the same other wise u will not be able to restore.
1)RESTORE FILELISTONLY FROM DISK = 'C:\yourOldDatabase.bak'
This will give u file name on the first column of ur output.
2) RESTORE DATABASE FROM DISK = 'C:\yourOldDatabase.bak'
WITH MOVE 'ResultFromtopQuery1' TO 'C:\Newlocdata.mdf',
MOVE 'ResultFromtopQuery2' TO 'C:\NewLocLog.ldf'
November 8, 2007 at 4:56 am
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
THis is the error I am getting. Am I doing the backup incorrectly from the 2000 server? As often is the case the help provided was not much help.
We're sorry
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.
******************************************************
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The volume on device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\EARS_tlog_200710310000.TRN' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=3259&LinkId=20476
November 8, 2007 at 7:54 am
Thats a transaction log backup. Take up full backup of ur database and then restore that. Transaction backups r of no use anyways if u dont have full backup and then all the transaction, backup in sequence.
In ur case, u dont need Differential or log backup. U just need one full backup and restore that on ur new server.
November 8, 2007 at 12:40 pm
Sorry, I guess I should have included all error messages. This is the one I get when I just try to restore the DB. This is "WITH" a new default db created.
This is with without an existing Database of the same name on the server.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Restore failed for Server 'LARRYS\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EARS.MDF'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
This is the error I get when I created a blank database prior to doing the restore.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Restore failed for Server 'LARRYS\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'EARS' database. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
November 8, 2007 at 1:42 pm
Can u use RESTORE FILELISTONLY FROM DISK = ' C:\ yourBackUpCopy.bak'
and give me the results of the query
November 9, 2007 at 5:34 am
If I have an empty DB with the same name as the backup file, when I just try to restore the files only, I get the error like above, this is not the correct db. If I try to restore files only without a db in existence, the errror is that I cannot restore to that db.
I just had a thought, many of the errors I was getting when I tried to restore it without a db present was "access denied" I went into the file structure and changed permissions allowing full control but the problem still exists. I am running the on a "Vista Ultimate" box. Vista is constantly asking permission, with other apps, before it makes changes, even if I am running them as an administrator. Could Vista be the culprit?
Larry
November 9, 2007 at 7:52 am
To restore the database from a *.bak file to the empty database you created:
1. open the databases tab and right-click on the database you want to restore.
2. Select Task\restore\database
3. Select "From Device:" select Add - then browse for the location of the database backup file
4. Click on the "Restore: box to select it (verify the backup is Full)
5. Click on Options in the upper left hand side (just below General)
6. Check the "Overwrite the existing database" box
7. IMPORTANT: in the center is displayed the Original File Name and Restore AS name - Change the Restore As path for
both the Logs and Data. This must match with the empty database you created. If this does not match it will not
restore - Then clock OK.
Good luck
November 9, 2007 at 9:12 am
Charlie,
Thanks a bunch. It was the options tab that I was missing. It went like a charm.
Everyone else, I appreciate all of your input, before you know it I may know enough to be dangerous.
Larry
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply