.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Restore with different name on same server

Posted By:      Posted Date: September 11, 2010    Points: 0   Category :Sql Server
Hello All... I have a database XYZ (Full Recovery Model,300 users in it size aroung 134800 MB,) on Server 1(dev) I need to back it up to some location(z drive/disk) Then I need to restore to same Server1(dev) with different name XYZ_ABC All I know is to do in GUI and writing Basic Scripts ,but my lead needs Scripts . Steps: 1.Doing an backup to disk z 2.Restoring it but I am concerend about renaming the DB and restoring it on same server1(dev) .... Can u guys help me on giving the Step -Step -Procedure to do ....What will be the steps and Scripts ?? and after restoring do i need to assign permissions to alla existing 300 users ? (may be this one depends on my lead) Thanks

View Complete Post

More Related Resource Links

SQL Server Backup and Restore (video)

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Backup SQL Server 2008 R2 Restore on SQL 2008

I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance. When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo) How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?Ian Ceicys

SQL Server database restore with multiple logicalfiles by using t-sql

Hi,   I have set of database backups created in the production sql server 2005. I need restore these backups into another sql server 2005 by using t-sql script(stored procedure). Each database will have different number of logical files in it. The physical location of the target server may be different for these logical files. I know that i should use WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf' clause. Now i want to know how would i put it in a t-script stored procedure such a way that it automatically identifies all the logical files and create a physical file on the target server. User will supply source of bakup file and target folder for mdf,ldf, etc files. Do i need to populate the sql query as string by looping through all the logical files? If so how would i execute the populated sql query as string ?  Mr Genius

Error when running an SQL script to restore a database on SQL Server 2008 R2 Express

Hi, At work, I use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. At home, I also use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. Whenever I do work on the database at work, I usually restore the database at home (or vice versa) I have been using the following SQL scripts to back up the database and to restore the same database (at work or at home). BackupRealEstate.sql USE Master BACKUP DATABASE [RealEstate] TO DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH NOFORMAT, INIT, NAME = N'RealEstate-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO RestoreRealEstate.sql USE Master RESTORE DATABASE [RealEstate] FROM DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO Everything worked fine until I replaced my 8yo home computer with a newer and faster one. My new home computer is running Windows 7 Home Premium and I use the latest SQL Server 2008 R2 Express. When I tried to run the above restore SQL script, I got the following error message: Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. There was no other application/process using this database. Can someone please help me? Regards,

SQL Server 2008 Database Restore(Database Mirroring)

HI,I tried to impliment Database mirrorin in SQL Server 2008.I have 3 Sqlserver 2008 Eneterprise Edition in 3 diiferent computers with same domain.Pricnicipal server =TCP://SPHDev.sphinet.com.sg:5022.Mirror=TCP://SPHPrimary.sphinet.com.sg:5022 and witness =TCP://wsc211.sphinet.com.sg:5022.But when i click start mirroring button i got an error TITLE: Database Properties------------------------------ An error occurred while starting mirroring. ------------------------------ADDITIONAL INFORMATION: Alter failed for Database 'SPH_MSCRM'.  (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://SPHPRIMARY.sphinet.com.sg:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418).This is the error message i got from Error Log"2009-06-03 17:01:08.89 spid27s     Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://SPHPRIMARY.sphinet.com.sg:5022"I restored Principal server DB  by using RESTORE WITH NORECOVERY and now this  database in mirror server is now look like as DBName(Restoring....).How i can identify

Cannot Restore Backup from Sharepoint Server 2007 to Sharepoint Server 2010 Trial Version

After installing the trial version of Sharepointn 2010 I wanted to restore my Sharepoint 2007 sites to it but received the following error:  "The site collection could not be restored. If this problem persists, please make sure the content databases are available and have sufficient free space." I went on to create a new content database in sharepoint 2010 but it still did not work. The old database i.e the one used by the sharepoint 2007 server is SQL 2005. During the installation of the sharepoint 2010 version I selected SQl Server Express for the database. Is there any specifics for migrating from MOSS 2007 to sharepoint 2010? Thanks for helping

Trying to Restore The Application Server Administration Service Timer Job in MOSS 2007


I was using this link to try and repair a failed service job.


What happened is that it blew away my Application Server Administration Service Timer Job.

Is there a way to restart that job?


restore SQL server 2005 database on new SQL server 2008 not working

please any one advice me ,i have Great Plain database on SQL server 2005 when i tried to install new SQL server 2008 and restore my Database the GreatPlain not working well as well the Data Base

Do I need free space when doing full database restore in SQL Server 2005?


I have a drive 'F' with 40G free space. I try to restore a database whose data + log file size is 60G.

The restore fails. Is it because a full restore needs  free disk space of 60G in this case?


SQL 2005 backup and restore onto new server - for DR and testing purposes

all I have a beefy server that currenty runs my SQL server, however i would like to backup this server and restore it to a different server then use SQL replication in a DR solution.


I have W3K R2 SP1 with SQL 2005 (version 9.00.3042.00)
The phycial server has:
DISK 0 - C: (conatins OS and system files 12GB)
and E: (conatins SQL logs 124GB)

DISK 1 - D: (some program files, and the SQL DB 408GB) - Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

all DISK's are NTFS formatted

How can I best accomplish my goal?

Should I use NTBACKUP and then do a restore onto a new server?
Should I build a new sever with W3KR2 with the same partitions and then backup the sql db via sql and do a restore onto the new server?
could I utilise hyper-v?

I do not have much experience with MS SQL server.


cannot restore sql 2000 database in sql server 2005 in windows 7


cannot restore the databse made in sql server 2000 in sql server 2005 in windows 7 with thw following error message

TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'KHURANA\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

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


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

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



plz help me out

Backup and Restore Sharepoint 2010 to new server

I have a Sharepoint 2010 server running SQL Express. (SERVER A)     I have an identically configured Sharepoint 2010 server running SQL Express (SERVER B).   I want to migrate the data from SERVER A to SERVER B.   Does anyone out there have a step by step guide for this?

Restore Failed for Server: SQL Express 2005


I am moving my data base from one computer to a new one. I placed the .bak file in the backup folder on the new computer (Windows Vista). I keep getting these errors I have checked the security settings and my user account has full privledges. I added my windows login to the SQL Administrators as well.

Here is the error msg.:  TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'laptop\sqlexpress'.  (Microsoft.SqlServer.Express.Smo)

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


System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)'

sql server will not start - Database 'model' cannot be opened. It is in the middle of a restore.

Sql server service will not start after server reboot:

2009-08-13 14:15:43.94 spid7s      SQL Trace ID 1 was started by login "sa".
2009-08-13 14:15:43.97 spid7s      Starting up database 'mssqlsystemresource'.
2009-08-13 14:15:43.97 spid7s      The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2009-08-13 14:15:44.30 Server      A self-generated certificate was successfully loaded for encryption.
2009-08-13 14:15:44.30 spid7s      Server name is 'WIN-37R9WFWAJIB\MSDB1'. This is an informational message only. No user action is required.
2009-08-13 14:15:44.32 spid10s     Starting up database 'model'.
2009-08-13 14:15:44.32 Server      Server is listening on [ 'any' <ipv6> 49167].
2009-08-13 14:15:44.33 Server      Server is listening on [ 'any' <ipv4> 49167].
2009-08-13 14:15:44.33 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSDB1 ].
2009-08-13 14:15:44.33 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2009-08-13 14:15:44.35 spid10s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2009-08-13 14:15:44.38 Server      Server is listening on [ ::1 <ipv6> 491

Moving full-text catalog from one server to another using backup / restore


Dear Sirs,
I'm having a problem with Full-Text Catalog in SQL SERVER 2005.
The problem is: I did a backup of a database that has a full-text catalog from one server and restored on another server.
The restore worked perfectly, but the catalog is down because it is pointing to the physical path from the source server.
To recover this full-text catalog I tried the commands below:

use master

sp_detach_db 'SiteEng2004'

(FILENAME = 'D:\SQL SERVER\DADOS\SiteEng2004\SiteEng2004_Data.MDF'),
(FILENAME = 'E:\SQL SERVER\LOGS\SiteEng2004\SiteEng2004_Log.LDF'),

Unfortunately it did not work and the following error message occurs :

Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "D:\SQL SERVER\DADOS\SiteEng2004". Operating system error 5: "5(error not found)".

Could anyone help me on what I should do to restore this full-text catalog on the destination server ?
Thanks in advance.

SQL Server 2000 to 2008 R2 db restore question


Hi guys

I've just restored a db (MS SQL 2000) unto MS SQL 2008. All went well and the users for that db were also created. But they were created on the db level.

On the server level, these logins were not there. I tried to create them manually at the server level. When I try to assign the login to the db it understandably says that the user with the same name already exists in the db.

I tried dropping the login created in the db level but I can't do so as there is a schema in the database with the username.

How should I go about getting the same login unto 2008 without dropping any schemas as I am trying to keep everything the same as it was in 2000 for testing purposes in 2008.


Thanks in advance.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend