For a backup strategy, I was thinking of just copying over all the system and user database device (DAT) files (of course after stopping all
the SQL services at a time when no one is accessing the database server). Then at the time of restoration, I would copy the files back to their
original location.
Is it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.
Not sure this is the correct forum, but I'll give it a go. I am in the process of deploying a series of databases from a development environment to a production environment. I've done some searching around best practices, but haven't found anything specifically calls out what is best. I am looking to find out the best approach for moving the code/tables/views/SP's, well everything from my dev environment to the production environment. Due to the complexity and large number of objects to be created, would a backup/restore to the production server be more prudent than creating a batch file type thing that creates all the objects through a series of scripts?
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments. As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists. Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way... FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup Thanks in advance for any help you can provide!
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them. 2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.
can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ......analysis services 2005 only
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
I had to replace my hard drive which was going bad. I reinstalled Sql 2005 and now need to restore the backup that I have. I selected Restore Database and chose From Device, and selected the backup location on J: drive and selected the backup file which has no extenstion. I checked the checkbox next to the backup set to restore. Click OK and then I get this error message: Restore failed for Server System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo) I don't know why it is looking for a MDF file as this was never in the Backup folder. Can someone please help me on this? I thought I was doing the right thing making a backup all along and now I can't restore it!
Is it possisable to restore from tape when the backup was taken from a different server. ServerA has the tape drive which performs the backups to tape, but ServerB which is under a different domain wants to restore ServerA database on ServerB, both databases on the two servers are the same is it possiable to restore a database on ServerB using ServerA file?
hello all !! I suffered a crash on one of my disks on a server runing MS-sql 7.0,and found out my backupexec dosnt really restore a runing version of my database. which solution would you recomend ,taking in acount i may have a copy of the database on another computer,which files i can restore? thanks yochai lam
Hi I am operating my databases under "trunc log on checkpoint" mode as I do not need point of failure recoverability.I was performing a recovery test using a backup which resulted in it being unsuccessful .Error Message received was "Recovery has failed because a nonlogged operation could not be redone.Use the RESTORE statement to restore all data in filegroup PRIMARY to a pt beyond the nonlogged changes"
Isnt sql*server supposed to prevent a non-logged operation while a backup is in progress?Also how do i prevent such a situation from recurring. If i switch to single user mode before a backup how do i prevent exisiting users from accessing ... without bouncing my sql*server?
I did a restore of database the user the original database is admin where it says the user is dbo.
After i did the restore i was able to add admin as user and dbowner but when i tried to change the user it said the user already exists.
How do i create the same..enviroment as or the original system with users..i saw a problem i i drop the user and them recreate it then it cannot find the object or the object which are owned by it are lost..could any one help me with this ...so as how to and what do i need to keep track of while restoring the database and configure the server back with same user names and permissions.
I have two databases on two different SQL Servers,our application is updating these two databases simultaniously.So every time these two databases will be in synchronized state. Now i want to schedule backup for these two Databases.How can i take backup of these two databases at single point of time. so that if i restore these two backups on corresponding servers,There should not be any conflicts.
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'NetworkDevice'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
I don't really understand what's goin.
basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.
Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.
Is there any other process that's much faster to implement.
Whenever I restore a backup on sqlserver 7.0 and create a new database from it, I always end up having problems with the users existing in the backup.
To enable those users to have access, I end up creating logins with those names and when I try granting those users Database access, I get the error message that the user already exists in the database.
Could some one tell me the right way to restore the backup and having all the users of the database also restored in the proper manner.
WE are using sqlserver 7.0 & backing full database using Seagate backup ver 7.2 on a tape drive.
I have following questions if anybody could help
1]How do I restore a single table backup from the tape backup without restoring the full database first on the disk & then running import/export utility to transfer/ copy that particular table to production database. Is there any third party backup utility which allows for a single / selected table restore?
2]What are the steps or if anybody has the script for tansfering logins/users with permisssions & without deencrypting the passwords when transfering from one server to another server.
Hi there, I am new to administration. i would like to create a development environment out of production database. for this i have to backup the production database on a regular basis. then use this backup and restore it to a database on different. i can do this task, but i want to automate this task to do it on regular basis. i would like to schedule my backup time. and immediately after backup i want to restore it to the database on other server, over writing existing data. what is the best way to do this???
I have a question related to SQL 6.5. We have a SMS 1.2 server which uses SQL 6.5. Two months ago, one of the NT admins did a NT backup of SMS database(sms.db) and then formatted the HD. Now, we need the SMS server back again. We installed the NT server, SMS server snd SQL server on that machine. The problem is, we now want the SQL server to recognize our SMS database (SMS.db file). We don't have any SQL backup of any database (that means, no copy of master database, no copy of other databases. IS THERE A WAY TO RECOVER SO THAT SQL SERVER RECPGNIZE THE SMS DATABASE(SMS.DB FILE) Please help me out. I would really appreciate that Jazib Frahim
I have a database xxxx on server a, I want to copy this DB to server b.How can I do this By back and restore. WHere can take this back up?I want to take this to a personal folder and wanted to restore to server b from this folder.Is it possible? Thanks.
I have 2 databases namely A & B respectively..... I have written a stored procedure that takes the backup of database A ....... Both the databases are operational .... Can I restore this Backup of Database A ...to Database B .... Is it possible. ....
I have read soimething that we can do it but with the Restore command we have to use the with move option...
I dont seem to understand this can someone please help me on this one....
I am trying to restore the backup from enterprise management studio i have selected the source of resource from device.Its not displaying the mdf etc files in the select the backup sets to restore..
Hi everyoneI have this situation with backup and restore.I have two instances of sql, sql1 and sql2.Production server is sql1 and test server is sql2.I created database maintenance plan that makes full backup every nighton tape.I would like to make some kind of job or to add steps to job that wascreated with DMplan which will make restore of backuped database to sql2server.How can i do this? How can i read which is the last file backuped ontape?Thanks very muchAlex*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I've been working developing database on my PC @ work, and sadly enough would like to spend some time on it at home. To these ends I backed up the database at work where it restored fine. However have tried to restore the file at home and am getting an error saying the operating system cannot access the file. Is this something to do with NTFS permissions?