Restore over 200 databases
-
Does anybody know if there is a best practice in restoring over 200 databases.
I could always create a script but I was wandering if there is a best practice.
There is an upgrade involved from SQL 2000 to SQL 2005. The production server is just too old and there is a new server with SQL Server 2005 Express installed.
Any help is apreciated.
Thanx
-
My 2 cents...
First have proper backups or have the old server in running condition so that you can fall back on it in case something goes wrong while moving your databases.
There are some breaking features which will cause issues while upgrading to 2005. Have you checked whether you are using any of those SQL 2000 features?
Detaching and attaching databases may be easier than restoring so many databases. BOL has info abt upgrading using attach-detach method.
If you can use the same disks on your new server then you are saved the trouble of copying so may files from the old server to the new one.
"Keep Trying"
-
I'm a little confused by the question;
a script to backup all the databases is very easy, and so is putting it in a job so it runs on a regular basis is very common....but a restore?
While the script-wise writing a loop to RESTORE is fairly easy like the BACKUP same as above, it's rare that you need to restore, say all 200 databases at the exact same time.
each database is probably part of a different application, so logically, it's hard to think of a scenario that you need to restore all 200 at the exact same time.
Can you explain the requirement, or is this just a general question?
-=edit=-
doh! reread the question, and realized it's part of an upgrade, sorry.
In that situation, I've always done a backup, but then detached, copied and reattached; if there was any problem, i always ahd the backups, but by attach adn reattaching, you don't have to use the MOVE option when restoring a new database; just a bit simpler on a repetative task.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The databases are from an accountancy. They have over 200 customers with each of them a database of their data.
I never had any access to the server and each night each database was backed up to a file (the usual .bak file).
The old server is no longer there and I have to restore all the databases. It's al for the same application just different databases. So all the data and logfiles can be placed in separate directories.
I was just curious if someone had a similar case like this.
-
Are you going to put all the 200 databases in a signle sql server instance?
"Keep Trying"
-
I'm not going to restore 200 databases in one instance. Although it is possible I do know that it's not recommended 😉
I'll probably split them up about 5 to 10 instances. But that's not the case, it's about how to restore these databases as quickly as possible.
I don't have the mdf and ldf files to do a detach-attach, and the old server is no longer accessible. I think it's going to be a long night.
-
the script below might help a little;
Assume you grab your 200 backups in a number of folders/subfolders.
you probably need to RESTORE HEADER ONLY to see the DatabaseName that is associated to the backup file;
all this does is PRINT those commands; I thought about inserting the results of the cursor into a table, and then creating/executing the 'RESTORE DATABASE AS ' + DataBaseName, but i wasn't sure if the backups are all FULL backus, or if any have multiple backups appended inside them...etc.
--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255),DataBaseName varchar(255))
--the source table: yours already exists, but needed for this example.
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@dbname varchar(1000)
--get the list of files to process:
--#########################################
--my folder with 14 backup files.
SET @path = 'C:\Data\'
SET @cmd = 'dir ' + @path + '*.bak /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--#########################################
--another folder with a collection of backups
SET @path = 'C:\Clients\'
SET @cmd = 'dir ' + @path + '*.bak /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--#########################################
--SELECT * FROM ALLFILENAMES WHERE WHICHFILE LIKE '%.BAK'
--ok now a cursor to read the database name for each file:
declare
declare c1 cursor for
SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES WHERE WHICHFILE LIKE '%.BAK'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status -1
begin
PRINT 'RESTORE HEADERONLY from disk = ''' + @path + @filename + ''''
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply