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.mdf'
/*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
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
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
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)
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
MDF = Primary Data File.
LDF = Log File.
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.
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
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:
[...] : 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…..
i need to copy all tables from a database to another database, so if it is possible to solve using any stored procedure…..
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.
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
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?
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
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…
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?
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.
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.
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
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.
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
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
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…
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
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
how to take backup of all the table of a database into another database
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.
Hello Pinal,
I need a script to take full mssql backup with all databses daily. you have this pls let me know.
Thanks
INdy
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
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
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)
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
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
*/
‘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
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