February 16, 2011 at 11:40 pm
Hi all ,
I am searching for the script which i can restore backup for sql server 2000 which is of light speed backup ..
I need to restore around 250 Database .
If any one has such script let me know .
Thanks in advance
Rishi
February 17, 2011 at 4:56 am
I'd go to the Quest web site and take a look at the SQL Litespeed documentation. Depending on how you installed Litespeed, it can use standard restore syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2011 at 7:09 pm
Hi Talla
I've done a lot of restores with LiteSpeed so I may be able to help you here.
I have taken one of my old scripts and set it back to SQL Server 2000. I have not tested this so there is a chance that you will need to fix it. I have ripped out a load of code in it, leaving you with the essentials.
The code assumes:
- You want to restore all DBs that exist on your currrent instance. If this is not the case, change the code for the cursor dbloop to select the names for you.
- Your file names are derrived from your database names. You will need to change the location of your backup files unless they are located at X:\Backup!!!!
If you want to do a bit of a test, comment out the line near the end that executes the dynamic SQL - exec (@cmd)
You can then see all the printed commands and see if they look good to you.
This script builds the MOVE statements based on the current locations of your files which is quite handy.
It's not my most elegant script but I hope it helps.
Good luck!
------------------------------------------
use master
go
declare @cmd varchar(8000)
declare @db nvarchar(256)
declare @s-2 varchar(1000)
declare @FILE varchar(300)
DECLARE dbloop CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb', 'master', 'msdb', 'model')
OPEN dbloop
FETCH NEXT FROM dbloop INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FILE = 'X:\BACKUP\' + @db + '.bak' -- The full path to the backup file - the path must be from the DB server's perspective
PRINT CONVERT(VARCHAR(19), GETDATE(), 120) + '> Starting restore of ' + @db + '...'
declare c cursor for
select ', @with = N''MOVE N'''''+RTRIM(f.name)+''''' TO N'+''''''+ RTRIM(f.filename)+''''''''+char(13)
from sysaltfiles f, sysdatabases d
where d.name=@db
and f.dbid=d.dbid
open c
set @cmd = 'exec master.dbo.xp_restore_database @database=N'''+@db+''', @filename=N'''+@FILE+'''' + char(13)
set @cmd = @cmd + ', @filenumber = 1, @with = N''RECOVERY'', @with = N''NOUNLOAD'', @with = N''STATS = 10''' + char(13)
set @cmd = @cmd + ', @with = N''REPLACE''' + char(13)
fetch next from c into @s-2
while @@fetch_status = 0
begin
set @cmd = @cmd + @s-2
fetch next from c into @s-2
end
close c
deallocate c
print @cmd
exec (@cmd)
FETCH NEXT FROM dbloop INTO @db
END
CLOSE dbloop
DEALLOCATE dbloop
PRINT CONVERT(VARCHAR(19), GETDATE(), 120) + '> Done.'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply