SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.

Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK
IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Watch a 60 second video on this subject

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

516 thoughts on “SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

  1. Hello

    i unknowingly deleted the main table in my sql server, now my question is how to retrive is it possible?..

    please help me in this regard.ASAP

    Thanks
    Senthil

    • I need a simple restore command with current date.

      Restore database with today backup file only. can any body helps me in this regards.

      Raghu

    • Hi pinal,
      When i’m doing restore DB then following error occur,Please help me to resolve this error

      The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

      Regards,
      Deivendran.A

  2. Yes. It is possible. (If you have backup)

    Assuming you have backup of the system.
    Take the latest Complete back up restore it.
    Restore Latest differential back up and restore it.
    Restore all the Transaction back up till the point before you delete your table.

    If you do not have backup. After transaction is committed, you cannot rollback. Sorry!

    Regards,
    Pinal Dave (SQLAuthority)

    • sir i m student of 12th science and i made database in visual studio 2005 migrate with visual studio and from app_data folder i have deleted my database by mistakely but i have .bak file of my database then is there any way for backupmy database….its my yearly project from my school and very important to me sir……

      • Yes, you can restore the database using .bak file.
        because there are several ways to restore the database using .bak files.

  3. Hi Pin ,
    Thank you.. I received your wonderful reply..
    But , I dont have any idea about the MDF and LDF .. May you explain something on this topic…
    Kind regards,
    Wilson Gunanithi . J

    • hi thank you very much , but i want to restore mdf and ldf files to my sqlserver2005 ,if you have solution pls tell me
      [email removed]

    • hi Wilson,

      In SQL server when ever the database is created. it creates with two file name extensions. i.e) .MDF and .LDF.

      .MDF is the Database file
      .LDF is the Log file of the database.

      Thanks
      Vijay

      • hi wilson ,
        This is sanjeev ,
        as the above answer that when the database is create then the .mdf and .ldf file is cretaed automatically and if u don’t give the path then that is store as the
        Program files->microsoft sql server->mssql.1->MSSQL->data

        you just see that particular .MDF and .LDF file

        • This is my code for restore database

          user master
          RESTORE DATABASE bannerAds FROM DISK=’D:\bannerAds_db_201004050200.BAK’
          WITH MOVE ‘Data_File’ TO ‘D:\db_files\bannerAds_Data.MDF’,
          MOVE ‘Log_File’ TO ‘D:\db_files\bannerAds_Log.LDF’

          when i Execute it the messages is appear ab belowe :

          Msg 3201, Level 16, State 2, Line 2
          Cannot open backup device ‘D:\bannerAds_db_201004050200.BAK’. Operating systerm error 2(The system cannot find the file specified)
          Msg 3013, Level 16, State 1, Line 2
          RESTORE DATABASE is terminating abnormally.

      • PD Wrote: I need to restore a SQL 2008 DB to SQL 2000 DB.

        PD was not totally true. U can’t restore schema but you can create schema viewing the tables, procs, function or trigers.. etc. after u create schema, u can restore all data from backup. to do this restore your data in 2008 Sql server & make script (‘Insert into t1 Values(v1,’v2′)’) …… for all table and execute this on SQL 2000.
        i wish u can do this.

  4. Hi Pin ,

    I have received the information about the backup devices in SQL Server 2005 that there are three type of devices as :

    Disk / Tape / Pipe — in this, I have some idea about first two.But I dont have any idea about the ‘Pipe’. So reply for the same.

    Sorry for the frequent disturbance.

    I am awaiting for your reply.

    Regards,

    Wilson Gunanithi . J

  5. when i try to backup or restore a database, the following error
    was arise.

    Server: Msg 3101, Level 16, State 2, Line 2
    Exclusive access could not be obtained because the database is in use.
    Server: Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    please could you help me.

      • Hi ,

        Put the database in single-user mode; in SQL Server Management Studio:

        •Right-click on the database name
        •Click on Properties context menu item
        •Click on Options menu item on the left
        •In the grid, scroll down to the bottom
        •Change ‘Restrict Access’ to SINGLE_USER
        now u can complete the restore.

  6. hello,
    i have a question : We have a database and a job which runs a stored procedure on the database periodically. We want to backup our database without management studio or enterprise manager. With TSQL we can backup the database with all tables and stored procedures. But we couldn’t backup the job. Is there any way to backup and restore the jobs in SQL Server 2005, especially without management studio? Or can we create jobs with TSQL via “sqlcmd”? If any, could you give an example, thanks for now..

  7. @Nagaranjan

    That Error Is coming because Application is using the database.
    so to take the backup or restore the datbase u must logout from the application completely.
    Then only YOu will be able to take .

    Sapna

  8. Hi Nagaranjan,

    I was struggling with that issue, also. I found doing a server restart worked great. Now, I’m reading just a logout is needed! I guess I’ll check the logout. It has to be faster, but …

  9. Hi Nagaranjan,
    Check the database name ur trying to restore might be trying to restore on existind DB which is beging accessed by others.
    ~Best Regards
    ~Nag

  10. Hi asoo,
    When u backup the database ,Database & DB objects are copied ,Jobs are server level routines – hence are supposed to be scripted & deployed
    ~Nag
    Eg:
    — Script generated on 7/17/2007 4:21 AM
    — By: sa
    — Server: MEPSERVER103

    BEGIN TRANSACTION
    DECLARE @JobID BINARY(16)
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N’[Uncategorized (Local)]‘) < 1
    EXECUTE msdb.dbo.sp_add_category @name = N’[Uncategorized (Local)]‘

    — Delete the job with the same name (if it exists)
    SELECT @JobID = job_id
    FROM msdb.dbo.sysjobs
    WHERE (name = N’CyclicFill_Proc_Job’)
    IF (@JobID IS NOT NULL)
    BEGIN
    — Check if the job is a multi-server job
    IF (EXISTS (SELECT *
    FROM msdb.dbo.sysjobservers
    WHERE (job_id = @JobID) AND (server_id 0)))
    BEGIN
    — There is, so abort the script
    RAISERROR (N’Unable to import job ”CyclicFill_Proc_Job” since there is already a multi-server job with this name.’, 16, 1)
    GOTO QuitWithRollback
    END
    ELSE
    — Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name = N’CyclicFill_Proc_Job’
    SELECT @JobID = NULL
    END

    BEGIN

    — Add the job
    EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’CyclicFill_Proc_Job’, @owner_login_name = N’sa’, @description = N’No description available.’, @category_name = N’[Uncategorized (Local)]‘, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    — Add the job steps
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’CyclicFill_Proc_Job_Step’, @command = N’Exec CyclicFill
    go’, @database_name = N’LTCDEMODB’, @server = N”, @database_user_name = N”, @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N”, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
    EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    — Add the job schedules
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N’CyclicFill_Proc_Job_Schedule’, @enabled = 1, @freq_type = 4, @active_start_date = 20061201, @active_start_time = 80000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    — Add the Target Servers
    EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    END
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:

    • Hi Nagaraju,

      Does this script enable to loop on jobs ? or Do we need to create the script for each job that we want to copy to another server ?

      thanks
      Sandrine

  11. Pingback: SQL SERVER - Restore Database Without or With Backup - Everything About Restore and Backup Journey to SQL Authority with Pinal Dave

  12. Oh i Am sorry now i guess its working ….

    Well NAGAARGUN this is for you .keep your database in Single user mode ,after that you will able to do so !!

    Keep posted if any further issue you encounter

  13. hi,

    i tried to create new database from existing one with different name but its not working so anybody help me to solve this issue…..

  14. i need to copy all tables from a database to another database, so if it is possible to solve using any stored procedure…..

  15. Dear Concern,
    Hello.

    This is really nice for those who really to be very much close and knows the more technicallity on SQL Server side.

    I would like to know that how we can restore the backup of systems databases. Like Master, Model, Msdb and TempDb. Also How we move TempDb Database in another drive rather than the installed location. If someone can help me out in this regard I would be really greatful for him/her.

    With Regards
    Anu

  16. I do need to clear few issues.

    1. A database size is of 1gb. If tran log backup is scheduled, will that affect the process?

    2. One of my friend maintains a complete backup of a database everyday. Data is always appended. What is the logic behind keeping a disk record of all those backups? At point of setback, if the last backup is restored, won’t that suffice?

    Pls answer immed.

  17. Please help me, please !!!

    Me too getting the same err:

    “Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.”

    I m following the steps below, to restore the Database:

    1. Restore the FileListOnly.
    2. Alter Database to SINGLE_USER mode.
    3. Restore Database with MOVE.
    4. Alter Database to MULTI_USER Mode.

    First 2 steps are working Fine, but at 3rd step, it’s giving the “Exclusive Access to Database” Error.

    Why is it so ?? Where I am making mistake ?? Please if anyone can help me, it’s quite urgent.

    Awaiting for reply.

    Thanks

    Rishi Songara

  18. Sorry, actually my third step goes like this, that is, no double backslash:

    3. Restore Database with REPLACE and MOVE.

    RESTORE DATABASE ASPNETChat FROM DISK = ‘D:\ASPNETChat.bak’ WITH REPLACE, MOVE ‘ASPNETChat_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.mdf’, MOVE ‘ASPNETChat_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.ldf’

  19. Sorry once again:

    3. Restore Database with REPLACE and MOVE.

    RESTORE DATABASE ASPNETChat FROM DISK = ‘D:\ASPNETChat.bak’ WITH REPLACE, MOVE ‘ASPNETChat_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.mdf’, MOVE ‘ASPNETChat_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Log.ldf’

  20. Hi Rishi,

    I believe when you’re trying to restore the DB, another opened instance is there which is resulting an exclusive conflict. first close all the instance, then open only one & try the mentioned procedures, it’ll work

  21. Hi Friend ,
    My name is Plahanov ES..
    Now i am working as a Database Programmer .
    I have to restore a Database backup to SQL Server..
    The scenario is , we have two separate Software running On two different system
    One software backup is in MS Access and Another is In Sql Server
    Two have the same databses tables……
    I want to Update the tables in the sql server Db ..with the datas from MS access databse table
    So can You suggest a Good Logic or any easy method to do it..
    I think of inserting the data to SQL server from msaccess one by one
    Is it a Right way to update it…
    Please Help me to Overcome this situation

    by
    Plahanov
    plahanov@gmail.com

    • Please follow the below steps: –

      1) Open the MS-Access and select the blank document.
      2) Secondly, right click using mouse and select the link tables option.
      3) select the files of type option like that “ODBC databases ()”.
      4) After that select the Machine data source or file data source depends upon your requirements.
      5) Select the database and select the table name which you have required.
      6) the last step is that you will see the link table like that “dbo_your table name”.
      7) Then open the table and changes or updation according to you.

  22. hi i need to create a job in MSSQL 2005 and schedule it everyday; just accessing some table and fetching the data and store it in to a table and rename that table as with the old table.
    i have
    sp_add_job
    sp_add_jobschedule .. stored procedures, now the problem is where to define the sql quries?

    can anyone help me!..
    -vivek

  23. Hi,

    i have one doubt…. i worte on T-sql for back up database…and here i selected all my database name using one varialble like @dbname through one cursor…. and given the path name….my script is working but one syntax error is coming i.e incorrect syntax ‘-‘ near that line no.. because i have one database name like ‘ad-catagories’ so this type of database name unable to backup so i want solution for this error…plz help me anybody…

    thanks and regards,
    satheesh kumar

  24. Hi ..is there any way to perform a selective merge on two or more sql databases…what i mean is i have different tables and stored procedures spread across multiple database files..i have to merge them to a single database file…

    Note: I generated the script file (name.sql) for one of my database ..dunot know how to restore the table structure from it..

    Waiting for ur help…

  25. I’ve got an error on restoring DB that have a DDL trigger.
    The problem is, the trigger need to insert a log record (username,date,scriptchanges) to different DB in a linkedserver.
    When I try to restore the DB on local PC(I don’t have a linked server) I’ve got a message
    “Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction”
    Is there a way to fix this problem ??

    thanks,
    ronsi

  26. hi

    SQLServer2005 backupdatabase in need to move that database to SQLServer2000.

    it giving me …in compatable error ….hw can i move that database to SQLServer 2000

    plz help me pout

  27. hi,
    I want to merge 4 diffrent database server into one database server. can u say me the process or steps.

    i mean 4 diffrent Dbase into one Dbase.

  28. I have the backup of the MS sql server 2005 on my local system with the old date entries in it. Now I want to use this database in my website hence i have created the database with all tables using the script generated.

    However, I am still struggling to upload all old date entries into it. Any help on how to do it would be very much appreciated.

    Cheers,
    Amit…

  29. This is really a very good way to load the mind with database concepts and i have got right solutions for my Window applications which require such services.

    Thanks a lot,
    Gopal Mohan

  30. Hi,
    We have two mssql db, one resides external and other internal. Which replicates each other, when replicating one the table has wiped off, which had huge file download datas along with images. I need to copy from internal mssql db to external mssql db.

    I tried DTS package, which fails after 4 to 5 hours. Data is more than 6 gb, tried restoring from the backup of the internal db, but external server had disk contriant to do this operation.

    Further now have replication problem.

    Please help with both replication and restore of the database.

    Thanks,
    Rao

  31. Hi,

    I have one query Regarding Restore:

    Say I a Job schedule with weekly Full Backup and daily Differential Backup and 1 hour Trnsaction log backup.

    Full Backup and Differential Backup backed up on single backup file!

    If I restore that backup to other Other what is the syntax:

    Pls. remember that bakup file contents both Full Backup and Differential backup

    Regards
    Rajib

  32. Hello Pinal,

    I developed an application (in vb6), for creating scheduled job to periodically backup our database. The Application is gathering all parameters from user and generates job and schedule. Everything is working fine, but size of .bak file is 4 times bigger, than .bak file created manually (without job just backup from enterprise manager)

    What is going on? What should I do to decrease size of the .bak file, created through job?

    Please, give me advise.

    Thanks.
    Sincerely,
    Luda

  33. i have a tape backup which is having almost 7 different databases(SQLSERVER2000) backups, i have to restore 1 table which is located in DB1. what syntax should i use for this …
    its urgent plz….

    thanks:)
    regards
    Aamir..

  34. Hi,

    Im actually doing a daily ETL from our different properties (company) and store the data on a single server.
    The thing is that my backup size has increase inormously and i would prefer to do a selective backup. that is specified which tables or views to be backup instead of doing a backup of the whole database.

    The reason behind is that we are actually doing a daily backup at property level and don’t want to backup the same database again.

    Regards,

    Arvind

  35. Hello sir,

    I am using sqlserver 2000. act i have finished in database backup. i need database restore., what i need mean which place i stored my database backup files that is restored .

    i want network system code. which system store my backup that is restore the database

    help me

    regards
    bala guru

  36. Hi All,

    can anyone tell me, how can i get the restored backup of old date. ie, i have lost a backup of date:12/01/2008, but i am sure that this backup is in my SQL 2000 Server i need to get the only backup of same date. is there any procedure to get it?

    plz help meeeeeee…..

    Thanx in advance…

  37. Hi Pinal,

    I have rather complex problem, consulted a few other DBAs with no luck so far, I am putting it here, may be you can sort it out…

    I have database backup which I want to restore on my hoster’s server (shared hosting on LunarPages), but a database with same name already exists there. When i go on to restore backup, i always get an error. I am restoring this backup to a different database name that already exists there.

    I renamed my database (using sp_renamedb), then took its backup and tried to restore but no luck again. It says Log file is being used by other database owner.

    Is there anyway i can play with LDF files to sort this one ? or how is it possible then ?

    Thanks – Waiting

  38. Hi Kaliem,

    Could you please provide me with the following information:

    1. Have u already tried WITH REPLACE clause in your RESTORE DATABASE command? If not, then use it. If you still get errors, provide me with the following information:

    2. The output of the following command:
    RESTORE FILELISTONLY FROM YourDBBackupFile.BAK

    3. From the Target Server (on which you want to restore the database), send me the output of the following commands:
    USE DBName — (Existing target database name)
    GO
    SELECT name, filename FROM SYSFILES

    NB: I want to know the locations of the target database files

    3. Is the existing target database a copy of the database whose backup you want to restore now or is it some different database altogether?

    Waiting for your reply.

    Regards,
    Munshi Verma

  39. Hi Madhu,

    This is regarding your following request:

    on December 6, 2007 at 6:20 pm32 madhu
    hi
    SQLServer2005 backupdatabase in need to move that database to SQLServer2000.
    it giving me …in compatable error ….hw can i move that database to SQLServer 2000

    plz help me pout
    ————————————————————————-

    You made this request way back in December. Did you get the solution to your query? If not and you still seek help in this regard, then let me know, I can help you.

    Regards,
    Munshi Verma

  40. Hi Pinal Dave,

    I am an Software Engineer. Now I have one query Regarding to take daily Backup with 2500 tables in MS SQL Server 2005 Enterprise edition. how to create a script file using Task Schedule.

    Thanks & Regards,
    V.Sathish.

  41. hello mr.pinal……
    iam a junior DBA..i have a doubts regarding retrival of data tables from the Remote server to the local host using sql server2005..what kind of procedures ,i have to take for this?

    Regards,
    v.vijayakumar

  42. Hi, can i made different restore db from server X (taken over X.x backup) to another server Y whit same database (i.e. name, design, …)?

  43. Hello Pinal

    I want to take backup of my database from one server to another.
    so i am using following command

    BACKUP DATABASE TO DISK = ‘//servername/sharedfoldername/filenam.bak’

    This work fine if i use this command on servers having XP operating system .But if i use this command on the machine having windows 2000 or 2003 server then it gives error.

    Error .
    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘\\iguru\PMS_BackUP\test.bak’. Operating system error 1326

    Please help me to solve this problem as early as possible.

    Waiting for reply.Thanks in advance

  44. HI dude,

    g8 doing.

    i m getting this message while restoring the test.bkp file

    Too many backup devices specified for backup or restore; only 64 are allowed.

    please help me…

  45. Hi pinal,
    can you please help to create a stored procedure to do backup
    with two input parameteres,one the name of database
    and other the type of backup.filname must have date, time in hour, min and second

  46. Hi ,

    I have a backup,and i want to restore it on to a different database on a different server.now the DB onw hich i want to restore my backup is replicated .the type of replication is transation.
    the normal restore wont work since replication wont allow the DB to be dropped,the other way is to first drop the replication restore the DB and then set up replication again.

    Can any body tell me if there is any way to restore without dropping the replication.

  47. hi,
    i am trying to restore a db backup from sql2005 to sql2000. i have carefully follwed these steps:
    -Change database compatible level to 80.
    -Restore the database in sql server 2000 server. Some features of the SQL Server 2005 may not function properly.

    now when i tried to run this
    RESTORE FILELISTONLY
    FROM DISK = ‘C:\demo_2k5.bak’
    GO

    it returned following error:
    Server: Msg 3205, Level 16, State 2, Line 1
    Too many backup devices specified for backup or restore; only 64 are allowed.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    can you please help…
    thanks in advance…

  48. Hi Mary,

    I think it is not possible to restore a Sql server 2005 backup to Sql server 2000. So restore the backup to Sql server 2005 environment, it will work.

    Regards
    Praveen

  49. Hi Dave,

    I have a backup of ‘A’ database which i took it from
    SQL Server 2005 Workgroup Edition, somebody deleted few records in the database. Is there any way to identify who deleted and when it was deleted from the log file.?

    Actually, I restored the backup into SQL Server 2005 Enterprise Edition and I tried with DBCC LOG(‘A’) and could not see any deleted actions.??? how to identify ???

    Regards
    Siva

  50. Hi Shiva,

    Delete commands are logged opreation and if somebody executed Truncate command which wont be logged.

    So for future if you would like to monitor DML commands you can anable C2 auditing or create trigger ,aletrs with DML limitation.

    Find out more details in BOL.

    Praveen barath

  51. Hi Praveen,

    Thanks for your response, Actually only few records were deleted, so they might have not used truncate statement they used only delete statement right..???

    Siva

  52. Hi Pinal Dave,

    I want To Import Data From a Table of one Database To A new Table in Other Data Base . Could you help me in or give me a hint to write a single query for this. I”ll be thank full for this
    .

    Vikas

  53. hi while restoring the database i got below error. what to do????

    “System.Data.SqlClient.SqlError: The tail of the log for database databasename has not been backed up.Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)”

    please reply as soon as possible.
    thanks & Regards
    Ritish

  54. I run above application i get below error

    Msg 3176, Level 16, State 1, Line 1
    File ‘E:\Program Files\Microsoft SQL Server\MSSQL\Data\SCMS_INV.mdf’ is claimed by ‘SCMS_INV_log'(2) and ‘SCMS_INV'(1). The WITH MOVE clause can be used to relocate one or more files.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  55. Hi

    I recently restored a database onto another server and this worked fine however all the views are empty. How can I re-populate the views with data again?

  56. Hi, Pin.
    I am using a cursor in my store procedure and it works great but the problem is it uses 100% the processor. is there any other way or method of using the particular procedure with the same process other than cursor? This will do a great favor for me if do give solution for this matter. Tnx in advanced

    Other scripts will be appreciated.

  57. This is my sample script:

    Declare @curCustomerID integer,
    @curCompanyName nvarchar(50),
    @curContactName nvarchar(50),
    @curAddress nvarchar(100)

    Declare curCustomer CURSOR FOR
    SELECT CustomerID,
    CompanyName,
    ContactName,
    Address
    FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer
    INTO @curCustomerID,
    @curCompanyName,
    @curContactName,
    @curAddress

    WHILE @@FETCH_STATUS = 0 BEGIN

    — TODO Insert record into specified table name

    FETCH NEXT FROM curCustomer
    INTO @curCustomerID,
    @curCompanyName,
    @curContactName,
    @curAddress
    END

    CLOSE curCustomer
    DEALLOCATE curCustomer

    • Try this code without a Cursor

      INSERT into target_table
      (
      CustomerID,
      CompanyName,
      ContactName,
      Address
      )
      SELECT
      CustomerID,
      CompanyName,
      ContactName,
      Address
      FROM Customers

  58. hi

    i want to import data from access to sql server.But acces database updates frequently.i want that updated data in sql server daily.
    Is it a good practice to import data daily using export /import program?i dont think so.pls give me any other solution

  59. Hi Pin,
    can you tell me
    i want to restore sql data but i haven’t Server 2005 Management Studio, i have mydb.bak
    how can i restore it without Server 2005 Management Studio
    pls help me out
    thanks

    • Declare @sql varchar(8000)
      set @sql=''
      select @sql=@sql+
      '
      Backup database '+name+' to disk=''E:\backup\'+name+'_'+convert(varchar(8),getdate(),112)+'.bak'''
      from master..sysdatabases
      EXEC(@sql)

      Create a procedure using the above code and schedule it to run periodically

  60. Hi,

    I want some help.

    I have two different servers. from one i have to take a backup of XYZ DB and restore in another server by different name.
    Both this server is having different IP address.
    How to specify the server name in script

    what i have is this:

    BACKUP DATABASE XYZ TO DISK = ‘c:\XYZ.bak’

    DROP DATABASE XYZ

    RESTORE DATABASE ABC FROM DISK = ‘c:\XYZ.bak’

    go

    But how to specify the server name

    Regards,
    Mahesh

  61. Hello every body

    I have problem in SQL 7.0 database
    the problem is that database size is not increasing after 4 to 5 months though the work is going on.

    thanks
    arjouna

  62. Hi all,

    I deleted 4 rows of data from a table in the data base(sql server 2005),i dont have backup of database,this i did one week back now i want to restore that 4 rows to the same table,i used only delete statement in query analyzer ,is it possible to restore that data,if so plz suggest me how can i achieve that,its very urgent the loss this data made my front end application not to work

    Thanks in Advance………

    Suresh

  63. Why some day one stored procedure take less time and another day same procedure take more time?

    Will you please help me with detailed answer?

    Thanks
    Shailesh

  64. Hi Dave!
    You are right that i can Restore my DB by:
    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
    But what if i have multi MDF and Multi LDF filesgroups…say, i have 200 files all togather
    Now how many time i will type WITH MOVE…WITH MOVE,
    Is there any easy solution to that?
    Thank you for your help.
    Rana.

  65. Hi guys,

    i would like to know is there any features in sql server 2005 management studio that can backup tables and data in script format?

    the purpose is to restore at cilent(customer)’s site in script format instead of database it self.

    really appreciate if you could help me immediately. its very urgent.

    thank you
    ~akram~

  66. hi all,

    i have mistakenly delete my database .ndf file and now the database is not working is i have a database backup and when i restore the databse still my database is not working any idea how to restore my database

    onw thing i want to tell you ppls is that .ndf file is located in E drive and E drive is removed and i copyed the .ndf file from E to F drive.

    help me how to slove this prb.

  67. Hi,
    i want to copy a dataBase from Another Database on the same server with T-SQL instructions without Using DTS

    how can i do this operation ?

    I have a Prod DataBase and a SandBox DataBase
    i want from time to time Copy the Prod DataBase into the SandBox DataBase.
    All this with T-SQL Instruction.

    thx in advance,

    regards,

  68. @Paul.

    The best way to copy the database on the same server, I would say,

    Take backup and make a new copy by restoring the same backup, this is easy, fast and hassle free.

    to do this perform the following actions,

    1. Take backup of the database you want to make copy of.
    2. Use the same backup, and restore the backup with a new name, you want to change the location of the files, using with move option, but this should be easy.

    Very simple.

    Thanks,
    Imran.

  69. Hi Pinal, just a quick question, i have restored a full database backup and now i have to restore 32 individual transaction log files to bring it to the state of completion. Is there a way to write a script that will prevent me from writing individual 32 restore t-log scripts for each transaction?

    thanks

  70. Could you please let me know if any sites / links to help me in Practicing Backs and Restote Strategies. I need to learn and Practice the different types of Backups.

  71. @Sai,

    This is the in detail information for Backups I saw on internet, This explains with screen shots and detailed explanation,

    Go to Google.com type : SQL Server 2000 Backup and Restore

    click the first link, that will give you detail information about backups in SQL Server 2000, 2005 is almost same for backups.

    Scroll down on web page to see screen shots,

    Hope this helps,
    Imran.

  72. Hi

    I am having a problem with restoring a database.

    we are planing for a reporting server.i have to restore the data in to that server. back up size is 79 gb. I have two disks of 40gb each.can any one help me in restoring the database . to two different disk.40Gb data on one disk and remaining 39Gb on other.

    Thanks In adnavce

    Raghu

  73. hi! my name is happy ,

    is there anyone can help me..,

    i would like to ask how to LAN vb 6.0 programs with the SQL SERVER 2000.

    – i developed a program that SQL SERVER 2000 as the backend and i dont know how the client can connect to server.

    – how to mount the .mdf
    – what connection string do i use? ( code )
    – can you please give me a step by step process how to do that..,

    thank you.., any response is well appriciated..

  74. Hi All,

    This is Chitra.
    Please guide me as to how to take backup of a table in another table in SQL Server for the last 3 days.
    Please provide me the script.

    Regards,
    Chitra

  75. hi

    SQLServer2005 backupdatabase in need to move that database to SQLServer2000.

    it giving me …compatability error

    is there any work around

  76. Hi Pinal,
    According to this (http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/) article, i tried to restore my database but i m facing errors. Below is my code.

    RESTORE FILELISTONLY
    FROM DISK = ‘C:\SQLBackup\automate.bak’
    GO

    ALTER DATABASE automate
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    RESTORE DATABASE automate
    FROM DISK = ‘C:\SQLBackup\CAPPS.bak’
    WITH MOVE ‘automate_dat’ TO ‘C:\DB\automate.mdf’,
    MOVE ‘automate_log’ TO ‘C:\DB\automatelog.ldf’

    Thanks in advance
    -Ashish Chauhan

  77. Hi i have probs in restoring my sql server 2005 bak file to 2003 sql server.
    The error says that

    The media family on device ‘D:\dolfaq\dolfaq.BAK’ is
    incorrectly formed.SQL Server cannot process this media family.
    RESTORE DATABASE is terminating abnormally

    is there any other way to crack this
    plz help
    thanks
    regards
    N.Balaji

  78. hello dear

    Is it is possible to restore sql server 2005 complete backup
    in sql server 2000.

    if yes….. pl tell me the procedure to resotre it assp.

    Thanks.
    Dhananjay
    Software Developer

  79. i have script of database.
    my database deleted mistakenly.
    before delete i generate script.
    can i retrieve tae database.

    please help me!!!!!!!!!!!!!! immidiately

  80. my database is in sql server 2000

    and where to run this code as you suggested
    //////////////
    RESTORE FILELISTONLY
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    GO

    Step 2: Use the values in the LogicalName Column in following Step.

    —-Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    //////////////

    please help i am newer to sql server.

  81. Is there any option to restore database from “.MDF” file only? I do not have “.LDF” file.

    How it will restore ? Is this possible or not?
    Thanks in Adv

    Haque

  82. Hi Everyone,

    I have .bak files which getting copied from a source server and brought into the target server. However, I have 2-3 .bak files for each database with different time-stamps. like:

    webdata_backup_200810311030.bak

    I want to load the database with the latest backup file (latest time-stamp). How can i acheive this?

    this is the script i am using:

    use master
    go
    Restore database WebData from
    disk=’J:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\WebData\*.bak’
    with Replace,
    MOVE ‘WebData’ TO ‘E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData.mdf’,
    MOVE ‘WebDatalog’ TO ‘E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData_log.ldf’
    go

    Thanks for your help!

  83. Hi,

    I generated bak file using this query from DATABASE Test ,

    BACKUP DATABASE Test TO DISK = ‘Test.bak’ WITH INIT

    For restoring I gave another DATABASE restoreDB

    RESTORE DATABASE [restoreDB]
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak’
    WITH REPLACE,
    MOVE ‘restoreDB_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\restoreDB.mdf’,
    MOVE ‘restoreDB_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\restoreDB.ldf’

    then I got this error.

    Logical file ‘restoreDB_Data’ is not part of database ‘restoreDB’. Use RESTORE FILELISTONLY to list the logical file names.
    RESTORE DATABASE is terminating abnormally.

    The main aim is to copy entire DATABASE Test to another DATABASE restoreDB.

    Thanks

  84. – Restore database from file
    —————————————————————–
    use master
    go

    declare @backupFileName varchar(100), @restoreDirectory varchar(100),
    @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
    @databaseDataFile varchar(100), @databaseLogFile varchar(100),
    @databaseName varchar(100), @execSql nvarchar(1000)

    — Set the name of the database to restore
    set @databaseName = ‘poc’
    — Set the path to the directory containing the database backup
    set @restoreDirectory = ‘D:\libs\sql\PRACTICE_DB_INSTALLER\’ — such as ‘c:\temp\’

    — Create the backup file name based on the restore directory, the database name and todays date

    set @backupFileName = @restoreDirectory + @databaseName + ‘-‘ + replace(convert(varchar, getdate(), 110), ‘-‘, ‘.’) + ‘.bak’

    — set @backupFileName = ‘D:\DATA\BACKUPS\server.poc_test_fbu_20081016.bak’

    — Get the data file and its path
    select @databaseDataFile = rtrim([Name]),
    @databaseDataFilename = rtrim([Filename])
    from master.dbo.sysaltfiles as files
    inner join
    master.dbo.sysfilegroups as groups
    on

    files.groupID = groups.groupID
    where DBID = (
    select dbid
    from master.dbo.sysdatabases
    where [Name] = @databaseName
    )

    — Get the log file and its path
    select @databaseLogFile = rtrim([Name]),
    @databaseLogFilename = rtrim([Filename])
    from master.dbo.sysaltfiles as files
    where DBID = (
    select dbid
    from master.dbo.sysdatabases
    where [Name] = @databaseName
    )
    and
    groupID = 0

    print ‘Killing active connections to the “‘ + @databaseName + ‘” database’

    — Create the sql to kill the active database connections
    set @execSql = ”
    select @execSql = @execSql + ‘kill ‘ + convert(char(10), spid) + ‘ ‘
    from master.dbo.sysprocesses
    where db_name(dbid) = @databaseName
    and
    DBID 0
    and
    spid @@spid
    exec (@execSql)

    print ‘Restoring “‘ + @databaseName + ‘” database from “‘ + @backupFileName + ‘” with ‘
    print ‘ data file “‘ + @databaseDataFile + ‘” located at “‘ + @databaseDataFilename + ‘”‘
    print ‘ log file “‘ + @databaseLogFile + ‘” located at “‘ + @databaseLogFilename + ‘”‘

    set @execSql = ‘
    restore database [' + @databaseName + ']
    from disk = ”’ + @backupFileName + ”’
    with
    file = 1,
    move ”’ + @databaseDataFile + ”’ to ‘ + ”” + @databaseDataFilename + ”’,
    move ”’ + @databaseLogFile + ”’ to ‘ + ”” + @databaseLogFilename + ”’,
    norewind,
    nounload,
    replace’

    exec sp_executesql @execSql

    exec(‘use ‘ + @databaseName)
    go

    — If needed, restore the database user associated with the database
    /*
    exec sp_revokedbaccess ‘myDBUser’
    go

    exec sp_grantdbaccess ‘myDBUser’, ‘myDBUser’
    go

    exec sp_addrolemember ‘db_owner’, ‘myDBUser’
    go

    use master
    go
    */

    • I am trying to run your script in sql server 2008 enironment and i am getting the errors when procedure trying to kill active database connection i am attaching error

      Msg 103, Level 15, State 4, Line 1
      The identifier that starts with ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
      from master.dbo.sysprocesses
      where db_name(dbid) = @databa’ is too long. Maximum length is 128.
      Msg 105, Level 15, State 1, Line 1
      Unclosed quotation mark after the character string ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
      from master.dbo.sysprocesses
      where db_name(dbid) = @databas’.
      Msg 102, Level 15, State 1, Line 1
      Incorrect syntax near ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
      from master.dbo.sysprocesses
      where db_name(dbid) = @databa’.

      Could you please helpme out

  85. ‘D:BackUpYourBaackUpFile.bak’

    and

    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

    ——————————–

    BackUpYourBaackUpFile, YourMDFLogicalName, YourLDFLogicalName, DataYourLDFFile

    what do all this imply …. can someone give any example…

  86. Hello, I have a corrupt VMWare session. withinit an SQL Server 2000 Database. There are jobs in it that a nead. How can i extract thos jobs. I can acces the volumes, so i can see the database files, but where to find the jobs i have created.

    Thanks in advance.

    Eric

  87. Hi,

    Can someone help me out with this please:

    I am trying to restore a db (backupcopy from my pc) on a network server.

    When I try to select the filelist name from the file option on restore, I get only the server folders/files. How do I pick up the .bak file from my pc?

    Thanks in advance,
    Priya.

  88. Hi Priya,

    To restore your back up on Network server, you need to copy your .bak file on the server OR map Server drive to your PC.

    Server will not allow you to select file from Network for Restore.

    Thanks,

    Tejas

  89. Hi Imran and Tejas,

    Thanks for your replies.

    I tried to restore the db like this:

    RESTORE DATABASE
    FROM DISK = ‘N\\\\dbbackup.bak’

    WITH MOVE ‘dbname.mdf’ TO ‘E:\\dbname.mdf’,

    MOVE ‘dbanme_log.ldf’ TO ‘E:\\dbname_log.ldf’

    With Replace

    When I give ‘with replace’ it gives error on that replace statement. When I remove the ‘with replace’ command it says:

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I tried giving : exec sp_who

    It gives only my pc’s name. Can someone help me out with this please? I had been trying to restore this for the past 2 days..

    Then I tried to backup the db from the local instance in the syntax which Imran gave me. But then it says, incorrect syntax near ”.

    I had been trying to fix this for quite sometime now.. please help..

    Thanks

  90. Ok, I had a problem with giving the database name with dot inbetween. Thats why it said that error. Now after enclosing the db name within quotes, Now I tried giving what Imran said. It gives the message :

    Cannot open backup device. Operating system error 53(The network path could not be found).

    I hv verified the network path.

    Can you confirm where I have to give the $ symbol. Right after the drive? Like this: \\\E$\\backupname

    Thanks.

  91. @ Priya.

    I apologize for giving you wrong advice, I think I understood your question completely wrong, script that I gave you takes backup of any database on network drive.

    But in your question you asked to restore database backup from network drive.

    Look at this webpage once :

    http://midnightdba.itbookworm.com/index_files/Page380.html

    One important note : backup you are trying to restore from different machine, should be in Shared folder. ( folder marked with a hand symbol), Only then SQL Server can access that folder. If your backup is not in a shared folder, then SQL Server cannot restore backup.

    Error1 : Exclusive access could not be obtained because the database is in use.

    Explanation:
    Generally you will see this message when you are trying to restore a database that is online. Or if you are trying to over write .mdf file on Operating System that are being used by database, this usually happens when you are trying to restore.

    You need to use with replace with Restore command to point it to different location.

    Backup will have some information in it, it remembers from which location it was taken backup. And when you try to restore it will try to restore database in the same location.

    Since your backup is residing in other servers, file location of the two servers might not match, so you need to use WITH REPLACE command with , WITH RESTORE command

    The Shared folder path is generally like this,

    \\Servername\Foldername\filename

    Servername – Name of the Server
    Foldername – Name of the folder ( this folder must be shared folder , folder will have a hand symbol )
    filename – name of the backup file.

    Regards
    IM.

  92. Hi Imran,

    Thanks for ur reply.

    I did try out this :

    RESTORE DATABASE
    FROM DISK =N’\servername\C$\sharefolder\dbbackup.bak’

    WITH MOVE ‘dbname.mdf’ TO ‘E:\\dbname.mdf’,

    MOVE ‘dbanme_log.ldf’ TO ‘E:\\dbname_log.ldf’,

    Replace

    But I still keep getting this error:

    Cannot open backup device. Operating system error 53(The network path could not be found).

    Thanks in advance..

  93. I was able to find a solution. Thought I would share it with you all:

    First had to download the Microsoft SQL Server Database Publishing Wizard 1.1 from the following location:

    http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

    I was able to take a backup of my local db through the above wizard and stored it in my local pc. It saves it as a .sql file.

    Then, login to sql express management studio with the server login credentials–click on File->open and open the file which you have just saved thru the publishing wizard. Execute the query. The db was restored successfully.

    This may not be the easiest way to restore on network from local pc. But then I had to do it only this time and not often. So, it worked for me!

    Regards,
    Priya.

  94. hi all. help me.

    Example:

    select * from invoice

    surname Lastname
    ………………………………..
    Michael Bolton
    George Michael
    Stiven Harry

    How insert new field? how do insert ordinal number to new field?

    id Surname Lastname
    ………………………………………
    1 Michael Bolton
    2 George Michael
    3 Stiven Harry
    . … …
    . … …
    . … …
    +1

    HOW DO IT?????????

  95. Hi Dave,

    You are simply great. Thank for all your help. Quick question. I was following your TSQL restore steps for restoring Adventure works database and got this error
    “The media set 2 media families but only 1 are provided. all members must be provided.

    what does it mean? Please advice.

    Thanks
    JG

  96. @JG

    Error : “The media set has 2 media families but only 1 are provided. all members must be provided.”

    Explanation: When you take backup using SQL Server Management Studio Interfaces ( Right Click Database Name, AllTask , Backup ) , You see a section called on interface called Destination , this is where you backup file will be stored. In this section you need to give Path where you want to store backup file and name of the backup file itself.

    Most of the times you will see a location by default in this Destination Section and if you click Add and add other location and file name. You will see two location and two file names in Destination Section if you then take backup (Click Ok ).What really happens is your backup has now been split into two parts which is now at two different locations saved in two different file names.

    When you try to restore database from using that backup copy, you need to mention two backup file located at two different locations. If you give any one backup file when restoring then you will see this message,

    “Error : The media set has 2 media families but only 1 are provided. all members must be provided.”

    Which says, there are two backup files for this backup copy or this family of backup contains two backup files, when restoring those backups give both the file names and location where they exists.

    How to avoid this error: When you take backup from SQL Server Management Studio using interfaces, make a habit to delete old location, and add a new file location and file name and then take backup Or else , use scripts and just give one location with a file name.

    Hope this helps,
    IM.

  97. How to restore a Database?I got some error while restore.
    that is
    The media set has 2 media families but only 1 are provided. error.Is there any method to rectify that problem?

  98. @Nirmala,

    Read reply above your reply, I have explained this issue in detail,

    what error says, when you took backup, you have taken in two different files, and when you restore you have to give both the files, but error says that you are using one of the two backup files.

    Solution: Use all backup files to restore database.

    Regards,
    IM

  99. Dave,

    In transactional replication , Our team member deleted all records in one table accidentally from publisher. Same commands also replicated to subscriber before we notice. We recovered most of the data using backup. Is it possible to recover from LOG file. Publisher DB in full recovery model.

    Please help on this issue.

    Many Thanks
    Renu

  100. @Renu,

    Yes It is possible, if you know the time when delete command was executed.

    You can do a point in time restore using transactional log back up ONLY IF You already have/had taken a full backup of the database before executing delete statement.

    What you need to do now.

    1. Take transactional log backup of the database NOW.

    2. You should have Fullbackup of the database that was taken prior to executing delete statement. Restore this full backup with NO_RECOVERY with a new name (just to reduce errors).

    3. Restore recently taken (only one) differential backup with NO_RECOVERY ( if any) and all transactional log backup’s that were taken between FULL Backup (If no Differential backup was taken) /Differential backup and recently taken Logbackup (if any). DO not RESTORE last Transactional backup (at this time, we will apply it in step4) , apply all other transactional log backup’s in sequence with NO_RECOVERY.

    4. Now apply the recently taken transactional log backup with RECOVERY using STOP AT Function, and specify a time prior to the time of executing delete statement.

    Instead of doing step1 – 4 using scripts, it is easy to do these steps using interfaces.

    Try this on your Test Server.

    Regards,
    IM.

  101. I Restored a Production Backup File to my dev environment DB. Now we got to know know that our client is working on forms in this DB. Can i restore the DB to the point previous point of time. we are not having Backup file of Dev environment

  102. Question:
    My Boss is convinced that a network file server back up is good enough to backup SQL server databases. How can I convince him back that Database Back up is different from file server back up

  103. @Mac,

    When sql server is running all its .mdf and .ldf files will be locked by sql server and no application can copy or take backup of those files, when you schedule a file system backup I am sure it cannot take backup for these .mdf and .ldf files on operating system.

    For most of the products this is true, that they cannot taken backup of .mdf files and .ldf files when sql server is using those files.

    Best way to use your file system backups with SQL Server backup is,

    First take SQL Server backup’s on operating system and then take backup of whole server using file system, that way you are taking backup of sql server native backups too which can be used when restoring,even though your .mdf and .ldf might be currupted, you still will have a good sql server backup.

    Just for the sake of your Boss, ask Backup team to restore whole server, and see if you can have your sql server databases up.

    Regards,
    IM.

  104. Hi Pinal,
    Thanks for this wonderful blog. I am referring to this over a period of time. I have a question for your help as:
    I have a large database with 50Gigs. I have setup full backup, differential backup and transaction log back up.

    The data base is uptodate and in running status. I am looking for an old copy of the database from full+diff backups to get some changes in the schema/stored procs etc.

    The point is that I want to restore this database with a different db name on the same server without disturbing the current running original database. I could restore the full backup. And now when I try to implement the differental backup [backup was done with a different db name], to the newly created db. that fails mentioning that NoRecovery is not specified.
    What may be wrong with me here. Thanks in advance
    Pawan Bansal

  105. Pawan,
    Are you restoring the full backup with norecovery
    then restoring the differential with recovery?
    You need to restore the full with norecovery then the diff with recovery.

  106. Hi,

    I have MS Access DB Backup.I need to store the backup to Sql server 2005.How to do this ? any one know the answer.pls tell me and send the details about step by step….

    Regards

    Komathi.S

  107. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  108. Hi,

    How to resotre the SQL server 2005 backup file into SQL server 2000.

    or
    How to take the backup from SQL server 2005 for SQL server 2000.

    can you give solutions..

    with regards
    M.Mohan Kumar

  109. Hi All,

    I’ve SQL Server 2000 and I’m trying to restore all production databases (around 50) to UAT environment, I’ve stored proc. where I create a cursor for sysdatabases after filtering out system databases then I iterate thru all databases, find relevant backup set and restore it. It works fine when I execute that stored proc in query analyser but when I execute same from SQL agen it fails after restore 8-10 databases. Both SQL Server And SQL Server Agent running under a domain account which is part of domain admin group. Can any one help with this.

    Regards,

    Ismail

  110. HI,

    I TRY TO RESTORE DATABASE I JUST FLASH ERROR “RESTORE DATABASE is terminating abnormally.”SO WHAT IS SOLUTIONS.

    PLS GIVE ME SOLUTIONS .

  111. Thanks for this! Very easy to follow instructions that allowed me to restore database back-ups taken from a hosted environment and restore them locally so I could test SQL Report writing with Crystal Reports.

    I had read other blogs and forums but did not find easy to follow instuctions like these. Worked the first time through.

    Thanks Again!!

  112. Hi!

    I have nos. of full backup for different days of week on LTO Tape. Now, I want to restore one particular backup from this LTO Tape. LTO tape contains 5 nos. of different days backup. How can I restore selected backup from TaPE.

    Pls. provide some solution.

  113. hi pin…..
    i need a answer……
    using this query………

    restore database padmin from disk=’C:\manish\padmin.bak’

    i got following errors

    Server: Msg 5105, Level 16, State 2, Line 1
    Device activation error. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin.mdf’ may be incorrect.
    Server: Msg 3156, Level 16, State 1, Line 1
    File ‘padmin’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin.mdf’. Use WITH MOVE to identify a valid location for the file.
    Server: Msg 5105, Level 16, State 1, Line 1
    Device activation error. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin_log.LDF’ may be incorrect.
    Server: Msg 3156, Level 16, State 1, Line 1
    File ‘padmin_log’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin_log.LDF’. Use WITH MOVE to identify a valid location for the file.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    and when i use this one……

    restore database padmin from disk=’C:\manish\padmin.bak’
    WITH
    MOVE ‘padmin’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL\Data\padmin.mdf’
    MOVE ‘padmin_log’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL\Data\padmin_log.ldf’

    i got………

    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near ‘MOVE’.

    what should i do????????????

  114. Hi
    i want answer of the following question

    Whenever i restore database using the query

    RESTORE DATABASE ID FROM DISK =’D:\ID1.0.BAK’

    I GOT FOLLOWING ERROR

    Msg 3102, Level 16, State 1, Line 1
    RESTORE cannot process database ‘ID’ because it is in use by this session. It is recommended that the master database be used when performing this operation.

    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  115. @ Manish Gupta,

    Books online is helpful most of the times,

    If you see books online, there is a simple example explained, I am copying the same example below,

    RESTORE DATABASE MyAdvWorks
    FROM MyAdvWorks_1
    WITH NORECOVERY,
    MOVE ‘MyAdvWorks’ TO
    ‘c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf’,
    MOVE ‘MyAdvWorksLog1′
    TO ‘c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf’
    RESTORE LOG MyAdvWorks
    FROM MyAdvWorksLog1
    WITH RECOVERY

    Try using similar script for your database.

    You are missing NORECOVERY option for data file and RECOVERY Option for log file.

    Recovery option for log file is default option, so you need not specify with Recovery, but I think you do need to specify with NORECOVERY. For more info, please refer to books online.

    @Mayur
    Looks like you are doing two mistakes. This is what I could say looking at the error message you provided.

    1. You are using database ID and you are trying to restore the same database (i.e. Database Name : ID). That is why error message says, use master database instead of ID database and execute your restore script.

    2. Even if you use master database and execute that restore script that you provided, you will see second error message, it is saying, Database ID is already in use, there is already a database existing on Sql server with name ID. you cannot restore ( which internally created a new copy of the database).

    you can do either one of these two things,

    1. Use with Replace option, and this will replace existing files used by the database “ID”, you will loose your present copy of the database.

    2. First drop the database, “ID” and execute your restore script.

    Important Note : Make sure you know what you are doing, performing any one of the above two tasks will result is data loss, you will loose your current copy of the database.

    Make sure you take backup of the database before you execute your scripts.

    If you have any question, please post it here. Its always good to ask and learn, instead of doing some silly and feeling guilty later on.

    IM.

  116. I have mirror database on separate server. I m using Microsoft 2005 standard edition and mirror database to another server. These two servers are at same location, now I need another server at remote side and want to add remote server in mirror. Is it possible to mirror database in two separate server. There may be possible by log shipping…How can I complete procedure for log shipping or mirroring to secure my database at remote location.
    So, Setup wil be like Primary Server -1 ,Seconday ServerA both at current lockation and Secondary ServerB at remore location.

    Devang

  117. Hi Pinal Dave

    Thanks for all the info on this site. My colleague deleted rows off a table that contained a lot of settings for our finance system. We do daily full backups and daily transaction log backups. Recovery model is full. 30 mins after she deleted the rows both the full backup and transaction log backups ran. I’ve tried doing a point in time restore using Management Studio, but I only seem to get the database restored to a point after her deletes. Is there any way for these changes to be rolled back?

    Regards

    FJ

  118. Hi Pinal Dave,

    My .LDF (file) eats most of the spaces of my drive. How do i BACKUP LOG file? If i do this will it free spaces on my drive?

    Thanks in advance.

    Regards,
    Ching

  119. I have a question for you Pinal.

    I make regular full backups of my database on the same server. I want to be able to pick out the most recent backup and restore it to a different database on a different server. Also I want an e-mail alert when the backup was success or failed.

  120. Hi pin,
    I backup database with full recovery model, My database have filegroups, I can restore database but restoring filegroup generating errors.

    Please help in this regard,

  121. Hi Pinal,
    I have a database with 12 secondary files, i alwayes delete the data in the secondary files. I want to delete the secondary files physicaly from the storage. Is it possible to do it maybe to change something in the master or msdb database, did you have any experience like this.
    Thanks in advance.

  122. I have a backup (DB_143073.bak) from SQL Server 2005 Enterprise, that I wish to create a new database from in SQL Server Express 2005.

    Thing is, after running:

    RESTORE FILELISTONLY
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB_143073.bak’

    It shows I have 3 logical files:

    DB_143073
    DB_143073_log
    sysft_DB_143073

    and if I use the Wizard, I get an error, because the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\DB_143073 does not exist.

    Some help on this subject would be very appreciated!

  123. @Chris

    When SQL Server takes backup of any database, its also stores some header information and location of the backup.

    For example,

    you took backup of Database A on Server A,
    Database A has its .MDF on D:\Program files\MSSQL\Data\Database_A_Data.mdf.

    and .LDF file on D:\Program files\MSSQL\Data\Database_A_log.ldf.

    Location of these files will be saved in backup.

    Now, when you try to restore backup on a different server or same server, SQL Server by default, try to create a database with its log and data file on the same location where it took backup from.

    Case 1: If you already have DatabaseA online, and try to restore the backup on the same server where you took backup, then you will get an error.

    Error Message : Database files are being used cannot restore backup……

    Case 2: If you try to restore backup on different server, and if this location does not exists,
    Location : D:\Program files\MSSQL\Data\
    Then sql server will give throw another error.

    Error Message : location does not exists …..

    In you case, you are getting second error message,

    Resolution : As posted in this post,

    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

    You need to use key word : With Move while restoring,
    This will point your backup to restore files at new location, rather than restoring at old location which does not exists in new server.

    If you are trying to restore backup, from SQL Server Management tools,

    1. Right Click Databases
    2. Click Restore Database
    3. Write New Database Name.
    4. Click From Device
    5. Click Browse Button and point to location where you have your backup file saved.
    6. Click OK.
    7. Check Backup Name. ( Under Select Backup Sets to Restore)
    8. Click Options ( Important Step)
    9. Under Restore As, Give new file location where you want to create .mdf, .ndf and .ldf files.
    You can either use browse button to point out to a location you want to create data and log file, or if you know a location already, just paste into that box.

    10.Click OK.

    Should work.

    ~ IM.

  124. This looks like the solution I am after, thank you very much Imran, and thank you too Pinal for such a wonderful source of reference.

    Just one question. What is the significance of the ‘sysft_DB_143073′ directory in the BAK file, and should it or can it be ignored? Not really sure what FT is all about, except I know it’s an abbreviation for ‘Full Text’ and appears to have some relevance to conducting a search.

    I also highly presume your script will ignore it, unlike the wizard which won’t.

  125. i need to update values of multiple columns with single value, i know the following query
    “update ‘tablename’ set column1 = value1 , column2 = value1, column3 = value1 where id = xxxxx ”
    but is there any other sql command which will reduce my work of everytime writing the value1 thrice. i need a query which will update all the the three columns with specifying the value1 only once.

  126. Hi pinal,
    Unfortunately I Have deleted 1 table from my database.
    I have backup for that database before 15 days.
    Please help me to restore the data.

  127. @Vijay,

    Vijay, if you just want the structure of the table and you think you created that table 15 days ago, then you can always find that table in your 15 days old backup. If you want Table with data in it, then perform the following steps.

    If your database is in full Recovery mode /Bulk logged Recovery mode then follow steps 1 – 4, other wise forget about the table.

    Step1 :
    If your database is in full Recovery mode /Bulk logged Recovery mode, then take Transactional Log Backup Right now.

    Step 2: Restore your Full backup which you took before 15 days with NO RECOVERY Option. Give Database a new name. Just to keep the current database running.

    Step3: Restore any other transactional log backup that you took after you took this full backup, meaning you said you took full backup before 15 days, if you took any transactional or differential backup, restore all backups in ascending order, one by one, all with NO RECOVERY Option. Do not apply the latest transactional log backup ( that you took just now).

    Step4 : Now You apply latest Transactional log backup, with RECOVERY, but you will use point in time recovery. Meaning you will ask Sql Server to stop restoring at specific time. ( This is the time when you dropped table). You need to restore the database untill that time.

    Thats it, As soon as restore completes you will have your table in the database. BUT, you will loose all data that was entered in the database after table has been dropped , this is because you asked Sql Server to not restore the rest of the log backup.

    If you have any other database which is a copy of this database ( DR Set up through Log Shipping or Database Mirroring or Replication ), you can try if table exists in other database ( There are more chances that it will be deleted in DR also, but still you can try, you could be lucky enough).

    ~ IM.

  128. Hi
    I have doubt can we restore sql server 2008 database backup into sql server 2005.

    With regards

    Bharath kumar

  129. Hi
    I have doubt can we restore sql server 2008 database backup into sql server 2005.

    Thanks in advance

    With regards

    Bharath kumar

  130. Am trying to restore a database named TestFulla.Bak to a database Test_2 with the following command

    RESTORE DATABASE Test_2
    FROM DISK = ‘D:\Ravindran\SQL Bakup\TestFulla.bak’
    WITH MOVE ‘Test_Data’ TO ‘D:\Ravindran\SQL Bakup\Test_Data.mdf’,
    MOVE ‘Test_Log’ TO ‘D:\Ravindran\SQL Bakup\Test_Log.ldf';

    but am getting this error message

    Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing ‘Test_2′ database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Help me..!!

  131. The restore process cannot restore the database from the backup file because there is already a database called Test_2 present on your SQL 2005 server. I tried deleting the Test_2 database then i restored it successfully…!!!

  132. Hi!

    I m using LTO Tape for backup my database. I m using maintenance schedule plan to take backup of my database everyday. so, i hv nos. of backup on my LTO TAPe media, now problem which I suffer is HOw can I Restore particular days database from LTO TAPE.

    LTO tape having nos. of backup which were executed by maintenance plan.

    Pls. give command qry for the same.
    Thx.

  133. Normally in IT world we have a naming method . Especially for Backup files . Through programmes when we take backup files with name like 04072009PRODDB.

    So when you try to restore the back up files you can select the DB files from this list.

    In your case you did it through ur MP.

    How did you did it ?

    Used any Custom Script ?

    How will use restore cmd without knowing the filename ?

  134. @NK Menon.

    The way you name your backup, the same way you can also read name of that backup file and use that name in restore command.

    You said, backups are named in a specific fashion, I am sure some one must have written a T-SQL to get backup name in that required format.

    Use the same logic, to read backup file names when restoring databases.

    In Short, you need to use dynamic SQL, in which name of the backup file changes as per the logic (same logic, with which you name your backup files).

    ~ IM.

  135. Hello pin,

    i try to restore the sql server database and getting a lot of error .

    actually the location of bak file is totally different to my system location

    RESTORE DATABASE [AdventureWorksDW]
    FROM DISK = ‘D:\AdventureWorksDW.bak’
    WITH MOVE ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorksDW.mdf’ TO
    ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf’,
    MOVE ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorksDW_Log.ldf’ TO ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf’

    and i get error-

    Msg 3154, Level 16, State 4, Line 3
    The backup set holds a backup of a database other than the existing ‘AdventureWorksDW’ database.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE DATABASE is terminating abnormally.

    So please give the some good suggestion regarding to this error .my lot of work is pending .

    Thanks And regards
    Pratyush singh

  136. Dear Sir,

    We have 2 servers named Webserver2 & Erpserver hosting the same ERP Application using SQL 2000 sp4. Webserver2 runs on SBS 2000 whereas Erpserver runs on SBS 2003.

    My Problem is that at a given time we use only one server.
    or eg. the path to access the data is http://webserver2/sleeknew/master/login.aspx.

    Each time the Webserver2 has a technical snag or virus problem (virus problem is very frequent, malwares are regularly found on the server, we use AVG Network edition with anti syware & malware), the server goes off the network or stop responding.

    Now the only option left is to backup the database & restore it on the other server (erpserver) & start working again. The webserver2 will now have to be formated & all the reqd. software need to be installed again.

    So, to save on this long procedure, if we are using the appl. http://webserver2/sleeknew/master/login.aspx. on webserver2 using the database hosted on webserver2. Can the database on erpserver also be simultaneously updated on erpserver while using the appl on webserver2. In this manner the shift from one server to another can be instant.

    So, pls. help me out of this situation. Your suggestions will of great help.

    Thanks & Regards

    Jay

  137. Hello Sir,

    Please help me with my question. I have server-A and server-B and I’ve backup both their database servers. I used database backup from server-B to restore database of server-A. Since database of server-A has different set of user accounts, is it possible to restore these user accounts using the database backups that I’ve created earlier in server-A? Also, please suggest a better process than what I’ve done.

    Thank you.

  138. @Vlad.

    Seems there is small confusion. You need be very specific about Users and Logins.

    Users exists at database level and Logins exists at instance /server level.

    Login is to enter into SQL Server, and user is to enter into database.

    Logins information is stored in master DB. and users information is stored in individual databases.

    When you restore a database backup of a database from Server B to Server A, you will still have all those users from Database on Server B, in new database that you just created on server A. Because users are defined inside database.

    Only thing you will see is users will be orphaned, either because there does not exists any login for those users in Server A, or SID for logins of few users does not match with SID of logins on Server A.

    I would suggest, Script out all your logins From Server B first and then execute that script on Server A.

    Or

    Exec Sp_Change_Users_Login ‘Auto_Fix’ /’update_one’
    TO fix orphaned users.

    ~ IM.

  139. Dear Imran Mohammed..

    One of your reply to chirs help me to restore my Database which was in .dat file.

    After searching many sites I got the exact answer posted by you….

    Good explanation of the location funda….

    Thanks & Regards,
    Mohammed Pasha.l

  140. I am responsible for an productional SQL database which is
    located 2000 miles away. I can login into the Windows 2003
    server remotely with a local administrator account, but I do
    not have a domain account there.
    I wish to build a copy of the database locally and do
    development work.
    I cannot copy the SQL database, which will deattach it,
    put it in Single User mode, copy the database and reattach it.

    I was thinking of restoring the database on my development
    server locally by using backup files only and then figuring
    out how to fix the broken logins.

    Can I restore an SQL database to a new SQL Server where it
    does not yet exist?

    Rick

  141. I forgot to mention above that the vesion is SQL Server 2000.

    The 2 SQL Server 2000s involved do not have any domain
    network connections, but access by copying files across
    mapped folders.
    This seems to eliminate some of the nice Wizards, which
    require direct domain connections.

    Thank you for any help.

    Rick

  142. Hi!, I heard about you in a coversation of my friends, now I have a question to ask you :-) , I have been developed a project in vb6 with sql server 2000 database, make backups but, now, I have to restore it!!, my problem is the version of sql server, I mean, in my computer have an O.S. in english, so, c:\program files\… but I need to restore backup generated from an O.S. in spanish, c:\archivos de programa, the error is :

    the fisical name c:\archivos de programa\……Base.mdf maybe incorrect…

    Help me please!!!!… sorry for the orthography errors… I hope you understand what I mean… thanks!!

  143. thx for the script.

    i want to share script for backup too

    BACKUP DATABASE [database] TO DISK = ‘[file location]’ WITH INIT, NOUNLOAD, NAME [backup name], NOSKIP, STATS = 10, NO FORMAT

  144. hii Mr.Dave
    hi had tryed u r code

    restore FILELISTONLY
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’

    but it give me this error

    Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’. Device error or device off-line.
    RESTORE FILELIST is terminating abnormally.

    after this i also tryed next to make db in single user tht was successfully compiled.
    and next code
    RESTORE DATABASE gohil
    FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’
    WITH MOVE ‘E:\gohil.mdf’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’,
    MOVE ‘E:\TC\gohil.ldf’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’

    which on efor the db restore further it gives me error
    —Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’. Device error or device off-line.

    so wht i have to do??? now

    please any one help me for the db restore

  145. Hi their,

    Upon doing the said script for restoring database i got the following error :(

    Msg 3102, Level 16, State 1, Line 10
    RESTORE cannot process database ” because it is in use by this session. It is recommended that the master database be used when performing this operation.
    Msg 3013, Level 16, State 1, Line 10
    RESTORE DATABASE is terminating abnormally.

    any idea?

    thanks

  146. Pritesh:
    I’m not an expert but:

    is your LDF line correct?

    MOVE ‘E:\TC\gohil.ldf’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\gohil.mdf’

    Should it be gohil.ldf ?

    I use database labels already in the database to restore
    (Backup is a full backup)

    Use GOHIL
    Restore database GOHIL
    from disk = ‘D:\GOHIL_db_200908240200.BAK’
    with move ‘GOHIL_DATA’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’,
    Move ‘GOHIL_LOG’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.ldf’,
    stats = 5
    go

    Rick

  147. Hi,

    Any body know’s my doubt just publish your idea!…

    I want to take a backup for one table in script format..
    like below this,

    Insert into tblStudent values (1,’Raja’)
    Insert into tblStudent values (2,’Sindhu’)

    This is very urgent know any one please help me,

    Thanks,
    Maniraj…

  148. Hi,

    Take a backup for database using query….

    Backup database dbName to disk=’E:\NewFolder’

    Then,

    Restore database dbName from disk=’E\NewFolder’

    That’s it…

    Maniraj….

  149. @Maniraj

    Untill SQL Server 2005, using SQL Server native tools, you cannot take backup of a table in a database. You can only take backup of database or filegroup(s) in a database.

    You said, you want to take backup in a script format, that is possible using SQL Server Tool by name : Database Publishing Wizard.

    For a step by step pictorial explanation of how you can generate script for a table with data, check this link:

    http://products.secureserver.net/products/hosting/PublishingWithDPW.htm

    ~ IM.

  150. hi pinal,

    can u pls explain me. how to restore my .bak file with sql server 2005 wizard. i think it is very easy to understand then query.

    i m trying this,

    i m here in sql server 2005 wizard
    1. ASHUTOSH/DATABASE/
    2. then right click on screen it will dispaly menu which have one option “Restore Database” then……????

  151. hi
    There is a problem with my project. i develope a windows based application in c# with sql server 2005 and develop a web site in Asp.net with sql server 2005 with same database .
    Now i want to update my windows application database with one single click at client side . and wana take backup and restore the backup . Please tell me the Proper process to do so . And if i have the few extra tables at web database , there can be any problem to update it or not, if yes suggest me the solution

  152. Hi Pnal,
    can we take backup on networkpath
    like ‘\\servername\sharefoldername\backupfilename.sqlbackup of .bkp’

    i used this query
    ‘BACKUP DATABASE MYDATABASE TO DISK =\\servername\sharefoldername\backupfilename.bkp’ WITH INIT
    but this is not working.

    Error:-Cannot open backup device ‘\\servername\sharefoldername\backupfilename.bkp’. Device error or device off-line. See the SQL Server error log for more details.

    please tell me the query and can we use this query in dotnet

    Thnaks in Advance
    Regards
    Raj

  153. Hi,

    Quick post just to point out a mispelling in the script : you requiered to rename the .LDF file to .MDF. Seems it should be .LDF :)

    Great job ;)

  154. Hello Pinal,

    I have a backup from a database with a single data file in the primary filegroup and would like to restore it to a database with multiple files in the primary filegroup. However, the restore always reverts the configuration for the new database back to a single file. What is the trick to make this work?

    Example:

    CREATE DATABASE [Test] ON PRIMARY
    ( NAME = N’Test_data1′, FILENAME = N’D:\Test_data1.mdf’ , SIZE = 2048KB , FILEGROWTH = 10%),
    ( NAME = N’Test_data2′, FILENAME = N’D:\Test_data2.ndf’ , SIZE = 2048KB , FILEGROWTH = 10%),
    LOG ON
    ( NAME = N’Test_log’, FILENAME = N’D:\Test_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
    GO

    RESTORE DATABASE [Test]
    FROM DISK = N’D:\Test.bak’ WITH RECOVERY, FILE = 1,
    MOVE N’Test_data’ TO N’D:\Test_data1.mdf’,
    MOVE N’Test_dog’ TO N’D:\Test_log.ldf’
    , NOUNLOAD, REPLACE, STATS = 10
    GO

    The restore ends up deleting the Test_data2.ndf file and I am left with a database with only one data file.

    Thank you, Bradley

  155. Hello Mr.Pinal,

    I have a requirement where in i have to get the database backup and restore when required.
    I have to do this in a web application. The application has been developed using Java/J2EE technologies.
    I have tried database backup and restore using JDBC…..but it didnot worked ….

    Need help regarding…..
    Iam using SQL Server 2005 database….
    Can you please mention the steps for performing the above nmentioned tasks

    thanks

  156. Hi,
    I am here to ask you help regarding Dotnetnuke with SQL Server 2008 express managment studio.

    I have dotnetnuke website backup and Database backup of 2005 sql server .bak and I uninstalled the 2005 and installed 2008 sql server express managment studio.

    In iis7 i pointed the new website to backup of Dotnetnuke site, and restored the .bak (2005) on 2008 sql server express managment studio. It not worked for me.

    earlyer what i did the same when i done on 2005 it was done. not on 2008.

    can you plz help..

  157. Dear Sirs
    I have a backup from SQL Server 2005 and want to restore it on SQL Server 2000. Is this possible.

    Regards
    abrar

  158. Hi, i was executing a restore process on sql 2005 but i had toi cancel it couse the users told me that the system was too slow, i didnt know that the reason was that some user was running a big process well, now the data base appears like it is restoring and i can stop that process, how do i do it?

  159. Simple restore commad

    I need a simple restore command with current date.

    Restore database with today backup file only. can any body helps me in this regards.

    remo

  160. How could I restore a SQL 2005 database which has full-text catalog to a SQL 2008 database with T-SQL statement?

    I can restore the database with SSMS GUI without any problem.

    Thanks in advance.

  161. Very useful article. Thanks a lot.
    I wrote a simple T-SQL to take backup and then restore it.
    I used “WITH PASSWORD” Option to protect my database backup from unauthorized access.

    BACKUP DATABASE PracticeDB
    TO DISK=’C:\PracticeD.bak’
    WITH DESCRIPTION=’Database Backup’,
    INIT,
    PASSWORD=’TryAgain’

    But anyone can overwrite it by taking the backup again. So How can I fully protect my database backup.

    (Please have a look at what I have done h ttp://www.techpint.com/programming/sql-server-backup-and-restore-database-using-t-sql)

    Thanks in advance.

  162. I finally got my restore to work between 2 servers. The script below concatenates the name of the file to match the name of my nightly backup which includes the date. The only time a problem would be Jan 1 when the year is off, but if I work on it some more, I think I can work that out. Make a job on the server that needs to get the backup restored onto it and then make this a step in the job and schedule it as you need it to run.

    Declare @vBackupPath varchar(46)
    set @vBackupPath =
    ‘\\NameofServerContainingBackup\NameofFolderContainingBackup\FirstPartBackupFileName’ +

    cast(Year(getdate()) as char(4)) +
    case when Month(getdate()) between 1 and 9
    then ‘0’ + rtrim(cast(Month(getdate()) as char(2)))
    else rtrim(cast(Month(getdate()) as char(2)))end +
    case when Day(getdate()) between 1 and 9
    then ‘0’ + rtrim(cast(Day(getdate()) as char(2)))
    else rtrim(cast(Day(getdate()) as char(2)))end
    +
    ‘RestOfYourBackupFileName.BAK’
    RESTORE DATABASE NameOfDBYouWantToRestore
    FROM DISK = @vBackupPath
    WITH REPLACE,
    MOVE ‘YourFile.mdf’ TO ‘E:\MSSQL\Data\YourDBName.mdf’,
    MOVE ‘YourLog.ldf’ TO ‘E:\MSSQL\Data\YourLog.ldf’

  163. Hi,

    We have migrated database from sql server 2000 to sql server 2005.

    Now we have the database ready in sql server 2005.

    Is there a way to use the (latest mdf and ldf files) from today from sql server 2005, and have the newest data in sql server 2005.

    Basically, we dont want to touch stored procedures, views etc, only need to newest data in sql server 2005.

    Can we detach the database(sql server 2005) and then reattach with newest mdf and ldf files.

    I am in urgent need and any help is appreciated.

    Thanks,
    Ashima

  164. H Ashima,

    You can move a database from one server to another by detaching & reattaching the .mdf, .ldf files. But this will move the whole database with all objects.
    Even to move database the recommonded method is backup-restore.
    To get only the differences in tables on two databases, you can use TABLEDIFF utility. This utility has option to generate the script of all changes.

    Regards,
    Pinal Dave

  165. Pinal, great blog. I was wondering I am getting backup files that I want to attach automaticly but I don’t know the filenumber is there a way to do this

    RESTORE DATABASE [MYDB]
    FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MYDB.bak’
    WITH FILE = 1,
    MOVE N’COREREP_3_Data’ TO N’C:\Data\MYDB.mdf’, MOVE N’COREREP_3_Log’ TO N’C:\Data\MYDB_1.ldf’, NOUNLOAD, STATS = 10

    But replace the File= 1 with File = MAXSomething

    GO

  166. I am a little closer but how do I get the position of the last record?

    RESTORE HEADERONLY FROM DISK = ‘C:\data\myDB.bak’

  167. I need to restore a sqlser 2000 database from disk. When I do a filelistsonly restore, there are only 3 file types listed, an .mdk and 2 ndfs. Is a full database restore possible from this situation? I tried a myriad of combinations of tsql with no success as yet

  168. I need to restore a sqlser 2000 database from disk. When I do a filelistsonly restore, there are only 3 file types listed, an .mdk and 2 ndfs. Is a full database restore possible from this situation? I tried a myriad of combinations of tsql with no success as yet

  169. hi
    i’m Prakash
    i’m a trainee in a software consultancy. i develop a software with SQL server as backend. i need the roll back query while insert or update data.

  170. good day sir!!!my question is on how to backup sql server 2005 using Visual basic 6.0 pls help me with my problem1!!im a beginner using a SQL Server 2005 integrated on vb 6,thanks God Bless!!!

  171. what is the difference between replication & mirroring..?

    &&&&&&&&&&&&&&&&&&&&&&

    what is the difference between Logshiping & replication ..?

  172. Hello Sir,

    The following query is working good on the server pc.

    BACKUP DATABASE Genius
    TO DISK = ‘D:\Genius1.bak’

    But when i try to take backup on the location of another PC on the LAN, i.e. my PC, Nikie,

    BACKUP DATABASE Genius
    TO DISK = ‘\\Nikie\D:\Genius1.bak’

    It is showing following error:

    Cannot open Backup device. Operating System Error 5 (Access is denied).

    Please help. I want to take backup on any client PC on the LAN.

    Thanks in advance.
    Regards,
    Nikie.

    • Try to copy the backup file to the local disk first. Then try restoring it. If I remember correctly I had some issues like this some years ago. I built couple of automated scripts for the backuping, file copying and restoring.

  173. Hello Shahriar,

    Table is not a unit for restore. You would have to restore the full database than get the data table from that database.
    If the table is in a separate Filegroup then you can restore that particular filegroup only.

    Regards,
    Pinal Dave

  174. hi

    i have seen that every one on web tells the simple backup query like

    BACKUP DATABASE DBNAM TO DISK = ‘BACKUP_PATH';

    i have a peoblem with this query. i am trying to backup my full Sql Server 2008 enterprise with a small script similar to above
    ——————-
    DECLARE @name VARCHAR(50) — database name
    DECLARE @path VARCHAR(256) — path for backup files
    DECLARE @fileName VARCHAR(256) — filename for backup
    DECLARE @fileDate VARCHAR(20) — used for file name

    SET @path = ‘D:\AutoDBBackups\’

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    ————–

    this script crashes when a database with an unusual name comes. like “SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10″

    simply if you have any database with a “-” in name backup query will not recognize that as a database

    simple solution is

    BACKUP DATABASE “SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10″ TO DISK = ‘D:\AutoDBBackups\as.bak';

    just wraped the database name with double quots and done.
    but when it comes to script there is no way. or i could not find any way.

    like this small script

    DECLARE @name VARCHAR(50)
    SET @name=’SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10′
    BACKUP DATABASE @name TO DISK = ‘D:\AutoDBBackups\as.bak';

    i tried with everything i can think of
    like

    SET @name=’[SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10]‘
    or
    SET @name='”SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10″‘

    and many other.. no use

    can anyone just try with that.. and explain what to do

    thanks
    Ali

  175. @Ali

    Replace these two lines with your two lines in your script,

    SET @fileName = ””+@path + @name + ‘_’ + @fileDate + ‘.BAK”’

    BACKUP DATABASE Quotename(@name) TO DISK = @fileName

    Should work, if it does not works please let us know what error message you get.

    ~ IM.

    • try the declare cursor statement as like below:

      DECLARE db_cursor CURSOR FOR
      SELECT ‘[' + name + ']‘
      FROM master.dbo.sysdatabases
      WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

      and set @fileName as below:

      SET @fileName = @path + replace(@name, ‘-‘,’_’) + ‘_’ + @fileDate + ‘.BAK’

      In same way, replace all other special character that are not allows in file name with underscore.

      Regards,
      Pinal Dave

  176. Hi Pinal,

    I am just working on SQL database restore and transaction logs backups in SQL 2005.

    I am restoring complete backup with recovery.

    Now, if i am trying to restore Transactional logs, it saying an error “The log or differential backup cannot be restored because no files are ready to rollforward.”

    Any suggestions PLZ.

      • Thanks Pinal,

        Ya Actuly, Database in not readable if restored with NORECOVERY option.

        So we want to restore with RECOVERY only to make it readable.

        • Hi Pinal,

          Actually, it seems we cant use STAND BY option in SQL server 2005.

          We want to restore the Transactional Logs in SQL 2005. Its not happening if restore is done with RECOVERY.

          And we are not able to use NORECOVERY as it will leave the database non-readable.

          Can you give any suggestions here….

  177. Hi Pinal,

    I wanted to back up a database and restore it in a different name using T-SQL Script. I tried the above script. But it is not is not working.

    CREATE DATABASE NEW_DB

    RESTORE FILELISTONLY
    FROM DISK = ‘c:\Backup\OLD_DB.BAK’

    ALTER DATABASE NEW_DB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    RESTORE DATABASE NEW_DB
    FROM DISK = ‘c:\Backup\OLD_DB.BAK’
    WITH MOVE ‘OLD_DB’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL$VSDOTNET\Data\NEW_DB.MDF’,
    MOVE ‘OLD_DB_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL$VSDOTNET\Data\NEW_DB_log.LDF’

    ALTER DATABASE NEW_DB SET MULTI_USER
    GO

    The error was
    Reissue the statement using the WITH REPLACE option to overwrite the ‘NEW_DB’ database.

    I tried with Replace Option before this and that gives the error
    The OLD_DB is in use. Use ‘Move’ to mention the location.
    (RESTORE DATABASE CM_V4_PO FROM DISK = ‘c:\Backup\OLD_DB.BAK’
    WITH REPLACE)

    The above replace script works fine in the server.

    I m using MSDE. This is urgent. Can anyone tell me what mistake am i commiting here

  178. when i am restoring from .bak file containg multiple backup sets and when i am restoring the most recent day backup i am getting only first file/date backup but i want to restore last file/date backup from .bak file.

    i have used this command also but still not working

    RESTORE DATABASE Testing
    FROM DISK = ‘C:\Documents and Settings\Administrator\Desktop\New Folder (2)\Snehal.bak’
    WITH file = 21 ,NOUNLOAD ,STATS = 10 ,RECOVERY ,REPLACE

    help me ! thanks in advance .

    ravi kore

  179. I have a recently downloaded a file from murach.com (Murach’s ADO.NET 3.5, LINQ, and the Entity Framework with C# 2008) which has got all the codes to work, which are present in the book.
    the database file given is developed using sql server2005 and i’m using sql2000 server. can I make use of that datbase in any way.
    please reply.
    thanks

  180. Hi,
    I have separate backup file with Datafile and Logfile from sql server 2000. I want restore into sql server 2005.How can i restore it.please any one help Me.

    Thanks
    T.Senthil kumar

  181. If this a typo
    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,

    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

    should it not read LDF and not .mdf
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

  182. Hi Sir,
    Is there any way to publication in sqlexpress 2005 with sqlserver 2000.
    Actually i have a offline database(localhost) and another same online database(eg. http://www.abc.com), Sir i want to use replication between sqlexpress and sqlserver2000. is this possible ?.
    I want it urgently.

    Thanks in advance.
    Warm Regards
    Raj Thapliyal

  183. Hi Samidh,
    You use this command.

    RESTORE DATABASE DatabaseName
    FROM DISK = N’E:\Transfer_06Apr2010.bak’
    WITH FILE = 1,
    MOVE N’Transfer_Data(Datafile of Old Database)’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Transfer2.MDF(Datafile of Restore Database(new Database))’,
    MOVE N’Transfer_Log(Logfile of Old Database)’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Transfer2_log.LDF(Logfile of Restore Database(new Database))’,
    NOUNLOAD, STATS = 10

    Thanks & Regards
    T.Senthilkumar

  184. Hello,
    I have one situation ,letsay one data base is completely deleted, but i have a saved full backup,Now is it possible to Restore the data base,

    While i am trying to restore I have followed the below steps
    1)created a new database with Old database name
    2)tasks–>Restore–>Database–>select from file–> selected the old data base backup file *.bak>then click on Ok.

    The i am receivig follwing the error

    ********************************************
    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Restore failed for Server ‘\’. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘DBtest’ database. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476

    ********************************************

    Please let me know any other tasks that i need to perform before restoring,,

    Thanks
    Saty

  185. So I followed the steps listed for restoring my db from the .bak file. Here is my script

    RESTORE FILELISTONLY
    FROM DISK=’E:\ColorsQCMacys2.bak’

    —————————————————–
    then, using the logical names, and the MOVE function
    —————————————————–

    ALTER DATABASE ColorQCMacys
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    —–restore db
    RESTORE DATABASE ColorsQCMacys
    FROM DISK = ‘E:\ColorsQCMacys2.bak’
    WITH MOVE ‘ColorsQC’ TO ‘my location.mdf’,
    MOVE ‘ColorsQC_log’ TO ‘my location.ldf’

    and I received the following error:

    Msg 5011, Level 14, State 5, Line 1
    User does not have permission to alter database ‘ColorsQCMacys’, or the database does not exist.
    MSG 5069, Level 16, State 1, Line 1
    ALTER DATABASEstatement failed.
    processed 12592 pages for database ‘ColorsQCMacys’, file ‘ColorsQC’ on file 1.
    Processed 3 pages for database ‘ColorsQCMacys’, file ‘ColorsQC_log’ on file 1.
    RESTORE DATABASE successfully processed 12595 pages in 1.459 seconds (70.717 MB/sec).

    So, does/will that have any negative effect on my db? It appears to be there, I’d just like to know if those cryptic error messages mean that something is not going to work properly down the road. Thanks so much for posting up relevant articles! You have no idea…. ;)

  186. Hi Sir!
    My self Manoj Singh actually i have join a company where used sql 7 now i want to proceed with sql 2005 .
    So plz tell me how i convert ..it. without Interrupt my all running application in in organization.

    • You can take a backup in version 7 and restore it in version 2005. Change the connection string and the application will run
      Also make sure to read about behavioural changes in migrating from one version to another version

  187. Hi Pinal,

    I am regular visitor of this website and its very helpful for me.
    I have a question for you.

    I want so see the backup process in sql server 2000 is it possible.

    or alse is there any command for backup which will show the percentage complete of backup

    Thanks,
    Rajul

  188. Hi,

    I got a transactional replication scenario; and I wants to know if my published DB fails:

    1. Could I recover the DB using the Subscriber Db
    2. If possible how to recover it?

  189. I need to know how to get backUP in sql.

    i am fresher.i am working SAP. i need to learn SQL deeply.Can you Give me a PDF which is easy to learn?

    is it the right way to Do backup in SQL ?

    use LRPTesting;
    Go
    backup database LRPTesting
    TO DISK = ‘D:\VGN1.BAK’
    WITH FORMAT,
    MEDIANAME = ‘Z_SQLServerBackups’,
    NAME = ‘Full Backup of VgnGoliveMIPL0605′;
    GO

    i got message, that is sucess.
    but i need to what is ‘Z_SQLServerBackups’

  190. Hi Pinal, i need a help urgently

    i have a backupfile located on \\kftusoktulsps14\public\Infosys\EU DB backup\Prod_Teamwork_EU10_BAK.bak

    i have to restore it on different server which is KFTUSOKTULSPS78

    with the name of Prod_Teamwork_EU10_04282010

    please create a script for me and help me as soon as you can,

    Thanks in advance

  191. Hi

    I have a sql script to restore my database :

    RESTORE DATABASE [PIScRestore001]
    FROM DISK = N’\\FSERVER\Development\Applications\PIDevelopment\StartupApp\Payroll\bin\Debug\MasterDB\BlankDB.bak’
    WITH FILE = 1,
    Move N’Payroll_Data’ TO N’F:\Databases\PIScRestore001.MDF’,
    Move N’Payroll_Log’ TO N’F:\Databases\PIScRestore001.LDF’,
    NOUNLOAD, STATS = 10

    AND i get this error:

    Msg 3201, Level 16, State 2, Line 2
    Cannot open backup device ‘\\FSERVER\Development\Applications\PIDevelopment\StartupApp\Payroll\bin\Debug\MasterDB\BlankDB.bak’. Operating system error 3(The system cannot find the path specified.).
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    it is some permissions related issue
    but its very difficult to give permissions to all systems on a network
    does anybody have a proper solution for this issue where the location of a backup is retrieved at runtime at remote m/c and i have to use that database?

  192. Hi Pinal Dave,

    I hope you can help me, I’m not a technical person by profession, I have a problem that my web hosting service wont help with. I hope you can :-)

    Canyou tell me how to identify the ‘create database’ line in a V4 .sql backup so that I can restore the back-up to a V5 database for my wordpress blog?

    Background.
    To upgrade my wordpress blog to 2.9.2 I needed to upgrade my sql server from V4 to V5

    I used the wordpress guidance to create a backup .sql file of my V4 database.

    Using my web hosting service UI (GoDaddy) I created a V5 sql database, upgraded my wordpress, posted a few posts on the blog to check it all works – it does :-).

    Then I tried to restore the V4 backup first using GoDaddy UI – it gave me a success message but didnt actually restore, then using MyPhpAdmin (StarfieldTech), again it gave me a success message without actually restoring the backup. Both support services told me the failure is because I still have the you “create database line in your SQL file”. They will not tell me how to identify this line.

    Searching the backup SQL file (in wordpad) for ‘Create Database’ produces no results. I do not know what line to delete in order to restore my database. Please help me :-)

    best wishes, Wendy (UK),

  193. Sir ji

    Gud Afternoon

    i have read your many articles,these articales are very usefill and i have learned .

    i have a problem sir

    how can we read .ldf file of a database

    thank u sir

  194. Hi,

    I am trying to import a database over the network. While doing so I am getting following error .

    Please let me know I any body have the solution for the same.

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘\\10.15.21.29\g\Intel\Mylearning4Saba.BAK’. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Thanks & Regards,

    Yogesh

  195. 1.create database ABC

    2.backup database ABC to disk=’D:\backup\DB\ABC.bak’

    Here i created a new database ‘ABC’.NOW my DATABASE is empty(no objects where created), i am taking full back up,the out put i am getting

    OUTPUT:

    Processed 152 pages for database ‘ABC’, file ‘ABC’ on file 1.
    Processed 2 pages for database ‘ABC’, file ‘ABC_log’ on file 1.
    BACKUP DATABASE successfully processed 154 pages in 0.267 seconds (4.698 MB/sec).

    MY dout is even though my database is not having any objects,why it is backup 152 pages data file,
    i did’t done any transations even why it backuped 2 pages of log files.

    2) AGAIN when i take the LOG BACKUP ,with out doing any transaction again it showing that ” 2 log pages where backuped”

    backup log ABC to disk=’D:\backup\DB\ABC.bak’

    OUTPUT:
    Processed 2 pages for database ‘ABC’, file ‘ABC_log’ on file 2.
    BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.219 MB/sec).

    3)AGAIN when i take the log backup ,it showing ‘ZERO pages backuped'(no transation were done)

    backup log ABC to disk=’D:\backup\DB\ABC.bak’

    OUTPUT:
    Processed 0 pages for database ‘ABC’, file ‘ABC_log’ on file 3.
    BACKUP LOG successfully processed 0 pages in 0.033 seconds (0.000 MB/sec).

  196. Hi Pinal,
    I have got a problem, I was working on database and while testing a Stored Procedure, unfortunately a querry of delete got executed and my data is deleted. But don’t know how to recover back that data.
    And i haven’t use transaction block so, not able to rollback that transaction. please help me out to get back my data.

    Thanks,
    Vineet

  197. Hi pinal,
    got stuck in database, where unfortunaltely got my data deleted and not even used transaction block. As data is deleted while execution of a Stored Procedure. So please help me out to retreive data back and FYI my database is on SQL Server 2008.
    Please help me out as soon as possible.

    thanks,
    Vineet

  198. Hey Can U help me how to take my database back up .

    the database is in my online server and I am using MS SQL Server.

    Plz help me soon….

    I am waiting for ur reply.

  199. Hi Pinal,
    I am Senthil. I have one doubt in Export and Import in sql server. Is it possible schema level export and Import in Sql server? , just like Oracle Sql * Loader.
    is there Any utility available in sql server?

    Thanks
    T.Senthil

  200. Hi Pinal,

    Really very good article!

    I have database called “xyz” which is already setup over client’s machine now I need to add new columns to that database in my new setup file and when I will run that setup on existing database make sure that existing data of “xyz” should not get lost. New columns should be added without affecting current data.

    How can I achieve this using T-SQL statements?

    Thanks,
    Harry

  201. how can you enter your home if you have lost your keys….as simple as that….restoration is seconds step after backup.
    you missing your first step so cant go further.

    Try to find you must be having either backup or mdf or mdf and ldf file. without this you cant recover.

  202. Hello,

    Can I use the “restore backup” process to create a new database? In other words, I need to take the backup from my shared host and use it to create the same database on our new dedicated server.

    Thanks,
    Doug

  203. yes, can do that by using restore database and provide the database name and then logical name of data file and log file and physical location of these files with physical name.

  204. Can anybody please help me to restore a database which had peer to peer transactional replication applied to it…..i restored the database n used KEEP_REPLICATION to keep the replication but it didn’t worked.

    any comments please.

    Thanx

  205. sir,
    i using sql server 2005 & management studio in windows 7. i take backup sql database successfully. but i use another system sql server 2005 & management studio in windows xp in a lan connection. in this xp i cannot restore.
    how to transfer the windows 7 database in xp..
    please help me..
    its very urgent

  206. I have a full backup a 00 hour midnight and every hours Transaction log backup.

    If my database crashed at 10:55 AM. then how can I restore 55 minutes data as I have last log backup available at 10:00 AM

  207. Hi ,

    I am getting following error while restoring database
    “The media set has 2 media families but only 1 are provided. All members must be provided.”
    What is solution of it . How to restore it without taking another backup

  208. Hai Pinal

    we are having Servers (SQl server 2005) in Ilaly and India, we need to Make Logshipping between these two Servers.
    Logshipping should be happen from Iltaly server to Indian server, so i configured the set up in Ilaly Server, here my problem is i am not able to connect with indian server from Italy server.

    i selected the TCP/IP Option in the “Sql Server Surface Area configuration” and tried with using IP address, But No use

    In the Secondary Server Settings Panel when i click the Connect button it shows only the Local and Network Servers of Italy Server, How can i connect with our Indian Server?

    Please suggest me what i have to do?

    is there any othere configuration Needed by System Admin or anything else ?

    Please give me some idea and help me

    Thanks in Advance
    Senthil Kumar T

  209. Hi Dev,

    Will you please give an example how can i create a sql-server 2008 job to restore the latest database.

    Thanks,
    Rajnish

  210. Hi,

    I created a dummy database and then right cilck on dummy – Taska – Restore – Database. In source of restore I selected from database and from drop down list I selected Main database.
    But after doing this, data was from my Main database was lost. Can anyone help me understanding how it happened and how can I (if possible) restore the lost data.

    Thanks

  211. Hi Pinal,

    I have been facing an issue. We have developed an application(DOTNET 2.0) which needs SQL jobs. Unfortunately, the server on which we hosted the application has SQL server 2005 express edition. So I am developing a custom EXE which takes the automatic backup of the database at a particular time daily when scheduler using windows scheduler. I am supposed to write a SQL script which takes the automatic backup of database. I will call this script in stored procedure in the code. Can this be achieved? I got an article in your blog which restores the database. I could not find articles which help in doing taking database backup. Please let me know whether this can be achieved. If yes, kindly suggest some ways to do it.

    Regards
    Shreesh Onkar

  212. Hi,

    I have to restore DB from particular file path(C:\temp) without giving databse name (ex: sampleDB.bak). Coz our back DB name has been changing daily. So can i write the SQL scripting to run the auto restore it?

    Thanks for your kindful rapid reply …

  213. Hi

    I have a database in replication from some time… everything is working fine, but now is the time to change the distributor server (hardware). I’ll backup and restore the database on the new server, but I wonder whether is there a possibility to see what tables are published for replication, because I forgot, or even to ‘backup and restore’ the replication process.

    Thank you for any answer.

  214. I got the following error message when i am trying to restore the backup to sqlserver2005(Express)

    TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Restore failed for Server ‘THAMES\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Ahaliya.mdf’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  215. HI pinal,

    I m trying to back up database (on server) over lan.

    if i m on my local pc and want to take back up of a database (on server) on my local pc, is it possible?

    i m trying this code,

    BACKUP DATABASE TO DISK = ‘\\node\sharedfoldername\bkupname.bak’

    but it gives me error,
    “Device error or device off-line. See the SQL Server error log for more details.”

    Can u help me in this!!!!!

  216. Hi Pinel,

    I am getting strange error while doing backup on SQL2005 server —

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Backup failed for Server ”. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media family on device ‘.BAK’ is incorrectly formed. SQL Server cannot process this media family. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    Any comment on it.

    Note: I am not restoring DB…..

  217. Hope you can help?

    I am using MOOS 2007 with SQL 2008 and trying to make a backup/restore with SQL the content DB.
    The scenaria is like this:

    The Organization I work for is small and therefore we have decided to keep both the MOSS and SQL in one Server. Now,
    The Server1 hold as mentioned the application and content_DB that I need to play around with. Server2 has identical installation and serves as backup with alternate url’s for web-applications.
    My question:
    How to make the backup of content_DB from Server1 and restore the same in Server2 while both server have the Content_DB in use, If there is any way to achieve this?
    Thank you

  218. I am restoring a SQL db to a terminal server: I am stuck on an error:

    RESTORE DATABASE afwdv1Data FROM DISK=’F:\Microsoft SQL Server\AGENASQL\(local)\AFW1\DATABASE_AFW1.bak’
    WITH MOVE ‘afwdv1Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwdv1Data.mdf’,

    RESTORE DATABASE afwdv1aData FROM DISK=’F:\Microsoft SQL Server\AGENASQL\(local)\AFW1\DATABASE_AFW1.bak’
    WITH MOVE ‘afwdv1aData’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwdv1Data.mdf’,
    MOVE ‘afwlog1′ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\afwlog1.ldf’

    ERROR: Incorrect syntax near the keyword ‘RESTORE’. I have an .mdf .ndf and .ldf. Not much experience in SQL so any help is appreciated.

    Thanks!!

  219. Hi ,

    I am restoring 9 dbs in a standby server (sql2000), everything is working fine except that I cant restore a certain db and I am wondering why?please help thanks.

    My stored procedure:
    ….
    ….
    RESTORE DATABASE sample
    FROM [device_sample]
    WITH
    FILE = 8,
    REPLACE,
    STANDBY =’f:\MSSQL\BACKUP\undo_sample.dat’,
    MOVE ‘logicalname’ TO ‘f:\MSSQL\physicalname.mdf’,
    MOVE ‘logicalname_log’ TO ‘f:\MSSQL\physicalname_log.ldf’

    WAITFOR DELAY ’00:00:05′

    I tried restoring it using enterprise manager gui and it is writing the .mdf but it stops there it is not writing the .ldf
    and the restore progress bar is not moving even though it already wrote the mdf file. I checked many times the file number in the backup device,logical and physical names and it is correct im wondring what is wrong..please help thanks

  220. I have transaction log files which I need to restore onto my db daily to keep the db updated. I will have about 50 log files every day I need to restore at night time. How can I restore using t-sql? Thanks.

  221. Hi sir,

    I cleared Backup and Restore.But how can i restore the database to running database. I tried to restore this comment, i got this error

    “RESTORE cannot process database ‘testDB’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
    RESTORE DATABASE is terminating abnormally.”

    Pls help me

    Thanks

  222. Hi ,
    I’m a bit new to sql.
    I have a sql server which has 2 databases. one for training and one for production.
    Now, I need to automate the process of refreshing the training database with the production copy.

    I understand that I can take the latest full backup of the production database and restore it to Training database.

    Is there any thing else that needs to be done?
    Can you provide me with the script that does similar kind of refresh activity?

    Thanks in advance.
    Prakhyath

  223. hello, I’m new to sql server and I need help in restoring the databases.

    I have a 90 backup(.bak) databases in disk… how can I restore them at a time…. I can do one at a time… Do I need to write any script or is there any option for that…

    Thank you for the help…

  224. I want to create password protected database file (i.e. datafile.mdf, datafile.ldf)

    So that no one can copy data and attach on other Sql Server.

    Please help me….

  225. when i am using the above procedure to restore the database i am getting the following error..plz give reply immediately..it is very urgent..it’s showing the error like

    The backup set holds a backup of a database other than the existing ‘MyAccountsdDBDec22′ database.
    RESTORE DATABASE is terminating abnormally.

  226. My Dear pinal

    I am facing a problem while restoring database in SQL2005

    Code

    RESTORE DATABASE NWORK2011
    FROM DISK = ‘D:DB\NWORK2010_backup_201011292100.bak’
    WITH MOVE ‘WORKSHOP2004_DATA’TO’D:DATA\NWORK2011.MDF’,
    MOVE ‘WORKSHOP2004_LOG’TO’D:DATA\NWORK2011.LDF’
    GO

    Error

    Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing ‘NWORK2011′ database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  227. hello sir, in my database the size of ldf was higher than the MDf file so i cant able generate the report soon… how reduse
    the size of Log file…pls help me

  228. Hello Pinal Dave,

    I have one problem with backups.

    I have taken Full backup,Differentail Backup, Transactional Backup.Now I am trying take Tail log backup by selecting the option “Back up the tail of the log, and leave database in the restoring state”. But i am not able to take this backup ,I am getting the error” (Microsoft.Sqlserver.smoExtended).

    Please could you explain what is this error and how to reslove this??

    Please mail me to [email removed]

    Waiting for your reply.

    Thanks A Lot.

    Vijay

  229. I have a backup file from SQL Server 2005 and i want to restore it to an existing database, i have NOT selected an option – overwrite the existing database, still data from the backup file is overwriting and my old in the database is lost.

    How to avoid above problem, can someone please help me.

  230. Is there any way to backup and restore the jobs in SQL Server 2005, especially without management studio? Or can we create jobs with TSQL via “sqlcmd”? If any, could you give an example, thanks for now..

  231. Hi Pinal,

    I’ve been reading your blog re:SQL and I could say I’m learning a lot! Thanks.

    But I have a question re: SQL 2005 & SQL 2000. I installed SQL2005 but when I am trying to restore a DB, I am receiving this error: ” Restore failed for Server ‘_’. The media family on device is incorrectly formed. SQL Server cannot process this media family. —> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The media family on device is incorrectly formed. SQL Server cannot process this media family.

    Okay, so I checked my version in SQL Management Studio:
    select @@version; I found out that it’s MS SQL 2000…

    I uninstalled my 2005 and reinstalled again but now I cannot connect to my server…

    Please help. Thanks. :)

    Lane

  232. when i try to backup or restore a database, the following error was arise.

    Msg 3234, Level 16, State 2, Line 1
    Logical file ‘Data_file’ is not part of database ‘CourseFinder’. Use RESTORE FILELISTONLY to list the logical file names.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Please anyone help me.

  233. Hiii Pinal,
    My one colleague had restored the database on my db. Unfortunately I didnt take a back up of the same. Can I get the same db before uploading.Please reply me ASAP. I tried with restore point but couldnt help it out. :(

  234. I have a table1 in my local database db1, I want to add that table with all its data in at db1 which is stored at my server. What should I do?

    • Best way is to Generate Create Table Statement using SQL Server Management Studio on your local database.

      Run that script on your destination server where you want to create that table.

      Once you have the table in new server, There are multiple ways of transferring data between source and destination. You can use Import Export Wizard, DTS, or Linked Servers to transfer data from one db (server) to another db(server).

      Easiest would be to use Import Export Wizard.

      You can also use Database Publishing Wizard to generate insert statement for your table. This generate a script that you will have to execute on your destination server.

      ~ IM.

      • I have the table created in the server db using the script. But I need to find a script that would transfer all the data from the client db to the server db. Import/Export is not a feasible solution as it would violate the PK, FK issues.
        Database Publishing wizard works from VS I guess? Not from the SQL Management Studio.

  235. Hi Pinal,

    I am trying ‘Index Tunning Wizard’ In sql 2000

    but its showing me this error msg

    ‘The Workload does not contain any events or queries that can be tuned against current database. Check to see that trace contains valid events or that SQL Script contains valid queries and you are tuning against the right database.’

    Can u help me in this!

  236. Hi Pinal,

    my database was corrupted and it showing like DATABASE (SUSPECT)

    can you help me how to recover,

    thanks

    Chandan

    • Hi ,

      Please suggest me, If i have restored the Database from fullback (.bak) file then it will overwrite the transaction log file (.LDF) file also or not ?? want your prompt response.

      Thanks in advance,
      Raghu

  237. Hello,

    I need to restore AD database to BC database on the same server. Both AD and BC are large databases. I can’t create a bak file as i dont have enough space in the database server. The Restore window in SQL mgmt studio takes lots of time to restore as database is large. Is there any SQL query to restore one database to another on SAME server. Kindly help me

  238. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  239. sir i want to go database field but i can not know how to start and where..
    please tell me any good insititute delhi/ncr who are chief and best for me..
    still i am waiting for quick response..

  240. Less experienced people can also use SQL Management Studio to restore database backups. Right-click on your database select Tasks->Restore and follow the instructions.
    There are also very simple 3rd. party tools like this http://sqlbackupandftp.com/restore/ to perform simple backup restoration. It can restore full backups from compressed and regular .BAK files.

  241. I want to take backup from MS SQL 2005 to drive (hard disk) using code i.e. when I click backup button through coding backup must be taken to drive(hard disk).

  242. Hi

    Im Mubeeth from Singapore,im want some help about Replication. Any one can help. if any one have please mail this address

  243. Dear sir,
    Hello i read your articles and use it its very easy to understand and implementation thanks for providing/sharing such nice articles,

    i have one question i just want to take a copy of one database from one pc and want to copy it in another pc,i am using sql server 2000,using script i am able to do it but can you provide the steps to do the script in sql server 2000 with snaps/detail so it will make my work easy.

    Thanks in advance

    Have a nice day

    • If they are on the same network, you can restore it usinh UNC Path

      In source server run this
      Backup database myDB to disk=’D:\myDB.bak’

      In target server run this
      Restore database myDB from disk=’\\Source_server_name\D\myDB.bak’

  244. Please let me know if i want to download the backup from server like IP and store into my laptop (i.e server is located in mumbai and Im staying in pune ) own dedicated server how to do it from SQL script or any special software is there …

    Regards
    Mayur

  245. Thanks a lot it worked for me..

    If your getting restoration failed while trying to restore the backup.

    Error msg: “restore failed for server microsoft.sqlserver.smo” Then use the below solution in MS SQL 2005.

    The below is the query i used :

    RESTORE FILELISTONLY
    FROM DISK = ‘C:\DBfilename.bak’
    GO

    “”once u execute the above query you get the logical filename” use this logical filename in the below query.”

    RESTORE DATABASE TestDB
    FROM DISK = ‘C:\DBfilename.bak’
    WITH REPLACE,MOVE ‘Logicalfilename’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test_DB.mdf’,
    MOVE ‘Logicalfilename_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test_DB.ldf’,
    MOVE ‘sysft_ifindexused_index’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test_DB.HRD’


    Regards
    Narashiman K Iyengar

  246. hello,

    i need some help here with sql server on win 2008 server running

    i have receive a BACKUP.BAK and i would like to restore it.

    i did a new DATABASE and i tried to run

    RESTORE FILELISTONLY FROM DISK = ‘D:BACKUP.BAK’
    Go

    came out with this error

    Msg 3242, Level 16, State 1, Line 1
    The file on device ‘D:BACKUP.BAK’ is not a valid Microsoft Tape Format backup set.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    how do i solve this? thanks

  247. Hi Pinal Dave,

    I have some amateur sort of questions. Kindly clear them. Thanks

    Refering to the original script:
    =========================
    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’

    1. If i had multiple log files when i took the backup then on restore operation, will multiple files be created or a single ldf and single mdf file will be created in Database? (i presume 01 mdf and 01 ldf for the said database be created.)

    2. When we create a backup of a database that is spanned on multiple mdf/ndf and ldf files, then is the structure of the resulting backup (.bak) file same as source files (does .bak files has single data and signle log file?)?

    Reply soon

  248. Your posts are really helpful.

    This is my implementation of the commands
    RESTORE DATABASE MyFiles
    FROM DISK = ‘E:\My Documents\SQL\storeMyFiles-Database\StoreMyFiles.bak’
    WITH MOVE ‘MyFiles’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles.mdf’,
    MOVE ‘MyFiles_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\myFiles_log.ldf’

    and this is the result
    Msg 3132, Level 16, State 1, Line 1
    The media set has 2 media families but only 1 are provided. All members must be provided.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    I have seen that you need 2 files which I don’t have even though the backup file seem to have the .mdf and .ldf files, however I have not seen the problem mentioned here.

    Maybe you can help. Thanks in advance

  249. Hi Pinal
    I would like to know how to take backup in sql server 2005 from shared server. I have knowledge about ordinary backup and restore but from web server there is no option. Your help would be helpful.

  250. Hi All,

    I am restoring the database using command line (SQL Express), when the restoration is successful will get the message as restore database successfully processed.

    If the restoration fails for any of the reason, it gives the message as restoration completed abnormally and it gives the “state” option/parameter also.. here how do i capture this state option ?

    Please help me ..

    Thanks,
    Shweta

  251. hi All

    I want restore file database of sql 2005 express into sql 2005 developer.
    But it not execute. How can I use it? Please help me! Thanks

  252. hi,
    I want restore database of sql 2005 while doing this i got following error:
    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Restore failed for Server ‘HOME-B00DD89814′. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 3 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

      • than for reply .
        sir i copy data base from my friend laptop.then i try to restore it on desktop.i first copy the data base to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
        as mention i go throw all restore procedure. according to me we have only one database whose backup i have copied. but for safety i save it on different drives (on my desktop) then after error mas i delete all .kindly plz help me urg.
        plz help me.

  253. hi,
    thanks for your 1st mail.
    sir is it possible that only one database has three related files.
    how to check that how much files are related to each other and how to take such database file while restoring.
    sir plz reply me early as possible .i am student not having much about this sql server.

  254. sir ,i execute this specid\fuc query. there i m getting DHW ma database file and its log file havig properties as TYPE: D for database and L for Log .
    plz guide me further , waiting for ur Reply..thanks in advance!!

  255. Hello SIr,
    thanks for your assist,
    May you tell me,
    How to schedule backup time for database ?
    ex:- suppose i would like to backing up my database 2 times per day, at 10 am and 6:00 pm , how should i schedule timing for database backup,

    • You can do it via SQL Server Agent. Create a new job and schedule it as you wish. The schedule section provides an access to schedule it. Goto section occurs every and select 8 and specify starting and ending time as 10 am and 6 pm respectively

  256. Hello, I need a little help with a restore process,

    I have this script in my system:

    RESTORE DATABASE [BasePrincipal] FROM DISK = N’C:\Sistema\backup.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    It work perfect in Windows XP, but not in Windows 7…

    The message is:
    Error al buscar el archivo ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Basesistema.mdf’ en los directorios, error del sistema operativo: 3(failed to retrieve text for this error. Reason: 15105)

    What can I do?
    Thanks for your help!!

  257. Hi
    I have a Sql server .bak file created in Sql 2005.

    I don’t have Sql server 2005 installed. Recently i have installed Sql server 2008
    When i am trying to restore the .bak file. I am getting the following error

    Restore failed for Database ‘BMTool’ microsoft.sqlserver.smo

    –create database permission denied in database ‘master’

    Why is it pointing to ‘master’ database when i am trying to create a new database with the bak file.

    Please suggest me the steps as i am new to sql server.

    • Hello Raj,

      You need to be a member of dbcreator server role. Because you do not have permissions to create a database, you are getting error when restoring a backup.

      master db stores information about server level objects, when you create database, information related to that action is stored in master database, that is why it tries to connect to master db.

      Please read Books Online for more information, below link can be used as quick reference

      http://msdn.microsoft.com/en-us/library/ms175892.aspx

      ~ IM

  258. Hi,
    I am having 9 database under once instance, need to write script to take backup. on network mapped drive, I have limited knowledge of scripting request your help to guide.

    Regards

    Pagi

  259. hi,
    I restoring database and my query is

    RESTORE DATABASE Library FROM DISK = ‘d:\data\Backup\Library.lib’ WITH MOVE ‘Library’ TO ‘d:\data\Library_Data.mdf’ , MOVE ‘Library_Log’ TO ‘d:\data\Library_Log.ldf’, REPLACE

    it returning me error message i.e.

    Msg 3234, Level 16, State 2, Line 1
    Logical file ‘Library’ is not part of database ‘Library’. Use RESTORE FILELISTONLY to list the logical file names.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    please help me what is the issue?

  260. I have a “monthend” database that needs to be restored every first day of the month from our production db. I perform local backups which look like this:
    backup_201112272300.bak
    As you can see the name changes every day due to the date. Is there a way to automate this process or am I stuck due to the file name changing?

  261. Hi,

    1. i have an sql 2000 full database backup, say mybackup1.bak. when i restore it to another sql 2000 instance, it failed. After investigating, i found in the ‘view contents’ options it says ***INCOMPLETE***. I myself did the backup of database from the customer pc. and copied to it to anothe system.

    2. i tried to restore mybackup1.bak with ms sql 2008 express edition. it says ‘specified cast of not valid. SqlManagerUI’. also the contents option of the restore says ‘Object cannot be cast from dbnull to other types. (mscorlib).

    could you please advise on this?

    Thanks,

    Ann Mary

  262. i copied the backup file from one system(sql server 2008R2) to another system (sql server 2008R2) can i restore this backup file without any errors? if both are in same domain what is the solution or not in the same domain what will be the solution?

  263. It works perfect when I run in SQL Server Management studio. however When I sent this sql command with Php the recovering database stuckin-restoring. Any Idea about this problem ?

    your help would be highly appreciated

    Thanks you.

  264. Hi Pinal Dave,

    I am creating one application for restoring the database. A user, select the .bak file through this application and need to restore the database (Which is reside in server). So, I pass the selected file as a binary data to the database. Is it possible to restore the database from a binary data?

    Thanks in advance.

  265. Dear Pinal Dave,

    I want to restore multiple database from single .bak file.
    How can i do this??? plz sugget me ……

    I have tried following steps from your blog but getting errors…
    RESTORE FILELISTONLY
    FROM DISK = ‘E:\MCSDB_NEW_BACKUP.bak’
    GO

    —-Make Database to single user Mode
    ALTER DATABASE TEST
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    —-Restore Database
    RESTORE DATABASE TEST
    FROM DISK = ‘E:\MCSDB_NEW_BACKUP.bak’
    WITH MOVE ‘TEST’ TO ‘F:TEST.mdf’,
    MOVE ‘TEST’ TO ‘F:TEST_log.ldf’

    ALTER DATABASE TEST SET MULTI_USER
    GO

    Error-
    Msg 3154, Level 16, State 4, Line 8
    The backup set holds a backup of a database other than the existing ‘TEST’ database.
    Msg 3013, Level 16, State 1, Line 8
    RESTORE DATABASE is terminating abnormally.

  266. Hi Pin,
    Getting an error i.e ‘The backup set holds a backup of a database other than the existing ‘OP1_2c659041_0de9_4bfc_93e8_fa394b503424′ database.
    ‘ while using the “Step 2: Use the values in the LogicalName Column in following Step.”

    Please help me out…

  267. Sir,

    I am restoring Database using TSQL as per given step
    but when i fired step 2 then one error has occured
    “Msg 3102, Level 16, State 1, Line 1
    RESTORE cannot process database ‘MDM’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.”

    so i fired next step: SET MULTI_USER
    GO
    then it display 1 msg
    Command(s) completed successfully.

    after this step tables of Database is not displaying
    kindly guide me………..

    Thanx In Advance

  268. Hi Pinal Dave,

    I have a question regarding the database restore.

    If we want to update / synch the data with the latest data on pre latest database, Can we restore the database by using the latest bak file.

    In this case , should we re create the users and provide them the accesss to db objects…?

    Please let me know best steps.

  269. DECLARE
    @BackupFile varchar(8000),
    @sql varchar(8000)
    SET @BackupFile = ‘D:\adventureworks2008r2.bak’
    SET @sql = ‘RESTORE DATABASE adventureworks2008r2 FROM DISK = ”’ + @backupfile + ”’ WITH FILE = 1,
    MOVE N”adventureworks2008r2_data” TO N”D:\adventureworks2008r2.mdf”,
    MOVE N”adventureworks2008r2_log” TO N”D:\adventureworks2008r2.ldf”,
    NOUNLOAD, REPLACE, STATS = 10, RECOVERY’
    exec (@sql)
    go

    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 23088 pages for database ‘adventureworks2008r2′, file ‘AdventureWorks2008R2_Data’ on file 1.
    Processed 3 pages for database ‘adventureworks2008r2′, file ‘AdventureWorks2008R2_Log’ on file 1.
    RESTORE DATABASE successfully processed 23091 pages in 9.307 seconds (19.382 MB/sec).

  270. –simple method
    RESTORE DATABASE adventureworks2008r2 FROM DISK = ‘D:\adventureworks2008r2.bak’ WITH FILE = 1,
    MOVE ‘adventureworks2008r2_data’ TO ‘D:\adventureworks2008r2.mdf’,
    MOVE ‘adventureworks2008r2_log’ TO ‘D:\adventureworks2008r2.ldf’

  271. Hello,
    Back Up and Restore with merge and duplication check
    I want to backup of my database and at time of restore i dont want to loss my old data i want to update database.How it is possible…?

    Currently in my application i take backup in XML file and while restore i loop through cursor…and Read XML file data one by one table’s Row…

    It takes too much time…..

    give me better solution… pls….

  272. Hi Pinal,
    I want to export or take backup of a table rows older than 30 days from the current time before deleting the same using a job scheduler but I’m unable to do the same using a query.

    Could you please suggest me the query to export or take backup of a table rows using the below condition.

    SELECT * FROM test.[dbo].[TBL_Datahistory] where SYSTEMDATE<=GETDATE() – 30

    Thanks in advance.

    Regards,
    Ravindra Gohil

  273. i want to take the backup automatically in sql server 2005 at regular interval…..plz suggest me…how to do this task……

  274. Step 1: Retrive the Logical file name of the database from backup.
    RESTORE FILELISTONLY
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    GO

    Step 2: Use the values in the LogicalName Column in following Step.
    —-Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    —-Restore Database
    RESTORE DATABASE YourDB
    FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
    WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
    MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’

    when i do this, i’m getting error msg as

    Msg 3132, Level 16, State 1, Line 1
    The media set has 2 media families but only 1 are provided. All members must be provided.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    i dont have the db so that i can backup again the db and all i have is just the Backup of my database… everytime i try to restore my db, it shows the same error..

    can anyone help me plzzz…

  275. hi pinal..
    Iam getting the below error while restoring my backup.please help me.
    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\Backup.bak’. Operating system error 2(The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

  276. Dave,
    We recieve monthly database updates in from of MDF and LDF files, Can I restore database from MDF and LDF files and replace the old ones with new ones?
    If yes can you please post script to automate it.

    Regards,
    Malii

  277. What is the Difference between:
    >BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak';

    AND

    >BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’ with Format;

  278. COPY .MDF FILE AND .LDF FILE FROM C:\PROGRAM FILES\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
    TO THE MACHINE WHERE YOU WANT TO RESTORE IT
    THEN SIMPLY GO TO SQL ENTERPRISE MANAGEMENT STUDIO AND ATTACHED THOSE .MDF FILE AND SQL WILL AUTOMATICALLY CREATE A NEW DATABASE IN 2008 FROM OLD 2000 DATABASE.

  279. Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority

  280. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  281. Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing ‘mydb’ database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    how can i resolve this.

  282. sir ,
    i am applying this code on my sql but it dose not work and gives a error
    plz solve error and reply me on my email address

    my code is this:-

    RESTORE FILELISTONLY
    FROM DISK = ‘E:new.bak’
    GO

    ALTER DATABASE new
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    —-Restore Database
    RESTORE DATABASE new
    FROM DISK = ‘E:new.bak’
    WITH MOVE ‘new’ TO ‘E:new.mdf’,
    MOVE ‘new’ TO ‘E:new.ldf’

    and error is this :-
    Msg 3154, Level 16, State 4, Line 7
    The backup set holds a backup of a database other than the existing ‘new’ database.
    Msg 3013, Level 16, State 1, Line 7
    RESTORE DATABASE is terminating abnormally.

  283. hi ,
    my database is located in other system. am connecting other user mode and take that db back up … but its showing error .. pls help me …
    BACKUP DATABASE db TO DISK=’d:\adw.bak’

  284. Hii Pinal

    Some times our restore takes too long time appox 8 hours to restore 160 GB data

    and Manytimes it hardly takes 3 hours

    using sqlserver R22008

    kindly tell a solution

    thanks

  285. Dear All,
    i would like to restore a set of databases (more than 100) and i would like to create a script to do that.

    How i can create a script to:
    1. create database base on the name of the bck file
    2. select the bck file
    3. restore database (and define a different location of mdf, ndf and ldf file)

    Thanks for your support.

  286. USE master RESTORE DATABASE FILELISTONLY HighTermAccount2013_02082014_143.bak FROM Disk = ‘c:\\BackupScheduler\\DataBase Backup\\HighTermAccount2013_02082014_143.bak’

    Error :
    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘.’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s