SQL SERVER - Restore Database Backup using SQL Script (T-SQL) « Journey to SQL Authority with Pinal Dave

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

February 25, 2007 by pinaldave

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://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQLAuthority Book Review, T SQL, Technology | 127 Comments

127 Responses to “SQL SERVER - Restore Database Backup using SQL Script (T-SQL)”

on June 11, 2007 at 11:50 pm senthil

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
on June 11, 2007 at 11:59 pm pinaldave

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)
on June 17, 2007 at 11:11 am Wilson Gunanithi . J

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
on June 17, 2007 at 11:17 am pinaldave

MDF = Primary Data File.
LDF = Log File.
on June 19, 2007 at 12:56 am Sandy

Hey Dude,

You have done a gr8 job by compiling all the scripts and docs in your blog.
Keep it up
on June 20, 2007 at 2:28 pm Wilson Gunanithi . J

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
on June 27, 2007 at 6:54 am Nagarajan

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.
on June 29, 2007 at 5:48 am asoo

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..
on July 11, 2007 at 2:33 am sapna

@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
on July 15, 2007 at 12:57 pm Bob

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 …
on July 17, 2007 at 4:14 am Nagaraju

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
on July 17, 2007 at 4:17 am Nagaraju

Hi Wilson,
Pipe means named pipes - u can back up on a network too
~Nag
on July 17, 2007 at 4:28 am Nagaraju

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:
on July 18, 2007 at 9:30 am SQL SERVER - Restore Database Without or With Backup - Everything About Restore and Backup Journey to SQL Authority with Pinal Dave

[...] : 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 [...]
on July 27, 2007 at 2:03 am Praveen Barath

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
on August 4, 2007 at 2:35 pm Arul

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…..
on August 4, 2007 at 2:43 pm Arul

i need to copy all tables from a database to another database, so if it is possible to solve using any stored procedure…..
on August 6, 2007 at 4:12 pm ANU

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
on August 23, 2007 at 10:45 am pritha malhotra

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.
on September 17, 2007 at 1:50 am Prashant Pandey

Is it possible to restore database using LOG file that is .ldf files.
on September 17, 2007 at 6:55 am Rishi Songara

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
on September 17, 2007 at 9:45 pm 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’
on September 18, 2007 at 12:25 am Rishi Songara

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’
on September 26, 2007 at 5:33 am Uttam

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
on September 30, 2007 at 11:21 pm Plahanov

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
on October 17, 2007 at 5:04 am vivek

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
on October 24, 2007 at 7:42 am Paul Fahey

Can sql 2005 Enterprise backup more then 1 DB at a time?
on November 3, 2007 at 1:36 pm satheesh

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
on November 6, 2007 at 7:54 pm eseyas

thanks for the comments you are providing so i am hope full to see more assistance matters related to aDBA
on November 13, 2007 at 5:05 pm vijay

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…
on November 28, 2007 at 7:25 am Ronsi

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
on December 6, 2007 at 6:20 pm 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
on December 10, 2007 at 1:25 am henryk

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?
on December 15, 2007 at 10:07 am Ishwardeen Maurya

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.
on December 18, 2007 at 5:52 pm Diwakar

Hi, Pinal

Good One .really Helpful

Diw….
on December 25, 2007 at 11:25 am Amit

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…
on December 27, 2007 at 2:34 pm Gopal

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
on January 10, 2008 at 12:37 am luis guzman

Ami…do you mean to upload a database doing an import or something like that?
on January 12, 2008 at 2:50 pm Karthik

hi pin,

im karthik

can we restore a particular table in a database

awaiting for your reply

thanks

Karthik. P.
on January 13, 2008 at 8:00 pm Rao

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
on January 17, 2008 at 3:59 pm Rajib Paul Choudhury

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
on January 28, 2008 at 10:27 pm Luda

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
on January 29, 2008 at 2:42 pm Aamir

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..
on January 31, 2008 at 10:13 pm Rajesh

How to create backup in LAN
on February 12, 2008 at 11:29 am Arvind

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
on February 12, 2008 at 11:24 pm Jerry

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.
on February 20, 2008 at 4:52 pm balaguru

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
on March 19, 2008 at 9:06 am vijay

how to take the backup in SQL SERVER 2005 in SQL Script format
pls give the steps…..

vijay
on March 20, 2008 at 6:46 pm rajesh

How to find the mydb.mdf file in my machine
on March 21, 2008 at 4:21 pm Navin

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…
on March 23, 2008 at 8:43 pm Kaliem

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
on March 26, 2008 at 6:40 am Munshi Verma

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
on March 27, 2008 at 3:11 am 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
on April 3, 2008 at 12:27 pm Sathish

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.
on April 4, 2008 at 5:32 pm vijayakumar

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
on April 9, 2008 at 3:02 pm Fedaa

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, …)?
on April 10, 2008 at 1:57 pm BlackHawk

Hello,

I have a question how to restore sql server 2000 files without extension into sql server 2005?

I need ur urgent reply…
on April 16, 2008 at 3:06 pm dhaval

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
on April 19, 2008 at 12:14 pm Ravi

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…
on April 24, 2008 at 9:54 am rikki

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
on April 24, 2008 at 1:56 pm simi

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.
on April 29, 2008 at 8:21 am mari

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…
on April 29, 2008 at 6:29 pm Praveen Barath

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
on April 30, 2008 at 7:38 pm Siva

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
on May 1, 2008 at 1:22 am Praveen Barath

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
on May 1, 2008 at 1:10 pm Siva

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
on May 22, 2008 at 8:52 pm Harikrish

Is it possible to restore database using only the LOG file that is .ldf file.
on May 28, 2008 at 12:27 pm Vikas

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
on June 3, 2008 at 4:05 pm Ritish

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
on June 11, 2008 at 12:57 pm yogesh

I want to ask that data of SQL Server is stored in which format??.mdf or .ldf??

How to take backup of data..
on June 18, 2008 at 5:44 pm Amit

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.
on June 18, 2008 at 5:52 pm Glyn

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?
on July 4, 2008 at 6:43 am Moonstar

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.
on July 4, 2008 at 6:52 am Moonstar

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
on July 8, 2008 at 1:56 pm Amit

how to take backup of all the table of a database into another database
on July 9, 2008 at 11:11 am sarika

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
on July 11, 2008 at 5:48 pm Prashant

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
on July 12, 2008 at 6:08 am sravs

hi pin,
how can I take a back up of table,instead of the whole database.
on July 13, 2008 at 1:08 am Indy

Hello Pinal,

I need a script to take full mssql backup with all databses daily. you have this pls let me know.

Thanks
INdy
on July 18, 2008 at 12:36 pm Mahesh

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
on July 18, 2008 at 6:05 pm santhosh

please give the information as soon as possible
on July 18, 2008 at 6:05 pm santhosh

how to back up the database
on July 23, 2008 at 8:24 am SS A Islam

how can backup database from SQL Server using vb6
on August 4, 2008 at 12:14 pm AG

@mahesh

RESTORE DATABASE ABC FROM DISK = ‘\\servername\c$\XYZ.bak’
on August 4, 2008 at 12:16 pm AG

@indy.

Pls use database maintaince plan and schedule them accordingly.
on August 4, 2008 at 12:19 pm arjouna

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
on August 4, 2008 at 3:36 pm Suresh

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
on August 6, 2008 at 1:42 am Shailesh

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
on August 8, 2008 at 12:07 am Rana

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.
on August 8, 2008 at 10:27 am akram

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~
on August 10, 2008 at 3:07 pm Ahmer

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.
on August 11, 2008 at 11:39 am Lipika

Hi Dave,

I have a question i.e. What is the difference between SQL and T-SQL??
Please help me

Thanks & Regards,
Lipika
on August 12, 2008 at 2:31 pm Paul

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,
on August 13, 2008 at 1:39 am Imran Mohammed

@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.
on August 13, 2008 at 2:31 pm Devdatta Bhosale

Please tell me who give me training of sql 2005 DBA. in pune.
on August 15, 2008 at 10:39 pm Raf Asghar

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
on August 21, 2008 at 4:44 pm Sai

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.
on August 22, 2008 at 6:00 am Imran Mohammed

@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.
on September 4, 2008 at 8:40 am Raghu

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
on September 6, 2008 at 5:45 pm happy

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..
on September 8, 2008 at 10:50 am Shailesh Khanesha

Dear,

Can I Get .mdf file location using T-SQL Statement??

Thanx

With Regards,
Shailesh Khanesha
(NIC)
on September 17, 2008 at 8:10 pm Chitra

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
on September 25, 2008 at 8:21 pm Bikram

hi

SQLServer2005 backupdatabase in need to move that database to SQLServer2000.

it giving me …compatability error

is there any work around
on October 2, 2008 at 11:24 pm Ashish

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
on October 3, 2008 at 11:43 am balaji

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
on October 6, 2008 at 1:03 pm Dhananjay

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
on October 15, 2008 at 5:26 pm swati

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

please help me!!!!!!!!!!!!!! immidiately
on October 15, 2008 at 5:34 pm swati

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.
on October 17, 2008 at 3:49 pm Srini

Hi,
please let me know How to restore .MDB file using t-sql?
on October 30, 2008 at 10:11 am Haque

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
on October 31, 2008 at 2:35 pm Ravindra

System.data.Sqlclient.sqlerror
on November 1, 2008 at 12:53 am Afsi

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!
on November 4, 2008 at 6:42 pm babu

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
on November 10, 2008 at 12:38 pm Vixen

hi, please help me, how do I revert the “BACKUP LOG WITH TRUNCATE_ONLY” command?

Thanks
on November 29, 2008 at 3:37 pm YordanGeorgiev

– 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
*/
on December 4, 2008 at 11:28 pm Sri

‘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…
on December 8, 2008 at 7:29 pm Eric

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
on December 18, 2008 at 9:41 pm Jason

Eric, it’s in msdb database.
on December 19, 2008 at 3:32 am Priya

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.
on December 19, 2008 at 9:11 am Imran Mohammed

@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
on December 19, 2008 at 11:17 am Tejas Shah

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
on December 19, 2008 at 5:12 pm Priya

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
on December 19, 2008 at 5:37 pm Priya

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.
on December 20, 2008 at 7:46 am Imran Mohammed

@ 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.
on December 23, 2008 at 11:14 pm Priya

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..
on January 2, 2009 at 10:36 pm Priya

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.
on January 4, 2009 at 5:58 pm Rushi

hello

plz let me know, under which situation might you restore the database backup…