March 5, 2010 at 9:38 pm
Hi All,
can anyone pls suggest me how to restore multiple databases.Is there any scripts or scheduling task.I want to copy more than 20 databases from one sql server 2005 to other sql server 2005.
thanks,
kumar.
March 7, 2010 at 7:02 pm
I wrote DTS & SSIS Packages to Backup the Production Database, copy it, write a SQL Script to create the Database if it does not exist.
I did this to refresh the QA & Development Environments
Write a simple script to restore the Database, Update Statistics, etc.
You create Package variables to use in the Connection Strings, etc.
Unfortunately I do not have the code anymore.
I'm assuming that you need to backup the Data?
The package consisted of a Backup Database Task.
Task to Copy the Database across the Network.
Task to Kill any connections to the destination Database.
SQL Server will script out the SQL to Create the Database for you and you can use that in a Task.
A Task to restore the Database on T-SQL.
Task to update Statistics, etc.
I does not take a lot of time to create.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 8, 2010 at 1:14 am
Hi All,
I got the solution in this site itself.
Pls find the code:-
/*************************************************************
Script made by : Lester A. Policarpio
Email Address : lpolicarpio2001@yahoo.com
Date Created : September 03, 2007
--=UPDATES=--
January 17, 2008
- Solved outputed value of the @restoredb variable
- Solved the "invalid length parameter" issue
May 6, 2008
- Removed unused variables
- Add the headeronly command to accept non sql backup format
(dbname_db_YYYYMMDDHHMM.BAK)
- Add more comments
May 12, 2008
- Accept Backup Files With Multiple NDFs
May 23, 2008
- Solved the problem when RESTORE HEADERONLY produces more than 1 value
Dec 12, 2008
- Restore Database Script for SQL 2005 now available
--=LIMITATIONS=--
- This script is tested for backup files ".BAK" only
-- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"
*************************************************************/
SET NOCOUNT ON
--Drop Tables if it exists in the database
if exists (select name from sysobjects where name = 'migration_lester')
DROP TABLE migration_lester
if exists (select name from sysobjects where name = 'header_lester')
DROP TABLE header_lester
if exists (select name from sysobjects where name = 'cmdshell_lester')
DROP TABLE cmdshell_lester
--Create Tables
--(cmdshell_lester table for the cmdshell command)
--(migration_lester table for the restore filelistonly command)
--(header_lester table for the restore headeronly command)
CREATE TABLE cmdshell_lester( fentry varchar(1000))
CREATE TABLE migration_lester(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
type char(1),
FileGroupName nvarchar(128),
size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
CREATE TABLE header_lester (
BackupName nvarchar(128) ,
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLsn numeric(25,0),
LastLsn numeric(25,0),
CheckpointLsn numeric(25,0),
DifferentialBackupLsn numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleid int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingId uniqueidentifier,
RecoveryForkId uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged Int,
BeginsLogChain bit,
HAsIncompleteMetaData bit,
IsForceOFfline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBAseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier
)
--Declare Variables
DECLARE @path varchar(1024),@restore varchar(1024)
DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)
DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)
--Set Values to the variables
SET @newpath_mdf = 'D:\data_files\' --new path wherein you will put the mdf
SET @newpath_ldf = 'D:\log_files' --new path wherein you will put the ldf
SET @path = 'D:\' --Path of the Backup File
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
--Insert the value of the command shell to the table
INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension
--Delete non backup files data, delete null values
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'
DELETE FROM cmdshell_lester WHERE FEntry is NULL
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE @migrate varchar(1024)
DECLARE migrate CURSOR FOR
select substring(FEntry,40,50) as 'FEntry'from cmdshell_lester
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''
INSERT INTO header_lester exec (@header)
--Get the names of the mdf and ldf
set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO migration_lester EXEC (@restore)
--Update value of the table to add the new path+mdf/ldf names
UPDATE migration_lester SET physicalname = reverse(physicalname)
UPDATE migration_lester SET physicalname =
substring(physicalname,1,charindex('\',physicalname)-1)
UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'
UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'
--@@@@@@@@@@@@@@@@@@@@
--Set a value to the @restoredb variable to hold the restore database script
IF (select count(*) from migration_lester) = 2
BEGIN
SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)
+' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH MOVE '+''''
+(select logicalname from migration_lester where type = 'D')+''''
+' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')
+''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')
+''''+' TO '+''''+( select physicalname from migration_lester
WHERE physicalname like '%ldf%')+''''
print (@restoredb)
END
IF (select count(*) from migration_lester) > 2
BEGIN
SET @restoredb =
'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+
' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '
DECLARE @multiple varchar(1000),@physical varchar(1000)
DECLARE multiple CURSOR FOR
Select logicalname,physicalname from migration_lester
OPEN multiple
FETCH NEXT FROM multiple INTO @multiple,@physical
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''
FETCH NEXT FROM multiple INTO @multiple,@physical
END
CLOSE multiple
DEALLOCATE multiple
SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)
print (@restoredb)
END
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- Run print @restoredb first to view the databases to be restored
-- When ready, run exec (@restoredb)
-- EXEC (@restoredb)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--Clear data inside the tables to give way for the next
--set of informations to be put in the @restoredb variable
TRUNCATE TABLE migration_lester
TRUNCATE TABLE header_lester
FETCH NEXT FROM migrate INTO @migrate
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@
--Drop Tables
DROP TABLE migration_lester
DROP TABLE cmdshell_lester
DROP TABLE header_lester
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply