SQL 2005 - reload databases after format

Hi

I am looking to format the disk in the existing SQL 2005 (std) server. I have ran maintanence plans and have the .bak copy of the database.
Can I use this .bak copy of the d/b or is there something else I need to consider before hitting the format button??

Thanks
nmxsupportAsked:
Who is Participating?
 
NightmanConnect With a Mentor CTOCommented:
1. Make sure that the integrity of all the backups is ok - restore them elsewhere first to test.
2. If you have user level permissions set on your databases, you will need to recreate them on the new server to avoid having orphaned logins and broken permissions (see this link - http://www.databasejournal.com/features/mssql/article.php/2228611)
3. You can also detach the databases (sp_detach_db) backup the .mdf and .ldf files for the user databases, and then re-attach them (sp_attach_db)
0
 
gpompeCommented:
If you are going to format the disk, you need a complete backup of the server. You need to backup ALL the databases (except TempDB) including msdb, master, etc.

Another good thing to do. Create a SQL Script (DDL) of the Database, including logins.
0
 
Scott PletcherSenior DBACommented:
If any DTS packages have been saved to the server -- rare, but possible -- copy those.

If xps/DLLs were added to the server, copy those objects as well.

Also, personally I would copy the last SQL and Windows error logs and keep them, just for reference (and they're small anyway).
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
nmxsupportAuthor Commented:
Okay, I have found the following Transact-SQL commands to backup & restore

backup database northwind to disk = 'c:\northwind.bak' with init
restore database northwind from disk = 'c:\northwind.bak' with replace

And the following to detach / attach databases

sp_detach_db northwind
create database northwind on (filename='c:\northwind.mdf'),(filename='c:\northwind.ldf') for attach

For what reason will I still require the system databases master, model & msdb?
Should all these databases be restored before restoration of the northwind database as outlined above?
0
 
Scott PletcherSenior DBACommented:
Master contains the logins, sys configuration, and other system info.
Msdb contains jobs, DTS packages (optional), maintenance plans (optional), and other system info.


Yes.  You must restore master *first*, then msdb.

Then, if needed, model (you probably don't need model, but it doesn't hurt).  If you choose to restore model, you MUST do that restore BEFORE you restore any user dbs.
0
 
nmxsupportAuthor Commented:
Ok but wouldnt it cause problems to detach all the system databases on the existing db server?
0
 
Scott PletcherSenior DBACommented:
You can't detach and re-attach them.  Instead, back them up and restore the backup on the other server.
0
 
nmxsupportAuthor Commented:
Ok so how does this look?

1. Backup all databases (incl. system) using database maintenance plan
2. As further measure, detach northwind database
3. Format & rebuild server, reload win 2003 & SQL 2005
4. Restore system databases (master then msdb) and overwrite any existing ones
5. copy northwind .ldf & .mdf data files
6. attach northwind d/b to new sql server
0
 
Scott PletcherSenior DBACommented:
You can ignore northwind; it's a sample db, not a system db :-).  You are free to delete it or never create it, doesn't matter.

The rest looks OK.  Be sure to check BOL for how to restore master -- special steps are required (or at least they were on SQL 2K, haven't restored master on 2K5 yet).
0
 
nmxsupportAuthor Commented:
northwind was the pseudonym for the real database name which was too complicated to keep writing anyhow!
all seems overly complicated to restore a single database. mysql is much simpler!
I will proceed to find the "special" steps to restore the master database...
thanks
0
 
NightmanCTOCommented:
Hi nmxsupport

Let's try to simplify this then. Please confirm the following:
1. You have 1 database.
2. You have no DTS packages.
3. You have no extended stored procedures.
4. You do not use any CLR internally in your database.
5. All users or applications connecting to the database do so either via windows authentication or a single SQL login.
6. No explicit object level permissions have been set up internally in the database.
7. Your maintenance plans can be easily reproduced, either by right-click and generate SQL, or by simply rebuilding the backup plan manually.
8. There is no replication.

If this is all true, you should simply be able to back the database up and restore it on another server (you will probably only have to port any specific SQL logins to the new server - windows authentication on the same domain should work just fine, as the SIDs will remain unchanged.). It sounds as if your setup is not overly complex, so replicating it should be as simple as restoring the latest backup (just make sure of its integrity first!).

Suggestion? Back the database up, restore to another machine and see if all of your applications can connect seamlessy. Any database server role members will still have to be re-added though (this was preserved in your old master database).

Us experts do tend to overcomplicate things unneccessarily at times ;)

Cheers
Night
0
 
nmxsupportAuthor Commented:
Hi

Okay, formatted the server yesterday fine, have reloaded SQL 2005, can restore all the data but not the associated security logins as the master database will not restore. When I attempt

The backup cannot be restored because it was created by a different version of the server (8.00.2039) than this server (9.00.1399)

The previous server was SQL 2000 which was upgraded to SQL 2005 to ensure that the data would be in the right format for the migration. The backups were definately carried out after the upgrade, however the backups were created using Enterprise Manager not SSMS. Is this where I went wrong, if not where?

Any way I can restore the master database now?

0
 
NightmanCTOCommented:
You cannot restore a master database between versions.

I suggest that you restore the master database on another (SQL 2000) box, and then port the logins from there.
0
 
nmxsupportAuthor Commented:
The backup is from a server that was SQL 2000 and was upgraded to SQL 2005.
I used a maintenance plan in enterprise manager to get the backup.
0
 
NightmanCTOCommented:
OK - either this is an older backup (prior to the upgrade) or there was a problem with the upgrade.

Can you restore the master database to a different SQL 2000 box? (note that it must be the same service pack version as your previous one - SP4)
0
 
NightmanCTOCommented:
Also, you should install SQL 2005 SP1 on the new server (9.00.2047, instead of 9.00.1399).
0
 
nmxsupportAuthor Commented:
Nope this backup was taken yesterday morning - around a week after the upgrade to SQL 2005.
I will load SQL 2000 onto a virtual server to see whether the master database can be restored there.

Dont know why the backups are reporting the wrong version, is it because I did the backup with enterprise manager rather than the ssms console?

I think I'll upgrade to SP1 once I get my databases sorted out!
0
 
NightmanCTOCommented:
Enterprise Manager should not be able to connect to a SQL 2005 server - if you were able to connect to it with EM (instead of management studio) after the upgrade, that suggests that either the upgrade was not successfull or there is something else wrong.
0
 
nmxsupportAuthor Commented:
That is strange, I remember accessing the database tables through both interfaces.
However that is old news now.
I have built a SQL 2000 SP4 virtual server, I have restored the master database but the service will not start because all of the databases can not be found.

udopen:operating system error 3 (error not found) during the creation/opening of physical device c:\mssql data\mssql\data\model.mdf

fcb:open failed:could not open device c:\mssql data\mssql\data\model.mdf for virtual device number (vdn) 1

this occurs for all databases (apart from master)
on the old server all the databases were in "c:\mssql data\mssql\data" and I have just installed a default installation which uses the "c:\program files\microsoft sql server..." path.

what now??
0
 
NightmanCTOCommented:
OK, let's step back, and see if we actually need to do this.

How many different sql logins did you have on your old server?
0
 
nmxsupportAuthor Commented:
I don't know to be honest.
There is an application running which is trying to communicate with the SQL database - I guess they have hard coded the username & password and they will not give me the details.

I am glad that this is not a critical situation at the moment. I am concerned how SQL would ever be recovered properly in a DR scenario though!

Okay, I have SQL 2000 SP4, I have restored the master database but it's complaining that it cannot find any other databases.
0
 
NightmanCTOCommented:
doesn't matter if it can't find the other databases

you want to pull permissions from the master db - look at the link that I posted in the beginning of this question, and do that against the master database on SQL 2000.

Then you should be able to apply those logins to the new server
0
 
nmxsupportAuthor Commented:
Does the link refers to an opertional SQL server?
This server will not run because databases are missing
0
 
nmxsupportAuthor Commented:
To be honest, I don't think this is possible now and trying to get the manufacturer of the s/w to help linking the security back into the databases
0
 
nmxsupportAuthor Commented:
Okay, it appears I have authentication from the client app to the database but I am now receiving  a "could not find stored procedure" error. I'll have a look into where the stored procedures are supposed to be stored.
0
 
nmxsupportAuthor Commented:
all sorted, there was a missing procedure which had to be manually imported into the database??!
okay, wonder what lessons have been learnt? :-)
0
 
NightmanCTOCommented:
Where was the stored procedure? On the master database?!

That's poor application design - sp's should be self contained in the application database PRECISELY because of this type of scenario.

Lessons learned:

1. Test your backup and recovery strategy regularly in a lab or test scenario. If your new server had to curl up and die today, how would you replicate your production environment?

2. Make sure that any new logins or system/security changes are well documented in a managed change-control process, and test redeployment with these in your backup testing as well. Make sure that 3rd party software that is running on your server is well documented (at least in terms of system configuration), and that this can ALSO be rolled back easily.

3. NEVER take for granted that the backup strategy that you tested 6 months ago still applies - retest REGULARLY.

4. Dry run system upgrades in a test environment before you take down your production system.

5. Experts exchange is a cool place to get help
0
 
Scott PletcherSenior DBACommented:
I'm confused.  I thought the server was already at 2005, based on this from your original q.

>> I am looking to format the disk in the existing SQL 2005 (std) server. <<
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.