LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
0
Guests Online:
101

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Restore database scripted out.

Please wait...

Restore database scripted out.

Postby oraclesqldba on Wed Oct 28, 2009 5:32 pm

I have a database to restore on another server. It's got about 500 files and I need the names of the files to remain the same.

Using either the gui or the scripting tool, it doesn't appear to be able to handle that and instead gives them names like dbname_1.ndf, dbname_2.ndf, etc.

Any idea how or where I can get this going. The other server is on line and I could use that, but I'd rather figure this out because it's a more typical and likely recovery scenario.
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby Emtucifor on Wed Oct 28, 2009 5:47 pm

Could you give some more detail? I'm not clear on what exactly is happening. I don't have any problem with restores changing my file names. Please give some examples and show some of the script you're using.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Restore database scripted out.

Postby oraclesqldba on Wed Oct 28, 2009 6:02 pm

When I copy the backup file to another server and use either the gui or the scripting option in the gui (sql 2008) the interface wants to change the names of the files from the current (March2009.ndf, April2009.ndf, etc) to databasename_1.ndf, databasename_2.ndf, etc.

I can't have this. They need to remain the original names....
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Wed Oct 28, 2009 6:09 pm

I just cooked this up and tested it on my server

here is the test code


  1. CREATE DATABASE testabc
  2. go
  3.  
  4. USE master
  5. go
  6.  
  7. ALTER DATABASE testabc
  8. ADD FILE ( NAME = 'Testdata2', FILENAME = 'f:\mssql\Testdata2.NDF', SIZE = 10, FILEGROWTH = 10)
  9.  
  10. go
  11.  
  12. USE testabc
  13. go
  14.  


the code below will give you all the filegroups which are not the primary and secondary filegroups (log and data file)
so running this
  1. SELECT 'ALTER DATABASE ' + DB_NAME() + ' ADD FILE ( NAME = ''' + RTRIM(CONVERT(VARCHAR(100),name) )
  2. +''', FILENAME = ''' + RTRIM(CONVERT(VARCHAR(100),filename))
  3. +''', SIZE = ' + RTRIM(CONVERT(VARCHAR(100),(SIZE * 8 / 1024)) )
  4. + CASE maxsize WHEN -1 THEN '' ELSE ' MAXSIZE = ' + RTRIM(CONVERT(VARCHAR(100),maxsize)) END
  5. +', FILEGROWTH = ' + RTRIM(CONVERT(VARCHAR(100),(growth * 8 / 1024) )) +')'
  6. FROM master..sysaltfiles
  7. WHERE dbid = DB_ID()
  8. and fileid > 2


will produce ALTER DATABASE testabc ADD FILE ( NAME = 'Testdata2', FILENAME = 'f:\mssql\Testdata2.NDF', SIZE = 10, FILEGROWTH = 10)
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Wed Oct 28, 2009 6:11 pm

do output to text so that you can copy and paste that
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Wed Oct 28, 2009 6:23 pm

If you have growth in % then this won't work according to BOL

growth int
Growth size of the database. A value of 0 indicates no growth. Can be either the number of pages or the percentage of file size, depending on the value of status. If status is 0x100000, then growth is the percentage of file size; otherwise, it is the number of pages.


can't find any with a status of 0x100000 even when I do create them with growth in %
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby Emtucifor on Wed Oct 28, 2009 6:45 pm

I have had problems with growth in %. Those problems may be fixed now, but I would remain suspicious of that method until further study proves that.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Wed Oct 28, 2009 7:08 pm

yeah if you have a 500GB file and you need to grow by 10 % (50 GB) that might take a while
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby oraclesqldba on Thu Oct 29, 2009 2:01 pm

Neat script, but nothing to do with restoring a database. I'm restoring a database. In this particular instance I need to be able to get the restore scripted out, preferably from the backup file since that may be the only thing available to me.

I put the problems with the gui and the native scripting button in the first post....basically, I'm too lazy to type it again.
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Thu Oct 29, 2009 2:53 pm

what I am trying to say is that you can script the Db then apply all these filegroups then when you are trying to restore they should at least match

I did the same thing sometime this year when I migrated a server to 2008 but somehow didn't save the script...I looked in subversion but couldn't find it
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby oraclesqldba on Thu Oct 29, 2009 4:19 pm

Is there a way to read from the backup file to get this info?
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Thu Oct 29, 2009 5:10 pm

the wizard usually gives what is in the backup...however the filepaths can't be found since the don't exist on the server you are restoring to

if you script out the database and the filegroups on the production server...run this on the server you are restoring to...doesn't it then match?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby onpnt on Thu Oct 29, 2009 5:36 pm

For server 1 to server 2 are the logical drive names the same and directory system paths? This shouldn't be a problem unless the directory systems are not found. If that is the case you should be able to restore with move on the filegroups to where they should be (and named).

Take a look at the script here to see the MOVE in action
http://sqlblog.com/blogs/tibor_karaszi/ ... roups.aspx
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Restore database scripted out.

Postby onpnt on Thu Oct 29, 2009 5:48 pm

>> Is there a way to read from the backup file to get this info?

No, not at that level. The best you'll get is RESTORE HEADERONLY
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Thu Oct 29, 2009 5:52 pm

Yes and I had something like that that would move the files for me since it was just doing a replace on drive letter and directory

script was very similar to the one I posted here

here this will generate the moves for you

found it :D

type this into a window
  1. RESTORE DATABASE [testabc] FROM  DISK = N'D:\Backup.BAK'
  2. WITH  FILE = 1,  



paste the output of this below the code above but above the code after the query
run the query below on the server that you want to do the restore at
  1. SELECT 'MOVE N''' + RTRIM(name) + ''' TO N''' +RTRIM(filename)  +''','
  2. FROM master..sysaltfiles
  3. WHERE dbid =DB_ID()


type this
  1.  NOUNLOAD,  REPLACE,  STATS = 10
  2. GO
  3.  



then your whole statement will look like this
  1. RESTORE DATABASE [testabc] FROM  DISK = N'D:\Backup.BAK'
  2. WITH  FILE = 1,  
  3.  
  4. MOVE N'testabc' TO N'f:\MSSQL\data\testabc.mdf',
  5. MOVE N'testabc_log' TO N'f:\MSSQL\data\testabc_log.LDF',
  6. MOVE N'Testdata2' TO N'f:\mssql\Testdata2.NDF',
  7.  
  8.   NOUNLOAD,  REPLACE,  STATS = 10
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Restore database scripted out.

Postby oraclesqldba on Thu Oct 29, 2009 6:00 pm

First, I applaud your effort helping me. clap clap clap. This newly posted should work, or I can modify it a bit --we add new files and filegroups daily. Still wishing in vain I could somehow use restoreheader or something to get what I need.

thanks again.
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby onpnt on Thu Oct 29, 2009 6:05 pm

>> --we add new files and filegroups daily

Why? Wondering the process reason for that design sense you're not (probably) getting any performance increase out of it
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Thu Oct 29, 2009 6:07 pm

unless the file and filegroups are on separate spindles you won't see a performance benefit since you still get latency from one drive
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby oraclesqldba on Thu Oct 29, 2009 6:15 pm

Why new filegroups and files? The customer is using partitioning and merges in a new partition daily.
oraclesqldba
Apprentice
Apprentice
LTD Bronze - Rating: 3
 
Posts: 45
Joined: Thu May 07, 2009 7:25 pm
Unrated

Re: Restore database scripted out.

Postby SQLDenis on Thu Oct 29, 2009 6:18 pm

unless you the fiegroups are on different drives why not putting it in 1 filegroup instead?


example

  1. CREATE partition FUNCTION pfMonths2008 (DATE)
  2. AS range RIGHT FOR VALUES ( '20080201','20080301','20080401',
  3.                 '20080501','20080601','20080701',
  4.                 '20080801','20080901','20081001',
  5.                 '20081101','20081201','20081231')
  6.  
  7. CREATE partition scheme psMonthly2008
  8. AS partition pfMonths2008
  9. all TO ([PRIMARY])
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Restore database scripted out.

Postby Naomi on Mon Apr 02, 2012 7:10 pm

Thanks, that was very helpful. I was able to restore a backup done in SQL Server 2008 R2 to SQL Server 2012 using this technique.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Restore database scripted out.

Postby Naomi on Fri May 30, 2014 7:59 pm

And today again after installing SQL Server 2014
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated