Solved

SQL 2005 - reload databases after format

Posted on 2006-10-24
28
378 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:nmxsupport
  • 13
  • 9
  • 5
  • +1
28 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17797033
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
 
LVL 9

Expert Comment

by:gpompe
ID: 17797037
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17797427
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
 

Author Comment

by:nmxsupport
ID: 17798406
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17798499
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
 

Author Comment

by:nmxsupport
ID: 17798556
Ok but wouldnt it cause problems to detach all the system databases on the existing db server?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17798575
You can't detach and re-attach them.  Instead, back them up and restore the backup on the other server.
0
 

Author Comment

by:nmxsupport
ID: 17798621
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17798656
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
 

Author Comment

by:nmxsupport
ID: 17798894
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17799499
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
 

Author Comment

by:nmxsupport
ID: 17817453
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17817478
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
 

Author Comment

by:nmxsupport
ID: 17817491
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:Nightman
ID: 17817542
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17817547
Also, you should install SQL 2005 SP1 on the new server (9.00.2047, instead of 9.00.1399).
0
 

Author Comment

by:nmxsupport
ID: 17817659
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17817816
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
 

Author Comment

by:nmxsupport
ID: 17817861
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17817973
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
 

Author Comment

by:nmxsupport
ID: 17817997
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17818084
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
 

Author Comment

by:nmxsupport
ID: 17818123
Does the link refers to an opertional SQL server?
This server will not run because databases are missing
0
 

Author Comment

by:nmxsupport
ID: 17818137
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
 

Author Comment

by:nmxsupport
ID: 17818220
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
 

Author Comment

by:nmxsupport
ID: 17818336
all sorted, there was a missing procedure which had to be manually imported into the database??!
okay, wonder what lessons have been learnt? :-)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17818402
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 17819054
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
The duplicate key value is (<NULL>) 14 46
t-sql month question 8 43
Sql query to Stored Procedure 6 20
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now