SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ran the script but no databases restored


Ran the script but no databases restored

Author
Message
oojimmyoo
oojimmyoo
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 35
Hi,

I have run this script on a laptop using SQL Server 2005 Express and it stated that the commands had completed successfully but no databases were restored from the .bak files.

Does this require the full edition of SQL Server?

If I comment out the "DROP TABLE" commands I can see that the **_lester tables were created within the master db.

I have even created a test db and ran the script against that with drop commands commented out and no records are present within these tables. I have given SQLExpress user full control to the folder containing the backup files.

Any ideas as to why this script is not restoring the dbs is greatly appreciated.

Regards

James
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5999 Visits: 3537
Any ideas as to why this script is not restoring the dbs is greatly appreciated.

Can you post the script?

Shawn Melton
Twitter: @wsmelton
Blog: blog.wsmelton.info
oojimmyoo
oojimmyoo
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 35
Here is the script:

/*************************************************************
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

--=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 varchar(1024),
PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),
size real,MaxSize real)

CREATE TABLE header_lester (BackupName varchar(50),
BackupDescription varchar(100),BackupType int,
ExpirationDate nvarchar(50),Compressed int,Position int,
DeviceType int,UserName varchar(30),ServerName varchar(30),
DatabaseName varchar(50),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,
LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,
BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,
CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,
CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,
MachineName varchar(50),Flags int,BindingId nvarchar(50),
RecoveryForkId nvarchar(50),Collation nvarchar(50))

--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 = 'C:\Virtual Servers\DBBackups' --new path wherein you will put the mdf
SET @newpath_ldf = 'C:\Virtual Servers\DBBackups' --new path wherein you will put the ldf
SET @path = 'C:\Virtual Servers\DBBackups' --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
matt6288
matt6288
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1815 Visits: 1370

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- Run print @restoredb first to view the databases to be restored
-- When ready, run exec (@restoredb)
-- EXEC (@restoredb)



Try uncommenting the EXEC line
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5999 Visits: 3537
I would also ask what method are you using to verify the database was restored?

Example: deleting a table within the database, execute your script, see if the table is put back.

Shawn Melton
Twitter: @wsmelton
Blog: blog.wsmelton.info
oojimmyoo
oojimmyoo
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 35
Sorry - that line was incorrect, the exec command was uncommented. Still no db's restored!

To verify whether the databases were restored I am using MS SQL Server Management Studio Express, right clicking the Databases folder and selecting "Refresh".
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5999 Visits: 3537
I would try the following steps to test if your script is working:
1) Create a test database
2) Create a table and add data (3 or 4 rows maybe)
3) Back that database up
4) Delete the rows of data or the table itself.
5) Execute your script
6) Open SSMS for Express and see if what you deleted is restored.

Shawn Melton
Twitter: @wsmelton
Blog: blog.wsmelton.info
oojimmyoo
oojimmyoo
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 35
Thanks for the suggestions Shawn but still no luck. The deleted rows were not restored but I did not receive any error messages!

Does the database have to exist within SQL Server prior to the restoration script being run or will it restore databases just from the bak file?
oojimmyoo
oojimmyoo
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 35
Has anyone got any ideas why this script has not worked?

Is it because I am using an Express version of SQL?

Is it because the databases do not already exist on this SQL server - they were backed up using a maintenance plan on another MS SQL Server?

Off topic, why does SQL Server not allow a maintenance plan to be created to restore bak files but allow them to be backed up?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search