Restore SQL Server database with different name, but let server handle filenames -


i created backup of sql server database this:

backup database [foo] disk = '\path\to\database.bak'

now want restore database different name on same sql server instance, while original database foo still exists. command try use:

restore database [bar] disk = '\path\to\database.bak'

when try in ssms, bunch of error messages telling me foo.mdf , foo_log.ldf cannot overwritten because in use database foo. have read far on , other places, resolve issue must use move option of restore command , specify filesystem path!!! have hard time accepting that: mean, sql server database server, , forces me deal low-level filesystem details perform database restore?!?

is there way how can tell sql server automatically figure out names of .mdf , .ldf files based on name of database restoring to? maybe need change backup command? or maybe can use different, higher-level api "backup database" / "restore database"?

note use case guarantees database restoring not exist.

edit: interactive solutions not option, need perform restore programmatically.

as there appears no elegant solution problem, bit bullet , implemented seems work - now. although not real answer question, thought else might benefit following thoughts.

here rough outline of implementation (tested on sql server 2012):

  1. create temporary database bar
  2. get real logical , physical file names system table select * sys.master_files database_id = db_id('bar')
  3. drop temporary database bar
  4. get backup logical , physical file names backup restore filelistonly disk = '\path\to\database.bak'
  5. make sure each file in backup (obtained in step 4) have corresponding real logical/physical file names (obtained in step 2)
  6. restore database restore database [bar] disk = '\path\to\database.bak'. add 1 move statement each file in backup logical file name backup mapped real physical file name (obtained in step 2).
  7. the restored database still has wrong logical names backup, fix these alter database [bar] modify file (name = 'backup_logical_filename', newname = 'real_logical_filename'). repeat each database file.

the approach of creating temporary database obtain "real" information database files (steps 1-3) allows delegate @ least few things dbms:

  • filesystem locations correct per dba's server configuration (e.g. separate locations data files , transaction logs)
  • mapping of database name file name filesystem name (e.g. if database name contains characters not valid in file names, such ":" character)

when matching real file names , backup file names (e.g. in step 5), file types in sys.master_files , in backup not same:

  • the file types in sys.master_files "rows" , "log" (or 0 , 1 if prefer numerical file type)
  • the file types in backup "d" , "l"

Comments

Post a Comment

Popular posts from this blog

Why can rails not find a route created by a helper? -

i created member route in rails 4: resources :line_items post 'decrement', on: :member end and gave matching method in line_items controller: def decrement @cart = current_cart @line_item = @cart.line_items.find_by_id(params[:id]) @line_item.decrement_quantity respond_to |format| if @line_item.save format.html { redirect_to shop_path, notice: 'line item updated.' } format.js {@current_item = @line_item} format.json { head :ok } else format.html { render action: "edit" } format.js {@current_item = @line_item} format.json { render json: @line_item.errors, status: :unprocessable_entity } end end end but when try make button: <%= button_to 'x', decrement_line_item_path(item) %> i error: no route matches [post] "/carts/25" what gives? your error message is: no route matches [post] "/carts/25" but expectin...
Read more

javascript - jquery or ashx not working -

i using ajax file upload control. it creating html: <form action="/pages/handlers/asyncfileuploadhandler.ashx" method="post" enctype="multipart/form-data" target="ajaxuploader-iframe-1397144545"> <input type="hidden" name="type" value="mindvoice"> <input type="file" id="imagefile" style="display: block; width: 80%; margin-right: auto; margin-left: auto;"> </form> and target is: <iframe width="0" height="0" style="display:none;" name="ajaxuploader-iframe-1397144545" id="ajaxuploader-iframe-1397144545"></iframe> the form submitted using .submit() . my ashx handler: <%@ webhandler language="c#" class="asyncfileuploadhandler" %> using system; using system.web; using nettpals.core; public class asyncfileuploadhandler : ihttphandler { public void processre...
Read more

opencv - DataType<cv::detail::deriv_type>::depth what is it used for -

i wondering why line used in lucas kanade tracker in opencv: datatype<cv::detail::deriv_type>::depth can explain me? in opencv, depth of mat refers type of data contained in mat 's data buffer. represented integer values correspond given data type. these integers commonly abstracted appropriate macro definition (e.g. uchar data represented macro cv_8u ). cv::datatype type-traits class provides method obtain corresponding integer value without having memorize macro means data type. there few cases user code needs use datatype::depth . more common datatype::type . a simple example shows 1 possible use of datatype::depth : cv::mat uchar_data = cv::mat::ones(3, 3, cv_8uc1); cv::mat float_data; uchar_data.convertto(float_data, cv::datatype<float>::depth); // ^^ equivalently replaced // cv_32f macro float_data.at<float>(0,1) += 0.5f; std::cout << float_data << std::endl;...
Read more