February 6, 2015 at 7:50 am
Hello all,
I have backed up databases from a 2008 server and now I would need to restore them to a 2012 , the only issue is that I need a script bcuz I have over a hundred databases.
any ideas on the best approach ?
many thx
JR
February 6, 2015 at 8:08 am
johnnyrmtl (2/6/2015)
Hello all,I have backed up databases from a 2008 server and now I would need to restore them to a 2012 , the only issue is that I need a script bcuz I have over a hundred databases.
any ideas on the best approach ?
many thx
JR
No matter how you slice it you are going to have to write some code. I would say just write out the script. It won't take that long to write the restore 100 times. Once you get going it will be pretty fast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 8:20 am
My scripting abilities are less than stellar and I need to do this today 🙁
I have backed up all the databases via a maintenance plan on the source server and put the *.baks on the destination.
Just trying to see what I need in the script ... unless I find one
February 6, 2015 at 8:25 am
johnnyrmtl (2/6/2015)
My scripting abilities are less than stellar and I need to do this today 🙁I have backed up all the databases via a maintenance plan on the source server and put the *.baks on the destination.
Just trying to see what I need in the script ... unless I find one
You just need to look at BOL. https://msdn.microsoft.com/en-us/library/ms186858.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 8:25 am
Try this at your own risk. I have used it in the past, but pretty sure it was for single-file/filegroup databases and they didn't have filestream.
drop table #files
drop table #dbfiles
drop table #bdev
go
declare @SourceDirBackupFiles nvarchar(200), @DestDirDbFiles nvarchar(200), @destdirlogfiles nvarchar(200)
select @SourceDirBackupFiles = 'full_path_to_backup_files_here', @DestDirDbFiles = 'full_path_to_data_file_location_here', @destdirlogfiles = 'full_path_to_log_file_location_here'
SET NOCOUNT ON
--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev
(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),
DatabaseBackupLSN 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 bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier NULL,
CompressedBackupSize bigint,
containment bit)
--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
LogicalName VARCHAR(500), PhysicalName VARCHAR(500), TYPE VARCHAR(1),
FileGroupName VARCHAR(500), SIZE VARCHAR(100), MaxSize VARCHAR(100),
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
,TDEThumbprint VARBINARY(32) NULL)
DECLARE @fname varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(1000)
DECLARE files CURSOR FOR
SELECT fname FROM #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname
--Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #bdev)
--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + '''' + char(10) + ' WITH MOVE '
--Get information about database files from backup device into temp table
TRUNCATE TABLE #dbfiles
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + CASE WHEN @type = 'D' then @destdirdbfiles WHEN @type = 'L' then @destdirlogfiles else 'ERROROROROROROR' end + '\' + @LogicalName
IF @type = 'D'
SET @sql = @sql + '.mdf'', ' + char(10) + + 'MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
--Here's the actual RESTORE command
PRINT @sql
print 'GO'
--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2015 at 8:56 am
Thanks for that...
I just want the script to get the database bak files and restore them to the same name as the bak file.
will try the script and let you know
February 6, 2015 at 9:42 am
For our backup jobs, we use a cursor to loop thru a list of databases (no problem with cursors here, since the backup commands run in sequence anyway). You could modify the code to do restores, assuming that you used some sort of naming convention for the backups. BTW, there might be a few minor issues with the code, since I had to pull out a bunch of other stuff that we do with the script.
DECLARE @BackupFile varchar(255)
,@DB varchar(50)
,@Name varchar(30)
,@Location varchar(100)
,@dt datetime
DECLARE DATABASES CURSOR READ_ONLY FOR
(select name from sysdatabases where name in
('abc','def','ghi','etc'))
OPEN DATABASES
FETCH NEXT FROM DATABASES
INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT@Name = @DB + '( Backup )'
SELECT@Location = '\\bkupshare\path\' + @DB +'\'
PRINT @DB
PRINT @Location
IF @IsNotLog = 1
BEGIN
DBCC CHECKDB(@DB);
SELECT@BackupFile = @Location + 'Full\' + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) +
REPLACE(LEFT(CONVERT(varchar, CURRENT_TIMESTAMP, 108), 5), ':', '') + '.BAK'
BACKUP DATABASE @DB
TO DISK = @BackupFile
WITH NOFORMAT, NOINIT, NAME = @Name,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 100
END
February 6, 2015 at 9:48 am
All of the source databases will basically be upgraded from 2008 over to a 2012 sql server and they are named with a long alphanumeric string ( application created ).
I will basically perform a backup of all DB's through the maintenance plan wizard and then restore with the script above with proper parameters.
hope it works 🙂
February 6, 2015 at 9:53 am
johnnyrmtl (2/6/2015)
All of the source databases will basically be upgraded from 2008 over to a 2012 sql server and they are named with a long alphanumeric string ( application created ).I will basically perform a backup of all DB's through the maintenance plan wizard and then restore with the script above with proper parameters.
hope it works 🙂
Surely you aren't testing this in production? I would hope you had tested this upgrade in a dev/test environment. It sounds however like that isn't the case. I wish you the best of luck and hope your weekend isn't lost.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 10:26 am
Sean Lange (2/6/2015)
johnnyrmtl (2/6/2015)
All of the source databases will basically be upgraded from 2008 over to a 2012 sql server and they are named with a long alphanumeric string ( application created ).I will basically perform a backup of all DB's through the maintenance plan wizard and then restore with the script above with proper parameters.
hope it works 🙂
Surely you aren't testing this in production? I would hope you had tested this upgrade in a dev/test environment. It sounds however like that isn't the case. I wish you the best of luck and hope your weekend isn't lost.
Heck no this is in a test environment 🙂
February 6, 2015 at 10:37 am
johnnyrmtl (2/6/2015)
Sean Lange (2/6/2015)
johnnyrmtl (2/6/2015)
All of the source databases will basically be upgraded from 2008 over to a 2012 sql server and they are named with a long alphanumeric string ( application created ).I will basically perform a backup of all DB's through the maintenance plan wizard and then restore with the script above with proper parameters.
hope it works 🙂
Surely you aren't testing this in production? I would hope you had tested this upgrade in a dev/test environment. It sounds however like that isn't the case. I wish you the best of luck and hope your weekend isn't lost.
Heck no this is in a test environment 🙂
Oh good. I was getting nervous for you since you had said you needed this done today and then hoping it works. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 10:39 am
Sorry for the confusion, I just need a plan by today
February 6, 2015 at 10:42 am
Since you mentioned Maintenance Plan let me once again plug Ola.Hallengren.com's AWESOME, FREE, DOCUMENTED and SUPPORTED maintenance suite. Get it, learn it, use it!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2015 at 10:48 am
TheSQLGuru (2/6/2015)
Since you mentioned Maintenance Plan let me once again plug Ola.Hallengren.com's AWESOME, FREE, DOCUMENTED and SUPPORTED maintenance suite. Get it, learn it, use it!! 😎
Good stuff 🙂
February 6, 2015 at 2:04 pm
The script works like a charm.
Thanks again 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply