Please, HELP! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Please, HELP!

Author

Topic

WolfAr
Starting Member

3 Posts

Posted - 2010-09-29 : 21:32:46

Greetings,

Let me say, right at the start, I did it! Yes, I screwed up. I bolloxed the thing.

Now for the history.

In April of 2008, I was tasked by some friends to put up a server that would host a website and forums. Since I didn't have a lot of money but I did have server licenses available for SQL Server 2005 Standard, I opted to go ahead and make that the backend database for the forums. The overall software config/install is:

Windows 2003 R2 w/SP2
SQL Server 2005 Standard w/SP3
IIS 6.0
php 5.2.1.4
phpbb 3.0x

When I built the server, I didn't know what the master database was oor what it was for. I just assumed it was the default database and configured my phpbb software to use it as the db and everything has worked fine for over 2 years. Now, though, the server hardware needs to be upgraded which means moving the database from the old server to the new. Try as I might, I can not figure out how to transfer the data. Given my druthers, I would like to find a way to get the data out of the master and into a completely new database created for the forum software. I've read everything I can find about backing up, restoring, detaching and attaching and none of it seems to work. No matter what I do, even when it appears to work, the new forums either smply don't display the old data or they crash completely. Is there any way at all that I can get the data out of the master databas on the old server and restore, import, attach.. whatever and get the phpbb software to work? Or have I completely screwed the pooch and need to start from scratch?

For the record, I'm nowhere near a DBA. I can install and configure SQL. I can set up backups and maintenance routines. That's about as far as my proficiencyt with SQL of any flavor extends.

I appreciate and help or suggestions.

Thanks in Advance
Wolf

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-30 : 08:07:39

It's easiest just to copy all the data and metadata to a new user database. Then back that up, restore it to the new server.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-09-30 : 08:09:44

could try this:

1. backup database master
2. restore the backup to a different db, with a different name.
3. go through the restored db and drop all non-phpbb related objects (not strictly necessary, but just to clean up)

good luck!

elsasoft.org

WolfAr
Starting Member

3 Posts

Posted - 2010-09-30 : 20:09:35

Greetings,

Thanks for the responses.

quote:
Originally posted by jezemine

could try this:

1. backup database master
2. restore the backup to a different db, with a different name.
3. go through the restored db and drop all non-phpbb related objects (not strictly necessary, but just to clean up)

good luck!

I've tried that on more than one occasion. All I get is an error that "The backup set holds a backup of a database other than the existing <name> database". If you don't mind me asking the obvious question, what is it I'm missing?

Thanks
Wolf

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-30 : 23:59:06

did u use WITH MOVE and make sure the filenames are different?

Not certain u can do that with master, never tried, but if you can, you definitely need with move option

WolfAr
Starting Member

3 Posts

Posted - 2010-10-01 : 11:29:22

Greetings

quote:
Originally posted by russell

did u use WITH MOVE and make sure the filenames are different?

Not certain u can do that with master, never tried, but if you can, you definitely need with move option

I've only ever tried to restore from the SQ Manager Studio gui. As I said above, I'm far from conversant with sql command line interface. what would that command look like?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-01 : 11:46:06

[code]RESTORE DATABASE myDatabase
WITH
Move 'master' to 'filepath for mdf here',
Move 'mastlog' to 'filepath for ldf here';
GO
[/code] be certain the file paths are not the same as your master database

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources