Backup and restore to new database

Hi I'm trying to take a backup in 2005 and restore it to multiple databases. 
It's part of a test environment we are trying to set up. I basically want to 
mimic the ability to completely overwrite the database which you can, or 
used to able to do, in the GUI. I'm getting errors about the tail log when 
backing up or that the files from the old data don't match the new database.

Sorry I don't have exact errors or code to hand - it seems such a basic 
thing to do I was hoping someone would have a script. It needs to be TSQL as 
I'm going to be running it from sqlcmd.

Cheers, Chris. 


0
Chris
2/10/2010 8:46:09 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
777 Views

Similar Articles

[PageSpeed] 1

Chris,

Lookup the RESTORE command in books online.  Focus on the REPLACE and MOVE 
options which is what you are going to need to overwrite existing databases. 
The REPLACE option tells RESTORE to replace the existing database and 
files - the MOVE option tells REPLACE where to put the new files, if that is 
different from the source system.

Jeff

"Chris Kennedy" <nospam@nospam.com> wrote in message 
news:O3GyUIpqKHA.4492@TK2MSFTNGP05.phx.gbl...
> Hi I'm trying to take a backup in 2005 and restore it to multiple 
> databases. It's part of a test environment we are trying to set up. I 
> basically want to mimic the ability to completely overwrite the database 
> which you can, or used to able to do, in the GUI. I'm getting errors about 
> the tail log when backing up or that the files from the old data don't 
> match the new database.
>
> Sorry I don't have exact errors or code to hand - it seems such a basic 
> thing to do I was hoping someone would have a script. It needs to be TSQL 
> as I'm going to be running it from sqlcmd.
>
> Cheers, Chris.
> 
0
Jeffrey
2/10/2010 11:02:58 PM
Chris Kennedy (nospam@nospam.com) writes:
> Hi I'm trying to take a backup in 2005 and restore it to multiple
> databases. It's part of a test environment we are trying to set up. I
> basically want to mimic the ability to completely overwrite the database
> which you can, or used to able to do, in the GUI. I'm getting errors
> about the tail log when backing up or that the files from the old data
> don't match the new database. 
> 
> Sorry I don't have exact errors or code to hand - it seems such a basic 
> thing to do I was hoping someone would have a script. It needs to be
> TSQL as I'm going to be running it from sqlcmd. 
 
To restore a database to a different one the original (note that the new 
database does have to exist):

   RESTORE DATBASE newdb FROM DISK = '<backupfile>'
   WITH MOVE 'name1' TO 'path1.mdf',
        MOVE 'name2' TO 'path2.ldf',
        REPLACE

To find the names name1 and name2, you find them if you run sp_helpdb
on the source database. They are in the first column. You can also find
them wity RESTORE HEADERONLY FROM DISK = '<backupfile>'




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
2/10/2010 11:03:02 PM
> To find the names name1 and name2, you find them if you run sp_helpdb
> on the source database. They are in the first column. You can also find
> them wity RESTORE HEADERONLY FROM DISK = '<backupfile>'


I think you meant RESTORE FILELISTONLY (although HEADERONLY also returns 
interesting information).

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D1C8C26010Yazorman@127.0.0.1...
> Chris Kennedy (nospam@nospam.com) writes:
>> Hi I'm trying to take a backup in 2005 and restore it to multiple
>> databases. It's part of a test environment we are trying to set up. I
>> basically want to mimic the ability to completely overwrite the database
>> which you can, or used to able to do, in the GUI. I'm getting errors
>> about the tail log when backing up or that the files from the old data
>> don't match the new database.
>>
>> Sorry I don't have exact errors or code to hand - it seems such a basic
>> thing to do I was hoping someone would have a script. It needs to be
>> TSQL as I'm going to be running it from sqlcmd.
>
> To restore a database to a different one the original (note that the new
> database does have to exist):
>
>   RESTORE DATBASE newdb FROM DISK = '<backupfile>'
>   WITH MOVE 'name1' TO 'path1.mdf',
>        MOVE 'name2' TO 'path2.ldf',
>        REPLACE
>
> To find the names name1 and name2, you find them if you run sp_helpdb
> on the source database. They are in the first column. You can also find
> them wity RESTORE HEADERONLY FROM DISK = '<backupfile>'
>
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
0
Tibor
2/11/2010 7:18:25 AM
Tibor Karaszi (tibor_please.no.email_karaszi@hotmail.nomail.com) writes:
> I think you meant RESTORE FILELISTONLY (although HEADERONLY also returns 
> interesting information).
 
I can never remember which is which of those two, thanks for correcting me!


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
2/11/2010 1:56:18 PM
Reply:

Similar Artilces:

Backup
When I try to make a backup from withing Great Plains, I am getting the message: Unhandled Script Exception Script '[693]()' not found Exception_Class_Script_Missing Script_CMD_Userfunc I cannot find anything in the Knowledge Base to help me resolve this. Can anyone help me? Thanks! >-----Original Message----- >When I try to make a backup from withing Great Plains, I >am getting the message: > >Unhandled Script Exception >Script '[693]()' not found >Exception_Class_Script_Missing >Script_CMD_Userfunc > >I cannot find anything in the Knowle...

Reports on working on new install
I have a fresh install that I have been using for the past couple of weeks successfully. However, if I select the Reports icon in the My Work folder I get the following error (help please): Microsoft CRM Unhandled Error Details: Server Error in '/' Application -------------------------------------------------------------------------------- No connection could be made because the target machine actively refused it Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and w...

New Exchange Server #5
I have deployed 2 new exchange servers in my Enterprise. both are Exchange 2k3 SP2 installed on Windows 2k3 RC2. One is a BE (Server B) and the other is a FE (Server C) hosting OWA and RPC over HTTP, working as advertised. These servers are in Administrative Group A which also host an Exchange 2k3 SP2 installed on Windows 2k, well call it server A. Server A is the only Internet Gateway for our Enterprise the Routing Group Master and connector for B, C and D routing (Administrative) groups. I need to decommission Server A and move its duties to Servers B and C. Any recommendations will ...

How do I delete databases that I do not need?
I'm trying to create a database-not working so well. I have made 3 attempts at my database but they are not what I need, they're basically mistakes. I imagine I will have a lot more before I'm done, how do I get rid of the mistakes?? Just locate the mdb files and delete them. Just be sure you know exactly which ones you want to be rid of. Bill "Patrice" <Patrice@discussions.microsoft.com> wrote in message news:82E4A484-6D04-41A7-A6B2-92B17A01DA1C@microsoft.com... > I'm trying to create a database-not working so well. I have made 3 > attempts >...

New dilemma
I have joined the ranks of economy downsizing. I have Vista Ultimate 64 Outlook 2007 computers at home and work. I want to combine Outlook data from the work computer, with the home computer, without losing any emails, contacts, or appointments. The desired result being using the only the home computer for both home and work. Suggestions appreciated. Thank you. How is this a new dilemma and how does your Subject tell us what your post is about? Your question is answered here daily, but you haven't provided enough information for an answer for you. What information store is being u...

Does Outlook's Auto Archive Always APPEND TO or OVERWRITE a Previous Backup?
I ask because in another thread it was stated that auto-archive always appends, but lots of advice on multiple sites caution the user that it always over-writes. If it always appends, then I can use it. If it always overwrites, I can't. I think this is a very important distinction for the user and thus we should know one way or the other which is true append or over-write. Thanks. Yes, it always appends, because it is adding individual items to the = folders in the archive .pst file, not overwriting that file.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programmi...

pst file backup program(pfbackup.exe) installation problem.
Trying to instal the above, but getting errors. 1. "Setup has detected that you already have a version of pfbackup installed on this machine. ....must uninstall.... Answer YES to next qurstion. Click OK. 2. Are you sure you want to uninstall? YES 3. This action is only valid for products that are installed. YES 4. Please uninstall previous versions of PFbackup using the Add/Remove Programs Control Panel. OK No matter how hard I look, it isn't there. Also tried looking under different (administrator) logon accounts. Any ideas? Is there a .dll file somewhere? Sugge...

Group Policy Backup
Is there a way that a Group Policy Object can be backed up on a Server Core machine? Like we do using GPMC in a full dc. Using Powershell? Howdie! Am 01.07.2010 15:50, schrieb Venkat: > Is there a way that a Group Policy Object can be backed up on a Server Core > machine? Like we do using GPMC in a full dc. Using Powershell? GPMC delivers Scripts you can use to back them up. You can also download them seperately: http://www.microsoft.com/downloads/details.aspx?familyid=38c1a89b-a6d2-4f2a-a944-9236999aee65&displaylang=en There should be plenty of scripting that...

Exchange 2003 restore on different host
I have exchange 2003 installed on host A and all users mailbox configured and used for while, now I have installed new exchange 2003 on host B and then restored backup version of exchange SG from host A to host B, can the exchange 2003 on host B mount the restored SG and read all the mailboxes? Appreciated your respond. James On 8 Feb 2007 21:13:31 -0800, "sss" <james.zheng@gmail.com> wrote: >I have exchange 2003 installed on host A and all users mailbox >configured and used for while, now I have installed new exchange 2003 >on host B and then restored backup vers...

backup to a file?
hi, can I backup my exchange 2003 to a file (instead of backing up to a tape drive)?. I will then copy the file to elsewhere. Our exchange server is in a different (distant)location and for some reason the tape drive is not workng. So until that gets resolved, I need to have some other way.(temporarily) I am referring to the 'file' destination option in windows backup. Is this possible and also, will doing this cause any issues while restoring?(if needed) thanks!!! Yes. Just specify a path and ntbackup will create you a bkf file which you can copy whereever. -- Thanks, Bria...

Great Plains Backup
Is it possible to schedule a GP backup? If yes how can this be done? You can schedule backups for system and company databases to occur on the days and the times that you specify. Only a system administrator can open the Schedule Backup window to schedule database backups. To schedule a database backup: 1. Open the Schedule Backup window. (File >> Backup >> Schedule button) 2. Enter a name for the schedule. 3. Select the company to back up. The path where the database will be backed up is displayed. You can modify the path, as needed. 4. Mark the days to ...

New Software Development Articles Directory #2
A new links directory of articles related to software development is "under construction" on http://www.softdevarticles.com/ Please feel free to use it in you next research and to contribute with your articles on XML and .NET ...

Backup
I have DataBase in SqlServer 2008.I Had taken database backup in full mode Around 10 days before. After that i ddi not take any other type of back.At the time of backup one table has around 1000 records.Now Accidentally Row has been deleted Now It has No Data.Before deletion now it has Around 3000 Records Is Their is Any Way to recover those deleted Rows From Full back Up Has the database been in full recovery model all the time since the full backup was taken? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi ...

NT Backup and other backup issues
I'm getting an error when I try to schedule a backup system every day at a certain time. (0x8070005) I'm using an Admin account. ??? Is there any software that can backup files without changing them into a backup format? If I have 1000 files in 100 folders I'd like to see 1000 files in 100 folders in the backup location. Does any backup software do this? And it would have to be done incrementally so you're not backing up the same thing over and over for nothing. "FrankEdwards" <no@frank.here> wrote in message news:op.vbmurnheq6rutr@-...

How do I select duplex printing using my new Dell 968w in Outlook
How do I use my new Dell 968w (wireless) inkject printer with duplexing option to print in Outlook on both sides of page "automatically?" Thank you. "math horizons" wrote in message news:0298E5DB-6BBC-42F0-9BF9-2790A1EB1145@microsoft.com... > How do I use my new Dell 968w (wireless) inkject printer with > duplexing > option to print in Outlook on both sides of page "automatically?" > Thank you. Not an Outlook issue. This is a printer issue in what configuration options are available when you choose to print to that printer. http://accessories....

BACKUPS #2
When I back up my Money 2006 (internally or to a CD) does that backup info include the Portfolio Manager data as well or does the Portfolio Manager info require a seperate backup? Thanks. In microsoft.public.money, hogbreath wrote: >When I back up my Money 2006 (internally or to a CD) does that backup info >include the Portfolio Manager data as well or does the Portfolio Manager info >require a seperate backup? Thanks. It's included. ========= 2006 suggested backup procedure Money 2006 introduces a useful multiple backup file feature. In M06: Tools->Settings->Bac...

Can't restore .pst files in Outlook 2007/Windows XP
Help! My hard drive crashed last week and I have had another installed. I had backed up all my data on my clickfree automatic backup and have managed to restore my pictures and documents, but am having problems doing so with my e-mails and address book. I have the .pst files on my PC but can't access them because I get an error message when I try to open them saying "You are attempting to open a file of type Microsoft Office Outlook Personal Folders (.pst) These fles are used by the operating system and by various programmes. Editing or modifying them could damage y...

Backup file #2
How do I create a backup file to be stored at different directory? How to specify the directory that I want the backup file to be stored? Thanks, Tom Try Tools - Options - Save. This will automatically save a copy of your file every x minutes to the directory of your choice. HTH -- Sincerely, Michael Colvin "tkanasoot@yahoo.com" wrote: > How do I create a backup file to be stored at different directory? How to > specify the directory that I want the backup file to be stored? > > Thanks, > Tom Do I need to set up my file to always create backup? Does this...

Database Reference Formula Help
Let's try this again. I posted this earlier, but somehow my reply never appeared to the original even though a successful confirmation appeared.... So, we'll give it another try. I have a question and answer database that I am maintaining. Each question has an ID number. Additionally, each answer for the question has other relevant questions linked to that answer. What I want to do is to create a column in this database that will tell me for each question ID, what other questions it's linked to, so when it comes time to change a question or answer, I know whic...

Exchange Front End Backup Frequency
Server 2003 Enterprise Front End and Back End Exchange 2003 Standard 2003 AD native/native Front end has empty store mounted for SMTP forwarding. What frequncy would you recommend backing up the front end server since it holds no real changing data? Can I get away with once every week? (hopefully) Backend has sufficient backups every day in place. Thanks for your help, Harry On Sat, 20 May 2006 10:34:14 -0400, "Harry Bates" <harry@NOSPAMrocksystems.net> wrote: >Server 2003 Enterprise Front End and Back End >Exchange 2003 Standard >2003 AD native/native &...

Exmerge Backup
hi we have exchange 2003 on and windows 2003 server. i login as administrator and i want to take backup of all the mailbox that are present useing Exmerge. but Exmerge doesnt recomend to change the permissions of administrator (recieve as and send as permissions which are denyed by default) . i have created a user who has admin previlages and i have created security group with receive and send as permissions set and made this user a memeber of that serurity group. now how do i run exmerge do i have to login to windows using this login or how how do i login to exchange with this user...

How do I add a make a new paragraph in a text cell?
I'd like to format a cell with multiple text lines. Is this possible without splitting and merging rows around the cell I want formatted with multiple paragraphs? I tried inserting a paragraph mark, but it only displays the paragraph mark without actually starting the text that follows on a new line. A manual (forced) line break is: <Alt> <Enter> -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "bearntrail" <bearntrail@discussions.m...

Backup OneNote files to CD
I lost a lot of my one note files when I purchased a new computer. I put them on CD but could not restore them from CD. I did not use the backup facility, as I am a new user of onenote and was unaware of this function. I now use back up regularly. My question is: is this the correct way to save files so I can put them on a CD and be able to restore them back in onenote should the need arise? I don't want to take a chance of losing any more files. -- John -------- Original-Nachricht -------- > I lost a lot of my one note files when I purchased a new computer. I put ...

Backup always
How can I configure Excel so that when it opens ANY file (EXCEL), and save, it will 'Always Create Backups'. That is, want the default behavior for Excel to save a back of any file it saves; not just new ons, and not want to have to check every file before saving, please. (Used to be able to do this:~( You could try something like this, Michael: http://www.vbaexpress.com/kb/getarticle.php?kb_id=456 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Michael Sullivan" <Michael Sullivan@discussions.microsoft.com> wrote in message new...

401K's
My company's 401K plan has been revised. They dropped 3 funds (of 8 choices we had) and added 5. Any shares invested in these funds were transferred into the new (correspondingly similar) funds. I have figured out how to do the transactions to get the account tidied up in Money 2003. Question: Since I'm not interested in historical prices of the 3 discontinued fund options, how do I eliminate the non-essential historical data. I would assume the sells and buys need to be retained to maintain the overall 401K account integrity. Also, how do I prevent Money from updating these 3 M...