Restore to a new server. Basic question for struggling noob.

[SunnyByfleet]

Hi,

I want to set up an offline copy of our website for experimental purposes, with a view to administering the site in the future. The site uses SQL Server 7 as a back end. I have a SQL backup of the company database.

I have built a webserver running Win2003, IIS6 and SQL Server 2000.

Can I restore the SQL Server 7 backup into the Sql Server 2000 database?

Judging from browsing this forum, it is possible, but when I tried I stumbled at the first hurdle. I have managed to restore the stored procedures used, but no data.

I don't expect anybody to give me the full answer to my problems, but I would appreciate if somebody could point me in the right direction with regards backing up and restoring databases.

Thanks

 

[hmckillop]

As far as i know the backups are compatible.
Though you can restore only the users databases from the SQL Server 7.0 database backup to a SQL Server 2000 server, but you cannot restore system databases (such as master, msdb, model and tempdb) from the SQL Server 7.0 backup to a SQL Server 2000.

http://www.sqlteam.com/item.asp?ItemID=9066

might provide some help

"I'm living so far beyond my income that we may almost be said to be living apart

 

[DrSql]

You should be able to restore the db either through EM or QA, but system tables have changes from 7.0 to 2000. Why do you want to restore the system tables. MSDB keeps informations about all your DTS packages, schduled jobs and history, MOdel if you havent customized it it should't effect in you case, TempDb doesn't have any data, every server restart it will get recreated and Master have Security, Permissions and and syslevel objects. If you are concerned about security, you can serach web site to get scripts to generate logins, including passwords and execute in your new server. If you need further help or any scripts please post back.

Dr.Sql
Good Luck.

 

[SunnyByfleet]

Hi, thanks for the responses. I don't think I need to restore any system stuff. My problem was I thought I had to have a database to restore the backup into, if you see what I mean. The problem there was I didn't know what the database would be called.

I did say that it was noob stuff!

I think it was because I was restoring by media, as opposed to by database.

I reran it per database and it appears to have done something.

My merry voyage will continue and I may well be back later to pester for those script files.

Thanks

 

[DrSql]

You have to create a new DB prior to restore unless you want to attach the production LDF and MDF files.
If you want to proceed that direction you might want to stop the SQL service in Prod (7.0) and copy the MDF and LDF for that db in to new server and attach the file. (dont forget start prod service back)
[tt]
Here is the script to attach the file
EXEC sp_attach_db @dbname = N'dummy',
@filename1 = N'c:\testdb1.mdf',
@filename2 = N'C:\Backup\testdb1.ldf'
[/tt]

Else you can go restore direction from EM or QA.

Here is a restore Script.
[tt]
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
[/tt]



Dr.Sql
Good Luck.

 

[mrdenny]

You don't need to create the database to restore the database. It's a myth that you do.

All creating the database does for you is put the database name in the drop down in the GUI.

One of the first things that the restore database job does is drop the old database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief