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)
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
Seems like you have to update to R2 version of your SQL Server
more on version numbers can be found here:
http://www.sqlteam.com/article/sql-server-versions
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.
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
hey if you have mdf and ldf files then you can directly attach these files to your database server…
MDF = Primary Data File.
LDF = Log File.
Hi,
I need to restore a SQL 2008 DB to SQL 2000 DB, can you please tell me the steps.
Hello HP,
A database from newer version can not be restored on older version or SQL Server.
Regards,
Pinal Dave
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.
Make sure the backup file is located at server’s directory and not in your local system
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.
Hey Dude,
You have done a gr8 job by compiling all the scripts and docs in your blog.
Keep it up
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
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.
Becuase the database you are trying to restore is currently used by someone. Close the connections and try again
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.
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..
@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
It is not true for backup but for restore you need to disconnect from the application
no this is not true dost i think there is some problem in syntax or server
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 …
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
Hi Wilson,
Pipe means named pipes – u can back up on a network too
~Nag
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
[...] : Pinal Dave (http://www.SQLAuthority.com) SQL SERVER – Restore Database Backup using SQL Script (T-SQL) SQL SERVER – Recovery Models and Selection BOL – Full Restore BOL – Restore [...]
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
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…..
What did you mean by its not working?
i need to copy all tables from a database to another database, so if it is possible to solve using any stored procedure…..
Use import/export wizard from Management studio
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
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.
1 No
2 Better keep latest week’s backup files and delete others
Is it possible to restore database using LOG file that is .ldf files.
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
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’
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’
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
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.
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
Can sql 2005 Enterprise backup more then 1 DB at a time?
It is a sequential process. Second backup starts only after the completion of the first one
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
Use [ad-catagories] instead of ad-catagories’
thanks for the comments you are providing so i am hope full to see more assistance matters related to aDBA
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…
Use import/export wizard from management studio
It will copy structures as well as data
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
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
Hello guys,
I am getting a message: DB in use everytime I try to make a DB resore from the database.bak file. Any idea?
As it says you need to close all the connects that are accessing the database which is to be restored
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.
Hi, Pinal
Good One .really Helpful
Diw….
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…
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
Ami…do you mean to upload a database doing an import or something like that?
hi pin,
im karthik
can we restore a particular table in a database
awaiting for your reply
thanks
Karthik. P.
No
You have to have data in a text file and use bulk insert
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
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
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
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
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..
How to create backup in LAN
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
I am trying to do a restore and it seems like it its taking quite a while. How long should it take for a 3 MB file?
Thanks.
3 MB should take only few seconds
Check whether the RAM is used by some other processes
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
how to take the backup in SQL SERVER 2005 in SQL Script format
pls give the steps…..
vijay
How to find the mydb.mdf file in my machine
exec sp_helpdb mydb
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…
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
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
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
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.
Create a procedure with the following code
backup database db_name to disk='path'
Schedule it as a job to run daily
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
Read about sp_addLinkedServer in SQL Server help file
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, …)?
Hello,
I have a question how to restore sql server 2000 files without extension into sql server 2005?
I need ur urgent reply…
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
Mostly likely there is no permission for that shared folder
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…
It is becuase you were trying to restore backup of 2005 version to 2000 version. Thats not possible
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
This post may help you
But you need to change it according to your need
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/backup-database.aspx
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.
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…
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
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
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
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
Is it possible to restore database using only the LOG file that is .ldf file.
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
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
I want to ask that data of SQL Server is stored in which format??.mdf or .ldf??
How to take backup of data..
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.
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?
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.
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
how to take backup of all the table of a database into another database
Only tables or procedures, views,etc too?
Read about Backup database in SQL Server help file
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
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
hi pin,
how can I take a back up of table,instead of the whole database.
1 Script the object using Generate script
2 Copy data to a text file using a bcp
Hello Pinal,
I need a script to take full mssql backup with all databses daily. you have this pls let me know.
Thanks
INdy
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
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
please give the information as soon as possible
how to back up the database
Backup database db_name to disk=’file_path’
how can backup database from SQL Server using vb6
@mahesh
RESTORE DATABASE ABC FROM DISK = ‘\\servername\c$\XYZ.bak’
@indy.
Pls use database maintaince plan and schedule them accordingly.
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
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
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
Search about Parameter sniffing in Google/Bing
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.
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~
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.
Hi Dave,
I have a question i.e. What is the difference between SQL and T-SQL??
Please help me
Thanks & Regards,
Lipika
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,
@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.
Please tell me who give me training of sql 2005 DBA. in pune.
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
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.
@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.
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
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..
Dear,
Can I Get .mdf file location using T-SQL Statement??
Thanx
With Regards,
Shailesh Khanesha
(NIC)
EXEC sp_helpdb 'db_name'
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
hi
SQLServer2005 backupdatabase in need to move that database to SQLServer2000.
it giving me …compatability error
is there any work around
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
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
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
i have script of database.
my database deleted mistakenly.
before delete i generate script.
can i retrieve tae database.
please help me!!!!!!!!!!!!!! immidiately
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.
Hi,
please let me know How to restore .MDB file using t-sql?
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
Read about sp_attach_db in SQL Server help file
System.data.Sqlclient.sqlerror
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!
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
hi, please help me, how do I revert the “BACKUP LOG WITH TRUNCATE_ONLY” command?
Thanks
– 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
‘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…
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
Eric, it’s in msdb database.
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.
@Priya
You cannot do it through GUI (Interface)
But you can do it through scripts…
BACKUP DATABASE Foo TO DISK = ‘\\myserver\myshare\foo.bak’ WITH INIT
Reference :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6096
Regards
IM
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
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
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.
@ 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.
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..
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.
hello
plz let me know, under which situation might you restore the database backup…
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?????????
may i know the architecture of the sql server2005. please any one give me the details of it.
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
@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.
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?
@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
Can i take backup of database using query analyzer.
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
@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.
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
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
@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.
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
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.
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
[...] SQL SERVER – Restore Database Backup using SQL Script (T-SQL) [...]
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
Hi all,
How to view the backup files of the restored database?
Regards
Sujoy
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
HI,
I TRY TO RESTORE DATABASE I JUST FLASH ERROR “RESTORE DATABASE is terminating abnormally.”SO WHAT IS SOLUTIONS.
PLS GIVE ME SOLUTIONS .
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!!
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.
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????????????
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.
@ 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.
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
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
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
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.
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,
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.
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!
@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.
@Chris,
Let us know if Irman’s wonderful solution helped.
Kind Regards,
Pinal
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.
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.
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.
@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.
Hi
I have doubt can we restore sql server 2008 database backup into sql server 2005.
With regards
Bharath kumar
Hi
I have doubt can we restore sql server 2008 database backup into sql server 2005.
Thanks in advance
With regards
Bharath kumar
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..!!
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…!!!
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.
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 ?
@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.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO
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
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
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.
@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.
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
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
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
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!!
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
You are awesome!!! Worked flawlessly and saved me a bunch of time.
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
can any one help me for db backup using query??
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
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
Thanks Pinal for your help.
It really helped me a lot
how to take backup of database using query .
pls help me out.
????????????????
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…
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….
@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.
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……????
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
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
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 ;)
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
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
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..
Hi, i want to know how to take backup from remoteserver to local server…. reply imd..
in that i am using sqlserver 2005 using asp.net with c#
Hi, i want to know how to restore database(.mdf) in sql server 2005.
Dear Sirs
I have a backup from SQL Server 2005 and want to restore it on SQL Server 2000. Is this possible.
Regards
abrar
This issue was resolved my using wizard functionality of moving sql2000 to sql 2005
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?
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
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.
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.
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’
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
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
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
I am a little closer but how do I get the position of the last record?
RESTORE HEADERONLY FROM DISK = ‘C:\data\myDB.bak’
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
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
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.
You need to start the transaction before any Insert or updated
Begin Transaction
your insert / update statements
commit or rollback transaction
Regards
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!!!
dear sir
i have 10 database some delete one of my database how to find who is delete my database from server …
Regards
Rajesh
what is the difference between replication & mirroring..?
&&&&&&&&&&&&&&&&&&&&&&
what is the difference between Logshiping & replication ..?
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.
Anybody can tell me. How can I restore one table or entity from full database backuup
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
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
@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
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.
Hello Vaibhav,
restore the full backup with NORECOVERY option and then restore the transaction log backup.
Regards,
Pinal Dave
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….
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
Ya Actuly, Database in not readable if restored with NORECOVERY option.
So we want to restore with RECOVERY only to make it readable.
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
Hello Ravi,
What error are you getting?
Please also share the command that you are using to take the backup of database.
Regards,
Pinal Dave
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
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
Hello Senthi,
I think you have copy of .mdf and .ldf files. You can use sp_attache_db stored procedure to attach the database.
Regards,
Pinal Dave
Hello pinal dave,
This command is working fine…
Thanks for your help…
Thanks
T.Senthil kumar
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’
yeah. Thanks for noticing it. I fixed it.
Hi Pinal,
I have to restore SQL Server 2000 Files (Cognos Samples) into SLQ 2008, your help is greatly appriciated.
Thanks in advance,
Sunil.
I am able to import the samples in 2008. Thanks.
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
Hello Raj,
You can set replication between SQL Server 2000 and 2005 but SQL Server 2005 express edition can be a subscriber, not a publisher.
Regards,
Pinal Dave
How can I restore backup of sql server2000 in sql server2005?
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
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
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…. ;)
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
Exactly what I was looking for.
Do you sell SQL Authority T-shirts?
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
I dont think there is a way to do this
May be third party can support
But how does it matter?
I have updated table with update query, now i want to restore previous data.
If you used transaction, you can rollback it
Otherwsie restore data from the latest backup file
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?
Retrive the Logical file name of the database from backup.
Restore filelistonly from disk=’back_up_file_path’
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’
Your question is not very clear
What do you want to know?
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
Restore database Prod_Teamwork_EU10_04282010
from disk=’\\kftusoktulsps14\public\Infosys\EU DB backup\Prod_Teamwork_EU10_BAK.bak
‘
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?
There is no way other than giving the proper permission to access the file
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),
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
You cant directly read. You need to make use of some third party log readers
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
Is it a shared file?
Try
‘\\10.15.21.29\g$\Intel\Mylearning4Saba.BAK’.
Tried it already
Make sure the server has enough permission to access that file
can any body plase explain me
How Backup Works in sql server 2005
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).
hi…
i m nt restore the db 2000 on the srver godday properly and nt get the value get the from db if u can help me than soooooooooo thank .
Can you give some claer informations?
It is difficult to understand what you want
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
If you have latest backup, you can try restoring it
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
It is not easy to get data back until you try some third party tools that read data from log files
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.
Backup database your_db to disk=’file_path’
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
Can you explain what is schema level export?
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
Script the alter table statements for the newly added columns and run them at the client’s database
by mistake i have delete delete database from sql server and i have no backup plz tell me how i restore or recover db (mdf, ldf) files.
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.
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
Yes. It is possible. Read about Restore command in SQL Server help file
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.
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
hi ,
I have .bak file i want to import it into mysql .how?
You can’t directly import to mysql. Restore it in SQL Server and use import/export wizard from there
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
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
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
Are you restoring backup file to the lower version of SQL Server?
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
Thankyou very much it helped me a lot
Hi Dev,
Will you please give an example how can i create a sql-server 2008 job to restore the latest database.
Thanks,
Rajnish
Refer this post and apply the logic used there
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/backup-database.aspx
Hi,
I have 400 sql Db’s backup file (*.bak). i need to restore all the 400 DB’s in one single shot. how can i do it?
You need to loop thru all the files and do restore
Read about xp_cmdshell to know about reading file names from the file system directory
Hi,
how to backup a database from local system to public ip?
Please help me ASAP
Thanks in advance.
Use UNC Path like ‘\\sysname\filename
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
HI There
How do you automate backup and recovery using a stored procedure?
i did not know where to put my Question so i have asked here
how to change the Administrator username/pass for sql 2008 ?
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
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 …
What is the format of the file? Is date value appended?
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.
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
——————————
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!!!!!
hiii,
i forgot to the save the back up file 30-09-2010 but its automaticaly remoed can i recover tat pls tel me soon
hiii,
i forgot to the save the back up file 30-09-2010 but its automaticaly removed can i recover tat pls tel me soon
pls give me a solution
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…..
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
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!!
Why are you using two restore statements? The second one seems to be correct
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
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.
HI,
I want to insert logical file names in a table from database backup file (dbbackup.bak). Please suggest me.
You need to restore the database backup file
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
Use master
Go
–your restore command
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
If you want to do it regularly, have a look at Replication
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…
You need to loop thru the physical files. Use xp_cmdshell
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….
I think in OS like Windows 7, it is possible
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.
You need to use REPLACE option to overwrite the existing logical files
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.
It emans that the database with the same logical files exists already. You need to use REPLACE option at the end of the script
Hi Pinal,
how can we get the logical names of a database from the Backup file.
Restore filelistonly from disk=path of backup file’
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
You can take a log backup and then truncate it
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
Thank you very much!
Very helpfull
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.
It will overwrite even if you dont select that option. That option is there to replace the database with different mdf and ldf names with different locations
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..
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
it is not possible to restore database of higher version to lower version. Generate script of the database and copy data using SSIS
Hi Pinal,
Can you tell me how to restore system databases in SQL server 2000.
Thanzzz for the sql queries. The queries are really helpful….
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.
It means that the logical file path does not exists in the current server. You need to use RESTORE WITH MOVE option to specify the correct path
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. :(
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.
Sir, how can i restore 2008 Db backup to 2005 database ?
No it is not possible. You need to generate script and run in 2005 version. Use SSIS or import/export wizard to copy data
This is very useful blog, I read very often. Thanks for sharing.
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!
Hi Pinal,
my database was corrupted and it showing like DATABASE (SUSPECT)
can you help me how to recover,
thanks
Chandan
Hello Chandan,
May i help you??, what is the version of your SQL server database??
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
Hi…. How to taking a Backup of SQL Server 2008 using SQL Query….Kindly help me…
Backup database your_db to disk=’C:\db_name.bak’
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
If your goal is to keep them same and if you dont want one of the databases, you can just drop the database BC and rename AD to BC.
[...] Restore Database Backup using SQL Script (T-SQL) [...]
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..
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.
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).
Use this
Backup database db_name to disk=’your path’
Hi
Im Mubeeth from Singapore,im want some help about Replication. Any one can help. if any one have please mail this address
Please tell us what problem you have
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’
Once again, your blog has given me valuable information! Thanks!
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
If there are connected via VPN, you can directly run a restore command. Otherwise copy it to laptop and restore it there seperately
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
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
The command should be
RESTORE FILELISTONLY FROM DISK = ‘D:\BACKUP.BAK’
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
Dear Mayur.
Ask a question Comprehensibly. Exatly what do u want.
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
Are you trying to restore the database with higher version to the lower version of SQL Server?
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.
You can take a backup in shared server as you do in the local server
Is there any way to restore the Stored Procedure in SQL 2005 ??
Thanks,
Yash
You can generate script of the procedure and run it in target server
Hi,
How to kill all active connections to DB using SQL script…
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
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
Post the actual code you have used. Also did you get any error?
Ok thanks I have executed it.
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
It means that the backup file has three related files. You need to specify all these three files to restore it properly
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.
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.
Try this code to see the number of files
Restore filelistonly from disk=’backup_path_and_file’
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!!
excuse me sir
how to restore multiple backups at once using sql script(t-sql)
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
I want to restore/replace the new DB on any other server’s old DB with same name
1 Copy the backup file into new server’s location
2 Use this command in new server
Restore database db_name from disk=’file_path’ with replace
Hi All,
Is possible create a task on SQL 2008 R2 for the DB can be restore automatically?
Any Advice ?
Regards
Yes you can make use of a job and schedule it to run periodically.
Hello, How can I restore 100 + Database with just one script ?
You can have all of them in a folder and write a query to get file names using xp_cmdshell and dynamically build restore database statements and execute them
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!!
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
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
Refer this post. It will backup all databases in the folder you specify
http://beyondrelational.com/ask/madhivanan/questions/605/backup-all-databases.aspx
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?
My backup file has been saved with name Library.lib
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?
Check this
http://beyondrelational.com/modules/2/blogs/70/posts/10795/backup-database.aspx
hi,
How can I take a back up from database that no one else could restore it?
thanx and best regards.
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
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?
You can restore it. Have you tried it?
Hi Pinal Dave i am expecting answer please…
hi plz tel me sql server backup and restore steps…
am waiting 4 ur rply
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.
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.
Dear Pinal Dave,
I would be better if you could provide a solution/anwer that I am looking for.
Regards,
Ann Mary
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.
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…
It means you are trying to restore the db backedup from higher version to lower version
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
As it says you are connected to the database MDM. So switch to master database
USE Master
GO
your restore script here
How to restore database from sql script?
Restore database db_name from disk=’file path’
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.
Restoring database will not affect users and their permissions
how to restore databases of express edition to standard sql server edition
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).
–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’
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….
excelen post..
Tank you!!!!!!!
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
declare @d char(8), @sql varchar(1000)
set @d=convert(char(8),getdate(),112)
set @sql=’SELECT * into new_table_’+@d+’ FROM test.[dbo].[TBL_Datahistory] where SYSTEMDATE<=GETDATE() – 30 '
exec(@sql)
Thanks Madhivanan for the reply, but i want to export the selected rows into a .csv file instead of putting them into a new table.
Use bcp http://beyondrelational.com/modules/2/blogs/70/posts/10798/bcp-export-data-to-text-file.aspx
i want to take the backup automatically in sql server 2005 at regular interval…..plz suggest me…how to do this task……
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…
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.
It means that there is no such file exists at the path you specified. Make sure to use the correct path
Thank You…
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
Somebody please Help!
What is the Difference between:
>BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’;
AND
>BACKUP DATABASE Database_Name TO DISK = ‘C:\.bak’ with Format;
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.
[...] Restore Database Backup using SQL Script (T-SQL) [...]
[...] Restore Database Backup using SQL Script (T-SQL) This is one of my most popular blog posts where I explained how to take backup using SQL Script in a few T-SQL statement. There are more than 500 comments on this blog so far. [...]
Hi Sir
i want to restore my database on my desire location by sql query
please tell me how can do that…
Thanks
nce